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,
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.
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:
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