Temporal tables in SQL Server 2016

Temporal Tables Step by Step e-Learning. Watch on YOUTUBE

--How to create Fresh New Temporal Table?
CREATE TABLE dbo.Departments
(
    DeptID INT NOT NULL PRIMARY KEY CLUSTERED,
    DeptName VARCHAR(50) NOT NULL,
    ManagerID INT NULL, --Employee.ID
 
    SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START,
    SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME(SysStartTime, SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentsHistory));
 
GO;
 
 
 
 
 
 
 
--How to enable system-version on existing table?
CREATE TABLE dbo.Departments
(
    DeptID INT NOT NULL,
    DeptName VARCHAR(50) NOT NULL,
    ManagerID INT NULL, --Employee.ID
);
 
 
 
ALTER TABLE dbo.Departments
ADD SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START CONSTRAINT DF_Departments_SysStartTime
        DEFAULT SYSUTCDATETIME() NOT NULL,
    SysEndTime datetime2 GENERATED ALWAYS AS ROW END CONSTRAINT DF_Departments_SysEndTime
        DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59') NOT NULL,
    PERIOD FOR SYSTEM_TIME(SysStartTime, SysEndTime)
 
 
 
 
ALTER TABLE dbo.Departments ADD PRIMARY KEY (DeptID);
ALTER TABLE dbo.Departments SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE=dbo.DepartmentsHistory))
 
 
 
 
 
--Sample SQL Server syntax to manage Temporal table
--Create Normal table
CREATE TABLE dbo.Departments
(
    DeptID INT NOT NULL PRIMARY KEY CLUSTERED, --Must have primary key
 
    DeptName VARCHAR(50) NOT NULL,
    ManagerID INT NULL, --Employee.ID
 
);
 
GO
;
 
 
 
--Add datetime column
ALTER TABLE dbo.Departments
ADD SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START  
CONSTRAINT DF_Departments_SysStartTime DEFAULT SYSUTCDATETIME() NOT NULL,
SysEndTime datetime2 GENERATED ALWAYS AS ROW END 
CONSTRAINT DF_Departments_SysEndTime DEFAULT CONVERT (DATETIME2, '9999-12-31 23:59:59') NOT NULL,
        PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
 
GO
 
 
 
--Enable SYSTEM_VERSIONING
ALTER TABLE dbo.Departments SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE=dbo.DepartmentsHistory))
 
GO
 
 
 
--Insert dummy records
INSERT INTO Departments
(
    DeptID,
    DeptName,
    ManagerID
)
VALUES
('1', 'dev', '1')
 
INSERT INTO Departments
(
    DeptID,
    DeptName,
    ManagerID
)
VALUES
('2', 'ui', '1')
 
GO
 
 
--Update few records
UPDATE Departments
SET DeptName = 'Developement'
WHERE DeptID = 1;
 
DELETE FROM Departments
WHERE DeptID = 2;
 
GO
;
 
 
 
--Verify records
SELECT *
FROM Departments
 
SELECT *
FROM DepartmentsHistory
 
GO;
 
 
 
--Get Historical Data
DECLARE @sdate DATETIME2 = '2017-07-28';
DECLARE @edate DATETIME2 = '2017-07-29';
 
SELECT *
FROM Departments
    FOR SYSTEM_TIME FROM @sdate TO @edate
WHERE DeptID = 1;
 
GO
;
 
 
 
--There are many extended select clause available to fetch historical data.
AS OF
FROM TO
BETWEEN AND
CONTAINED IN ( , )
ALL
 
 
--Drop table
ALTER TABLE dbo.Departments SET (SYSTEM_VERSIONING = OFF)
ALTER TABLE dbo.Departments DROP PERIOD FOR SYSTEM_TIME
DROP TABLE dbo.Departments
DROP TABLE dbo.DepartmentsHistory
GO
;
 
 
 
 
 
--Temporal tables store values for columns in UTC time zone, we can't change it as of now but we can play with filter and cast the calue in to different time zone.
--refrence https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables
 
SELECT DeptID,
       DeptName,
       SysStartTime AT TIME ZONE 'UTC' AT TIME ZONE 'US Mountain Standard Time' AS EntryDate
FROM dbo.Departments
 
 
DECLARE @date DATETIME;
SET @date = GETUTCDATE();
SELECT *
FROM dbo.Departments
    FOR SYSTEM_TIME AS OF @date

--If you want to search all data with respect to primary key then you can use FOR SYSTEM_TIME ALL

SELECT *
FROM dbo.Departments FOR SYSTEM_TIME ALL PS
WHERE DeptID=1

Error you found:

Cannot set SYSTEM_VERSIONING to ON when SYSTEM_TIME period is not defined.

Make sure you have defined system_time period in table schema.

If you are trying to create new Temporal table then

SysStartTime datetime2 GENERATED ALWAYS AS ROW START,
SysEndTime datetime2 GENERATED ALWAYS AS ROW END,
PERIOD FOR SYSTEM_TIME (SysStartTime , SysEndTime )

If you are applying on existing table then

ALTER TABLE dbo.Departments
ADD SysStartTime datetime2 GENERATED ALWAYS AS ROW START
CONSTRAINT DF_Departments_SysStartTime DEFAULT SYSUTCDATETIME() NOT NULL,

SysEndTime datetime2 GENERATED ALWAYS AS ROW END
CONSTRAINT DF_Departments_SysEndTime DEFAULT CONVERT (DATETIME2, '9999-12-31 23:59:59') NOT NULL,

PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)





System versioned temporal table 'DB2016.dbo.Departments' must have primary key defined.

If you are applying in existing table then
ALTER TABLE dbo.Departments ADD PRIMARY KEY (DeptID);

For fresh new temporal table
DeptID INT NOT NULL PRIMARY KEY CLUSTERED,





Drop table operation failed on table 'DB2016.dbo.Departments' because it is not supported operation on system-versioned temporal tables.
Truncate failed on table 'DB2016.dbo.Departments' because it is not supported operation on system-versioned tables.


ALTER TABLE dbo.Departments SET (SYSTEM_VERSIONING = OFF)
ALTER TABLE dbo.Departments DROP PERIOD FOR SYSTEM_TIME
DROP TABLE dbo.Departments
DROP TABLE dbo.DepartmentsHistory




Temporal table 'DB2016.dbo.Departments' already has history table defined. Consider dropping system_versioning first if you want to use different history table.
ALTER TABLE dbo.Departments
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentLogs))

You can have only one corresponding versioning table.






Other Errors:

• Cannot set SYSTEM_VERSIONING to ON when SYSTEM_TIME period is not defined.
• ADD PERIOD FOR SYSTEM_TIME failed because table 'DB2016.dbo.Departments' contains records where end of period is not equal to MAX datetime.
• ADD PERIOD FOR SYSTEM_TIME failed because table contains records where end of period is not equal to MAX datetime.

Make sure you have correctly configured temporal table.Make sure you have correctly