Data Science Lab

Machine

32GB RAM, 4 Core Hyper-threaded enabled 2.8Ghz, SSD Storage

SQL Server

Out of the box, default installation

SQL Server 2014

12880ms

SQL Server 2016

1676ms

use tempdb

go

set nocount on

go

if(0 <> (select count(*) from tempdb.sys.objects where name = 'tblDBCC') )

begin

    drop table tblDBCC

end

go

create table tblDBCC (

iID int NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,

strData nvarchar(2000) NOT NULL

)

go

--Insert data to expand to a table that allows DOP activities

print 'Populating Data'

go

begin tran

go

insert into tblDBCC (strData) values ( replicate(N'X', 2000) )

while(SCOPE_IDENTITY() < 100000)

begin

    insert into tblDBCC (strData) values ( replicate(N'X', 2000) )

end

go

commit tran

go

-- CheckDB

declare @dtStart datetime

set @dtStart = GETUTCDATE();

dbcc checkdb(tempdb)

select datediff(ms, @dtStart, GetUTCDate()) as [Elapsed DBCC checkdb (ms)]

go

USE master

GO

DBCC TRACEON(3604)

GO

DBCC SHOWONRULES

GO

DBCC SHOWOFFRULES

GO

Rule Name

Description

JNtoNL

Join to Nested Loop

JNtoHS

Join to Hash

JNtoSM

Join to Sort Merge

LOJNtoNL

Left Outer Join to Nested Loop

LSJNtoHS

Left Semi join to Hash

LASJNtoSM

Left Anti Semi Join to Sort Merge

USE AdventureWorks2012

GO

SELECT

    c.CustomerID ,

    c.PersonID ,

    c.StoreID ,

    c.TerritoryID ,

    c.AccountNumber ,

    c.rowguid ,

    c.ModifiedDate

FROM Sales.SalesOrderHeader OH

    INNER JOIN Sales.Customer C ON OH.CustomerID = C.CustomerID

WHERE OH.ShipMethodID = 1

GO

USE AdventureWorks2012

GO

SELECT c.CustomerID ,

c.PersonID ,

c.StoreID ,

c.TerritoryID ,

c.AccountNumber ,

c.rowguid ,

c.ModifiedDate

FROM Sales.SalesOrderHeader OH

INNER JOIN Sales.Customer C ON OH.CustomerID = C.CustomerID

WHERE OH.ShipMethodID = 1

OPTION( QUERYRULEOFF JoinCommute )

GO

USE AdventureWorks2012

GO

SELECT c.CustomerID ,

c.PersonID ,

c.StoreID ,

c.TerritoryID ,

c.AccountNumber ,

c.rowguid ,

c.ModifiedDate

FROM Sales.SalesOrderHeader OH

INNER JOIN Sales.Customer C ON OH.CustomerID = C.CustomerID

WHERE OH.ShipMethodID = 1

OPTION( QUERYRULEOFF JNtoHS )

GO

alter database master set trustworthy on

go

use master

go

create procedure proc_enable_tf

with execute as owner

as

Exec('dbcc traceon(9481)')

go

grant execute on proc_enable_tf to public

go

create TRIGGER trigger_enable_tf

ON ALL SERVER

FOR LOGON

AS

BEGIN

IF app_name()= 'Microsoft SQL Server Management Studio – Query' — replace this with your application name

begin

exec master.dbo.proc_enable_tf

end

END;

USE AdventureWorks2014

GO

SELECT

    OH.PurchaseOrderNumber ,

    dbo.ufnGetProductListPrice(OD.ProductID, OH.OrderDate) ListPrice ,

    OD.UnitPRice ,

    OD.OrderQty ,

    OD.LineTotal ,

    dbo.ufnGetStock(OD.ProductID) RemainingStock

FROM Sales.SalesOrderHeader OH

    INNER JOIN Sales.SalesOrderDetail OD ON OH.SalesOrderID = OD.SalesOrderID

GO

USE AdventureWorks2014

GO

SELECT *

FROM Sales.SalesOrderHeader OH

CROSS APPLY dbo.ufnGetContactInformation(OH.SalesPersonID)

GO

USE MASTER

GO

SELECT DB_NAME(database_id) + '.' +

        OBJECT_SCHEMA_NAME(OBJECT_ID, database_id) +

'.' + OBJECT_NAME(OBJECT_ID, database_id)

        AS Function_Name,

QS.last_execution_time ,

QS.max_worker_time ,

QS.max_physical_reads ,

QS.max_logical_reads ,

QS.max_logical_writes ,

T.Text

FROM sys.dm_exec_function_stats QS

CROSS APPLY sys.dm_exec_sql_text(sql_handle) T

create table #tblHistogram

(

vData sql_variant,

range_rows bigint,

eq_rows bigint,

distinct_range_rows bigint,

avg_range_rows bigint,

actual_eq_rows bigint DEFAULT(NULL),

actual_range_rows bigint DEFAULT(NULL)

)

go

create procedure #spHistogram

@strTable sysname,

@strIndex sysname

as

dbcc show_statistics(@strTable, @strIndex) with HISTOGRAM

go

truncate table #tblHistogram

go

insert

into #tblHistogram (vData, range_rows, eq_rows, distinct_range_rows, avg_range_rows)

exec #spHistogram '[Person].[Person]', 'PK_Person_BusinessEntityID'

go

select * from #tblHistogram

-- EQ_ROWS

update #tblHistogram set actual_eq_rows = (select count(*) from [Person].[Person] with(NOLOCK) where BusinessEntityID = h.vData)

from #tblHistogram h;

-- RANGE_ROWS

with BOUNDS (LowerBound, UpperBound)

as

(

select LAG(vData) over(order by vData) as [LowerBound], vData [UpperBound] from #tblHistogram

)

update

#tblHistogram

set actual_range_rows = ActualRangeRows

from (select LowerBound, UpperBound,

(select count(*) from [Person].[Person] with(NOLOCK) where BusinessEntityID > LowerBound and BusinessEntityID < UpperBound) as ActualRangeRows from BOUNDS

) as t

where vData = t.UpperBound

go

select

vData, eq_rows, actual_eq_rows, range_rows, actual_range_rows from #tblHistogram

where eq_rows <> actual_eq_rows or range_rows <> actual_range_rows

order by vData

go

SELECT

     [event]

    ,[computer]

    ,[operator]

    ,[source]

    ,[starttime]

    ,[endtime]

    ,[message]

FROM [dbo].[sysssislog];

-- sample tables with FKs

create table dbo.M (id int primary key, M_c1 int not null, M_c2 datetime not null, M_c3 char(10) not null, M_c4 datetime);

create unique index ux1_A on dbo.M (M_c1);

create unique index ux2_A on dbo.M (M_c2, M_c3);

create table dbo.N1 (id int primary key, N1_c1 int, N1_c2 datetime, N1_c3 char(10), N1_c4 datetime);

create unique index ux1_N1 on dbo.N1 (N1_c1, N1_c3);

create unique index ux2_N1 on dbo.N1 ( N1_c2);

alter table dbo.N1 add constraint FK_N1_M foreign key (N1_c1) references dbo.M (M_c1);

create table dbo.N2 (id int primary key, N2_c1 int, N2_c2 datetime, N2_c3 char(10), N2_c4 datetime);

create unique index ux1_N2 on dbo.N2 (N2_c1);

create unique index ux2_N2 on dbo.N2 (N2_c3, N2_c4);

alter table dbo.N2 add constraint FK1_N2_M foreign key (N2_c2, N2_c3) references dbo.M (M_c2, M_c3);

create table dbo.P1 (id int primary key, P1_c1 int, P1_c2 datetime, P1_c3 char(10), P1_c4 datetime);

create unique index ux1_P1 on dbo.P1 (P1_c1);

alter table dbo.P1 add constraint FK_P1_N1 foreign key (P1_c1, P1_c3) references dbo.N1 (N1_c1, N1_c3);

create table dbo.P2 (id int primary key, P2_c1 int, P2_c2 datetime, P2_c3 char(10), P2_c4 datetime);

create unique index ux1_P2 on dbo.P2 (P2_c1);

create unique index ux2_P2 on dbo.P2 (P2_c2, P2_c3);

alter table dbo.P2 add constraint FK_P2_N1 foreign key (P2_c2) references dbo.N1 (N1_c2);

alter table dbo.P2 add constraint FK_P2_N2 foreign key (P2_c3, P2_c4) references dbo.N2 (N2_c3, N2_c4);

create table dbo.P3 (id int primary key, P3_c1 int, P3_c2 datetime, P3_c3 char(10), P3_c4 datetime);

alter table dbo.P3 add constraint FK_P3_N2 foreign key (P3_c1) references dbo.N2 (id);

create table dbo.Q1 (id int primary key, Q1_c1 int, Q1_c2 datetime, Q1_c3 char(10), Q1_C4 datetime);

alter table dbo.Q1 add constraint FK_Q1_P1 foreign key (Q1_c1) references dbo.P1 (P1_c1);

create table dbo.Q2 (id int primary key, Q2_c1 int, Q2_c2 datetime, Q2_c3 char(10), Q2_c4 datetime);

alter table dbo.Q2 add constraint FK_Q2_P2 foreign key (Q2_c1) references dbo.P2 (id);

create table dbo.Q3 (id int primary key, Q3_c1 int, Q3_c2 datetime, Q3_c3 char(10), Q3_c4 datetime);

alter table dbo.Q3 add constraint FK1_Q3_N2 foreign key (Q3_c1) references dbo.N2 (id);

alter table dbo.Q3 add constraint FK2_Q3_P2 foreign key (Q3_c2, Q3_c3) references dbo.P2 (P2_c2, P2_c3);

GO

-- populate all tables

insert into dbo.M (id, M_c1, M_c2, M_c3, M_c4)

select 1, 10, '2015-01-01', 'AB1', '2015-01-02'

union all

select 2, 20, '2015-01-02', 'AB2', '2015-01-03'

union all

select 3, 30, '2015-01-03', 'AB3', '2015-01-04';

insert into dbo.N1 (id, N1_c1, N1_c2, N1_c3, N1_c4)

select 11, 20, '2015-01-01', 'CD1', '2015-01-02'

union all

select 21, 30, '2015-01-02', 'CD2', '2015-01-03'

union all

select 31, 10, '2015-01-03', 'CD3', '2015-01-04';

insert into dbo.N2 (id, N2_c1, N2_c2, N2_c3, N2_c4)

select 11, 11, '2015-01-01', 'AB1', '2015-01-02'

union all

select 12, 22, '2015-01-02', 'AB2', '2015-01-03'

union all

select 13, 33, '2015-01-03', 'AB3', '2015-01-04';

insert into dbo.P1 (id, P1_c1, P1_c2, P1_c3, P1_c4)

select 100, 20, '2014-01-01', 'CD1', '2015-01-02'

union all

select 101, 30, '2014-01-02', 'CD2', '2015-01-03'

union all

select 102, 10, '2014-01-03', 'CD3', '2015-01-04'

insert into dbo.P2 (id, P2_c1, P2_c2, P2_c3, P2_c4)

select 200, 20, '2015-01-01', 'AB1', '2015-01-02'

union all

select 201, 30, '2015-01-02', 'AB2', '2015-01-03'

union all

select 202, 10, '2015-01-03', 'AB3', '2015-01-04'

insert into dbo.P3 (id, P3_c1, P3_c2, P3_c3, P3_c4)

select 301, 11, '2010-01-01', 'EF1', '2015-02-02'

union all

select 302, 13, '2010-01-02', 'EF2', '2015-02-03'

union all

select 303, 12, '2010-01-03', 'EF3', '2015-02-04'

use SW_TEST

go

if object_id('dbo.usp_searchFK', 'P') is not null

    drop proc dbo.usp_SearchFK;

go

create proc dbo.usp_SearchFK

@table varchar(256) -- use two part name convention

, @lvl int=0 -- do not change

, @ParentTable varchar(256)='' -- do not change

, @debug bit = 1

as

begin

    set nocount on;

    declare @dbg bit;

    set @dbg=@debug;

    if object_id('tempdb..#tbl', 'U') is null

        create table #tbl (id int identity, tablename varchar(256), lvl int, ParentTable varchar(256));

    declare @curS cursor;

    if @lvl = 0

        insert into #tbl (tablename, lvl, ParentTable)

        select @table, @lvl, Null;

    else

        insert into #tbl (tablename, lvl, ParentTable)

        select @table, @lvl,@ParentTable;

    if @dbg=1    

        print replicate('----', @lvl) + 'lvl ' + cast(@lvl as varchar(10)) + ' = ' + @table;

    if not exists (select * from sys.foreign_keys where referenced_object_id = object_id(@table))

        return;

    else

    begin -- else

        set @ParentTable = @table;

        set @curS = cursor for

        select tablename=object_schema_name(parent_object_id)+'.'+object_name(parent_object_id)

        from sys.foreign_keys

        where referenced_object_id = object_id(@table)

        and parent_object_id <> referenced_object_id; -- add this to prevent self-referencing which can create a indefinitive loop;

        open @curS;

        fetch next from @curS into @table;

        while @@fetch_status = 0

        begin --while

            set @lvl = @lvl+1;

            -- recursive call

            exec dbo.usp_SearchFK @table, @lvl, @ParentTable, @dbg;

            set @lvl = @lvl-1;

            fetch next from @curS into @table;

        end --while

        close @curS;

        deallocate @curS;

    end -- else

    if @lvl = 0

        select * from #tbl;

    return;

end

go

exec dbo.usp_SearchFK 'dbo.M'

if object_id('tempdb..#tmp') is not null

    drop table #tmp;

create table #tmp (id int, tablename varchar(256), lvl int, ParentTable varchar(256));

insert into #tmp

exec dbo.usp_SearchFK @table='dbo.M', @debug=0;

declare @where varchar(max) ='where M.id=2' -- if @where clause is null or empty, it will delete tables as a whole with the right order

declare @curFK cursor, @fk_object_id int;

declare @sqlcmd varchar(max)='', @crlf char(2)=char(0x0d)+char(0x0a);

declare @child varchar(256), @parent varchar(256), @lvl int, @id int;

declare @i int;

declare @t table (tablename varchar(128));

declare @curT cursor;

if isnull(@where, '')= ''

begin

    set @curT = cursor for select tablename, lvl from #tmp order by lvl desc

    open @curT;

    fetch next from @curT into @child, @lvl;

    while @@fetch_status = 0

    begin -- loop @curT

        if not exists (select 1 from @t where tablename=@child)

            insert into @t (tablename) values (@child);

        fetch next from @curT into @child, @lvl;

    end -- loop @curT

    close @curT;

    deallocate @curT;

    select @sqlcmd = @sqlcmd + 'delete from ' + tablename + @crlf from @t ;

    print @sqlcmd;

end

else

begin

    declare curT cursor for

    select lvl, id

    from #tmp

    order by lvl desc;

    open curT;

    fetch next from curT into @lvl, @id;

    while @@FETCH_STATUS =0

    begin

        set @i=0;

        if @lvl =0

        begin -- this is the root level

            select @sqlcmd = 'delete from ' + tablename from #tmp where id = @id;

        end -- this is the roolt level

        while @i < @lvl

        begin -- while

            select top 1 @child=TableName, @parent=ParentTable from #tmp where id <= @id-@i and lvl <= @lvl-@i order by lvl desc, id desc;

            set @curFK = cursor for

            select object_id from sys.foreign_keys

            where parent_object_id = object_id(@child)

            and referenced_object_id = object_id(@parent)

            open @curFK;

            fetch next from @curFk into @fk_object_id

            while @@fetch_status =0

            begin -- @curFK

                if @i=0

                    set @sqlcmd = 'delete from ' + @child + @crlf +

                    'from ' + @child + @crlf + 'inner join ' + @parent ;

                else

                    set @sqlcmd = @sqlcmd + @crlf + 'inner join ' + @parent ;

                ;with c as

                (

                    select child = object_schema_name(fc.parent_object_id)+'.' + object_name(fc.parent_object_id), child_col=c.name

                    , parent = object_schema_name(fc.referenced_object_id)+'.' + object_name(fc.referenced_object_id), parent_col=c2.name

                    , rnk = row_number() over (order by (select null))

                    from sys.foreign_key_columns fc

                    inner join sys.columns c

                    on fc.parent_column_id = c.column_id

                    and fc.parent_object_id = c.object_id

                    inner join sys.columns c2

                    on fc.referenced_column_id = c2.column_id

                    and fc.referenced_object_id = c2.object_id

                    where fc.constraint_object_id=@fk_object_id

                )

                    select @sqlcmd =@sqlcmd + case rnk when 1 then ' on ' else ' and ' end

                    + @child +'.'+ child_col +'=' + @parent +'.' + parent_col

                    from c;

                    fetch next from @curFK into @fk_object_id;

            end --@curFK

            close @curFK;

            deallocate @curFK;

            set @i = @i +1;

        end --while

        print @sqlcmd + @crlf + @where + ';';

        print '';

        fetch next from curT into @lvl, @id;

    end

    close curT;

    deallocate curT;

end

go

-- create table to hold DDL history

CREATE TABLE sw_test.[dbo].[DDL_History](

    [database_name] [nvarchar](256) NULL,

    [start_time] [datetime] NULL,

    [login_name] [nvarchar](256) NULL,

    [user_name] [nvarchar](256) NULL,

    [application_name] [nvarchar](256) NULL,

    [ddl_operation] [nvarchar](40) NULL,

    [object] [nvarchar](257) NOT NULL,

    [type_desc] [nvarchar](60) NULL

) ON [PRIMARY]

GO

USE [master]

GO

--Declare variables

SET NOCOUNT ON;

DECLARE @dbid INT;

DECLARE @dbname VARCHAR(100);

DECLARE @execstr VARCHAR(4000);

-- Declare a cursor.

DECLARE dbs CURSOR FOR

SELECT database_id,name from sys.databases where name not in ('master','tempdb');

-- Open the cursor.

OPEN dbs;

-- Loop through all the tables in the database.

FETCH NEXT

FROM dbs

INTO @dbid,@dbname;

WHILE @@FETCH_STATUS = 0

BEGIN;

-- Check default trace for any DDL in specific database

SELECT @execstr='if (select convert(int,value_in_use) from sys.configurations where name = ''default trace enabled'') = 1

begin

declare @d1 datetime;

declare @diff int;

declare @curr_tracefilename varchar(500);

declare @base_tracefilename varchar(500);

declare @indx int ;

declare @temp_trace table (

obj_name nvarchar(256)

, obj_id int

, database_name nvarchar(256)

, start_time datetime

, event_class int

, event_subclass int

, object_type int

, server_name nvarchar(256)

, login_name nvarchar(256)

, user_name nvarchar(256)

, application_name nvarchar(256)

, ddl_operation nvarchar(40)

);

select @curr_tracefilename = path from sys.traces where is_default = 1 ;

set @curr_tracefilename = reverse(@curr_tracefilename)

select @indx = PATINDEX(''%\%'', @curr_tracefilename)

set @curr_tracefilename = reverse(@curr_tracefilename)

set @base_tracefilename = LEFT( @curr_tracefilename,len(@curr_tracefilename) - @indx) + ''\log.trc'';

insert into @temp_trace

select ObjectName

, ObjectID

, DatabaseName

, StartTime

, EventClass

, EventSubClass

, ObjectType

, ServerName

, LoginName

, NTUserName

, ApplicationName

, ''temp''

from ::fn_trace_gettable( @base_tracefilename, default )

where EventClass in (46,47,164) and EventSubclass = 0 and DatabaseID = '+cast(@dbid as varchar)+' ;

update @temp_trace set ddl_operation = ''CREATE'' where event_class = 46;

update @temp_trace set ddl_operation = ''DROP'' where event_class = 47;

update @temp_trace set ddl_operation = ''ALTER'' where event_class = 164;

select @d1 = min(start_time) from @temp_trace

set @diff= datediff(hh,@d1,getdate())

set @diff=@diff/24;

        insert into sw_test.dbo.DDL_History (database_name,start_time,login_name,user_name,application_name,ddl_operation,object,type_desc)

        select database_name,start_time,login_name,user_name,application_name,ddl_operation,s.name+''.''+o.name as "object",o.type_desc

        from @temp_trace tt inner join

         '+@dbname+'.sys.objects o on tt.obj_id=o.object_id inner join

             '+@dbname+'.sys.schemas s on s.schema_id=o.schema_id

     where object_type not in (21587) -- don''t bother with auto-statistics as it generates too much noise

and start_time > (select case when max(start_time) is NULL then ''1900-01-01'' else max(start_time) end from sw_test.dbo.DDL_History)

end'

EXEC (@execstr)

FETCH NEXT

FROM dbs

INTO @dbid,@dbname;

END;

-- Close and deallocate the cursor.

CLOSE dbs;

DEALLOCATE dbs;

CREATE TABLE ##tmpTest

(

    Value INT

)

--Query 1

IF (OBJECT_ID('tempdb..##tmpTest') IS NOT NULL)

    DROP TABLE ##tmpTest

CREATE TABLE ##tmpTest

(

    Value INT

)

DECLARE @i INT =1

WHILE (@i <= 1000000) --Value 1000000 is used to facilitate testing process, please be careful in choosing this value for your server to avoid overloading the server

BEGIN

    INSERT INTO ##tmpTest(Value) VALUES(@i)

    SET @i=@i+1

END

--Query 2

SELECT TOP 1 * FROM ##tmpTest

--Query 3

SELECT * FROM ##tmpTest

DECLARE @i INT =0,

        @val INT =0

WHILE (@i <= 1000000)

BEGIN

    SELECT @val=Value FROM ##tmpTest WHERE Value=@i

    SET @i=@i+1

    SELECT @val

END

ALTER DATABASE [DEMO_1] SET QUERY_STORE = ON

GO

ALTER DATABASE [DEMO_1]

SET QUERY_STORE (OPERATION_MODE = READ_ONLY,

                CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 367),

                DATA_FLUSH_INTERVAL_SECONDS = 900,

                INTERVAL_LENGTH_MINUTES = 60,

                MAX_STORAGE_SIZE_MB = 100,

                QUERY_CAPTURE_MODE = AUTO,

                SIZE_BASED_CLEANUP_MODE = AUTO)

GO

ALTER DATABASE [DEMO_1] SET QUERY_STORE CLEAR ALL

GO

EXEC sys.sp_query_store_flush_db

SELECT name, type_desc FROM sys.all_objects

WHERE name LIKE '%query_store%' or name= 'query_context_settings'

확장이벤트

설명

query_store_background_task_persist_started

Fired if the background task for Query Store data persistence started execution

query_store_background_task_persist_finished

Fired if the background task for Query Store data persistence is completed successfully

query_store_load_started

Fired when query store load is started

query_store_db_data_structs_not_released

Fired if Query Store data structures are not released when feature is turned OFF.

query_store_db_diagnostics

Periodically fired with Query Store diagnostics on database level.

query_store_db_settings_changed

Fired when Query Store settings are changed.

query_store_db_whitelisting_changed

Fired when Query Store database whitelisting state is changed.

query_store_global_mem_obj_size_kb

Periodically fired with Query Store global memory object size.

query_store_size_retention_cleanup_started

Fired when size retention policy clean-up task is started.

query_store_size_retention_cleanup_finished

Fired when size retention policy clean-up task is finished.

query_store_size_retention_cleanup_skipped

Fired when starting of size retention policy clean-up task is skipped because its minimum repeating period did not pass yet.

query_store_size_retention_query_deleted

Fired when size based retention policy deletes a query from Query Store.

query_store_size_retention_plan_cost

Fired when eviction cost is calculated for the plan.

query_store_size_retention_query_cost

Fired when query eviction cost is calculated for the query.

query_store_generate_showplan_failure

Fired when Query Store failed to store a query plan because the showplan generation failed.

query_store_capture_policy_evaluate

Fired when the capture policy is evaluated for a query.

query_store_capture_policy_start_capture

Fired when an UNDECIDED query is transitioning to CAPTURED.

query_store_capture_policy_abort_capture

Fired when an UNDECIDED query failed to transition to CAPTURED.

query_store_schema_consistency_check_failure

Fired when the Query Store schema consistency check failed.

CREATE TABLE dbo.TestTemporal (

ID int primary key

,A int

,B int

,SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL

,SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL

,PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)) WITH(SYSTEM_VERSIONING = ON

);

-- Initial Load

INSERT INTO dbo.TestTemporal(ID, A, B)

VALUES     (1,2,3)

        ,(2,4,5)

        ,(3,0,1);

SELECT * FROM dbo.TestTemporal;

-- Modify Data

DELETE FROM dbo.TestTemporal

WHERE ID = 2;

UPDATE dbo.TestTemporal

SET A = 5

WHERE ID = 3;

SELECT * FROM dbo.TestTemporal;

select * from dbo.MSSQL_TemporalHistoryFor_1253579504

drop table dbo.TestTemporal

메시지 13552, 수준 16, 상태 1, 줄 14

테이블 삭제 작업은 시스템 버전 관리 임시 테이블에서 지원되는 작업이 아니므로 'SW_Test.dbo.TestTemporal' 테이블에서 테이블 삭제 작업을 수행할 수 없습니다.

ALTER TABLE dbo.TestTemporal SET (SYSTEM_VERSIONING = OFF);

ALTER TABLE dbo.TestTemporal SET (SYSTEM_VERSIONING = ON

(HISTORY_TABLE=dbo.MSSQL_TemporalHistoryFor_1253579504,DATA_CONSISTENCY_CHECK=[ON/OFF])

);

SELECT

[owt].[session_id],

[owt].[exec_context_id],

[ot].[scheduler_id],

[owt].[wait_duration_ms],

[owt].[wait_type],

[owt].[blocking_session_id],

[owt].[resource_description],

CASE [owt].[wait_type]

WHEN N'CXPACKET' THEN

RIGHT ([owt].[resource_description],

CHARINDEX (N'=', REVERSE ([owt].[resource_description])) - 1)

ELSE NULL

END AS [Node ID],

--[es].[program_name],

[est].text,

[er].[database_id],

[eqp].[query_plan],

[er].[cpu_time]

FROM sys.dm_os_waiting_tasks [owt]

INNER JOIN sys.dm_os_tasks [ot] ON

[owt].[waiting_task_address] = [ot].[task_address]

INNER JOIN sys.dm_exec_sessions [es] ON

[owt].[session_id] = [es].[session_id]

INNER JOIN sys.dm_exec_requests [er] ON

[es].[session_id] = [er].[session_id]

OUTER APPLY sys.dm_exec_sql_text ([er].[sql_handle]) [est]

OUTER APPLY sys.dm_exec_query_plan ([er].[plan_handle]) [eqp]

WHERE

[es].[is_user_process] = 1

ORDER BY

[owt].[session_id],

[owt].[exec_context_id];

GO

USE [master];

GO

-- add "boss" to sysadmin:

CREATE LOGIN boss WITH PASSWORD = 'x', CHECK_POLICY = OFF;

ALTER SERVER ROLE sysadmin ADD Member boss;

-- add "dev1" to serveradmin:

CREATE LOGIN dev1 WITH PASSWORD = 'x', CHECK_POLICY = OFF;

ALTER SERVER ROLE serveradmin ADD Member dev1;

-- add "dev2" to dbcreator:

CREATE LOGIN dev2 WITH PASSWORD = 'x', CHECK_POLICY = OFF;

ALTER SERVER ROLE dbcreator ADD Member dev2;

-- "peon1" will only be in public

CREATE LOGIN peon1 WITH PASSWORD = 'x', CHECK_POLICY = OFF;

-- "peon2" will be in public *and* granted explicit access to AW2014:

CREATE LOGIN peon2 WITH PASSWORD = 'x', CHECK_POLICY = OFF;

GO

USE [AdventureWorksDW2012];

GO

CREATE USER peon2 FROM LOGIN peon2;

GO

USE [master];

GO

DENY VIEW ANY DATABASE TO peon1;

EXECUTE AS LOGIN = N'peon1';

GO

SELECT name FROM sys.databases WHERE HAS_DBACCESS(name) = 1;

GO

REVERT;

-- table for collecting data

CREATE TABLE #x([login] SYSNAME, db SYSNAME, error INT);

-- table of logins we care about today

DECLARE @logins TABLE([login] SYSNAME);

INSERT @logins([login]) VALUES(N'boss'),(N'peon1'),(N'peon2');

-- build SQL commands for read attempts to each online db

DECLARE @sql NVARCHAR(MAX) = N'', @cmd NVARCHAR(MAX) = N'';

SELECT @sql += N'

BEGIN TRY

INSERT #x SELECT TOP (1) SUSER_SNAME(),N''' + REPLACE(name,'''','''''')

+ N''',0 FROM ' + QUOTENAME(name) + N'.sys.all_objects;

END TRY

BEGIN CATCH

INSERT #x SELECT SUSER_SNAME(),N''' + REPLACE(name,'''','''''')

+ ''', ERROR_NUMBER();

END CATCH;' FROM sys.databases

WHERE [state] = 0;

SELECT @cmd += N'

EXECUTE AS LOGIN = N'''

+ REPLACE([login], '''', '''''') + N''';

' + @sql + N'

REVERT;' FROM @logins;

EXEC [master].sys.sp_executesql @cmd;

-- report

SELECT [login], [db], [Access?] = CASE error

WHEN 0 THEN 'Yes'

WHEN 229 THEN 'Access denied on sys.all_objects'

WHEN 916 THEN 'Cannot connect to database'

ELSE 'No - ' + CONVERT(VARCHAR(11), error) END

FROM #x ORDER BY [login],db;

GO

--DROP TABLE #x;

select cast(round((75-25)*rand()+25, 0) as integer) as RandBetweenValue

create function randbetween(@bottom int, @top int)

returns int

as

begin

    return (select cast(round((@top-bottom)*rand() + @bottom,0) as integer))

end

go

create view vRandomNumber

as

    select rand() as RandomNumber

go

create function randbetween(@bottom int, @top int)

returns int

as

begin

    return (select cast(round((@top-@bottom)* RandomNumber + @bottom,0) as integer) from vRandomNumber)

end

go

select dbo.randbetween(25, 75)