Temporal Tables Practice on SQL SERVER 2016

/ Create TemporalDB database and drop tables if exist /

SET NOCOUNT ON;

USE Test

GO

IF OBJECT_ID(N'dbo.Employees', N'U') IS NOT NULL

BEGIN

IF OBJECTPROPERTY(OBJECT_ID(N'dbo.Employees', N'U'), N'TableTemporalType') = 2

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

IF OBJECT_ID(N'dbo.EmployeesHistory', N'U') IS NOT NULL

DROP TABLE dbo.EmployeesHistory;

DROP TABLE dbo.Employees;

END;

GO

-- Create and populate Employees table

CREATE TABLE dbo.Employees

(

empid INT NOT NULL

CONSTRAINT PK_Employees PRIMARY KEY NONCLUSTERED,

mgrid INT NULL

CONSTRAINT FK_Employees_mgr_emp REFERENCES dbo.Employees,

empname VARCHAR(25) NOT NULL,

sysstart DATETIME2(0) NOT NULL,

sysend DATETIME2(0) NOT NULL

);

CREATE UNIQUE CLUSTERED INDEX ix_Employees

ON dbo.Employees(empid, sysstart, sysend);

INSERT INTO dbo.Employees(empid, mgrid, empname, sysstart, sysend) VALUES

(1 , NULL, 'David' , '2015-06-01 19:54:04', '9999-12-31 23:59:59'),

(2 , 1 , 'Eitan' , '2015-06-01 19:54:04', '9999-12-31 23:59:59'),

(3 , 1 , 'Ina' , '2015-06-01 20:01:41', '9999-12-31 23:59:59'),

(4 , 2 , 'Seraph' , '2015-06-01 19:54:20', '9999-12-31 23:59:59'),

(5 , 2 , 'Jiru' , '2015-06-01 19:54:20', '9999-12-31 23:59:59'),

(6 , 3 , 'Steve' , '2015-06-01 21:32:20', '9999-12-31 23:59:59'),

(7 , 4 , 'Aaron' , '2015-06-01 21:32:20', '9999-12-31 23:59:59'),

(8 , 5 , 'Lilach' , '2015-06-01 20:01:41', '9999-12-31 23:59:59'),

(9 , 4 , 'Rita' , '2015-06-01 21:32:20', '9999-12-31 23:59:59'),

(10, 5 , 'Sean' , '2015-06-01 20:01:41', '9999-12-31 23:59:59'),

(11, 6 , 'Gabriel', '2015-06-01 21:32:20', '9999-12-31 23:59:59');

-- Create and populate EmployeesHistory table

CREATE TABLE dbo.EmployeesHistory

(

empid INT NOT NULL,

mgrid INT NULL,

empname VARCHAR(25) NOT NULL,

sysstart DATETIME2(0) NOT NULL,

sysend DATETIME2(0) NOT NULL

);

CREATE CLUSTERED INDEX ix_EmployeesHistory

ON dbo.EmployeesHistory(empid, sysstart, sysend)

WITH (DATA_COMPRESSION = PAGE);

INSERT INTO dbo.EmployeesHistory(empid, mgrid, empname, sysstart, sysend) VALUES

(6 , 2, 'Steve' , '2015-06-01 19:54:20', '2015-06-01 21:32:20'),

(7 , 3, 'Aaron' , '2015-06-01 20:01:41', '2015-06-01 21:32:20'),

(9 , 7, 'Rita' , '2015-06-01 20:01:41', '2015-06-01 20:11:01'),

(9 , 3, 'Rita' , '2015-06-01 20:11:01', '2015-06-01 21:32:20'),

(11, 7, 'Gabriel', '2015-06-01 20:01:41', '2015-06-01 20:11:01'),

(11, 3, 'Gabriel', '2015-06-01 20:11:01', '2015-06-01 21:32:20'),

(12, 9, 'Emilia' , '2015-06-01 20:01:41', '2015-06-01 21:32:20'),

(13, 9, 'Michael', '2015-06-01 20:01:41', '2015-06-01 20:11:01'),

(14, 9, 'Didi' , '2015-06-01 20:01:41', '2015-06-01 20:11:01');

-- Enable system versioning

ALTER TABLE dbo.Employees ADD

PERIOD FOR SYSTEM_TIME (sysstart, sysend);

ALTER TABLE dbo.Employees

SET ( SYSTEM_VERSIONING = ON ( HISTORY_TABLE = dbo.EmployeesHistory ) );

/* Querying data and optimization considerations

FOR SYSTEM_TIME subclauses

The AS OF subclause is only one of four subclauses that the FOR SYSTEM_TIME clause supports. Here’s the full list of supported subclauses:

AS OF @datetime

FROM @start TO @end equal to StartDate < @end AND EndDate > @start

BETWEEN @start AND @end equal to StartDate <= @end AND EndDate > @start

CONTAINED IN(@start, @end) equal to @start <= StartDate AND EndDate <= @end

*/

-- AS OF @datetime

DECLARE @datetime AS DATETIME2(0) = '2015-06-01 20:11:01';

SELECT *

FROM dbo.Employees FOR SYSTEM_TIME AS OF @datetime;

-- This query is equivalent to the following:

SELECT *

FROM dbo.Employees

WHERE sysstart <= @datetime

AND sysend > @datetime

UNION ALL

SELECT *

FROM dbo.EmployeesHistory

WHERE sysstart <= @datetime

AND sysend > @datetime;

-- FROM @start TO @end

DECLARE

@start AS DATETIME2(0) = '2015-06-01 19:00:00',

@end AS DATETIME2(0) = '2015-06-01 21:32:20',

@empid AS INT = 9;

SELECT *

FROM dbo.Employees FOR SYSTEM_TIME FROM @start TO @end

WHERE empid = @empid;

-- This query is equivalent to the following:

SELECT *

FROM dbo.Employees

WHERE empid = @empid

AND sysstart < @end

AND sysend > @start

UNION ALL

SELECT *

FROM dbo.EmployeesHistory

WHERE empid = @empid

AND sysstart < @end

AND sysend > @start;

-- BETWEEN @start AND @end

DECLARE

@start AS DATETIME2(0) = '2015-06-01 19:00:00',

@end AS DATETIME2(0) = '2015-06-01 21:32:20',

@empid AS INT = 9;

SELECT *

FROM dbo.Employees FOR SYSTEM_TIME BETWEEN @start AND @end

WHERE empid = @empid;

-- This query is equivalent to the following code:

SELECT *

FROM dbo.Employees

WHERE empid = @empid

AND sysstart <= @end

AND sysend > @start

UNION ALL

SELECT *

FROM dbo.EmployeesHistory

WHERE empid = @empid

AND sysstart <= @end

AND sysend > @start;

-- CONTAINED IN(@start, @end)

DECLARE

@start AS DATETIME2(0) = '2015-06-01 20:01:41',

@end AS DATETIME2(0) = '2015-06-01 21:32:20',

@empid AS INT = 9;

SELECT *

FROM dbo.Employees FOR SYSTEM_TIME CONTAINED IN (@start, @end)

WHERE empid = @empid;

-- This query is equivalent to the following code:

SELECT *

FROM dbo.Employees

WHERE empid = @empid

AND sysstart <= @end

AND sysend >= @start

UNION ALL

SELECT *

FROM dbo.EmployeesHistory

WHERE empid = @empid

AND sysstart <= @end

AND sysend >= @start;