This article is half-done without your Comment! *** Please share your thoughts via Comment ***
SQL Server 2016 introduced System-versioned Temporal Table to keep a history of data change by adding additional history table.
In the below article, You can read more about System-versioned Temporal Table and you must know all the rules before creating a System-versioned Temporal Table.
You can also enable System-versioned Temporal Table in the existing table.
Check this demonstration:
Create a table with sample data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
CREATE TABLE tbl_Employees ( EmpID INT PRIMARY KEY ,EmpName VARCHAR(20) ,EmpDepartment VARCHAR(20) ,EmpSalary INT ) GO INSERT INTO tbl_Employees VALUES (1,'Anvesh','Database',90000) ,(2,'Jenny','JAVA',65000) ,(3,'Martin','PHP',85000) ,(4,'Roy','PHP',94000) GO |
Enable System-versioned Temporal Table on this table:
|
1 2 3 |
ALTER TABLE tbl_Employees SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE=dbo.tbl_EmployeesHistory)) GO |
You will get a below error because you must configure SYSTEM_TIME column.
|
1 2 |
Msg 13510, Level 16, State 1, Line 1 Cannot set SYSTEM_VERSIONING to ON when SYSTEM_TIME period is not defined. |
Configure SYSTEM_TIME column:
|
1 2 3 4 5 6 |
ALTER TABLE tbl_Employees ADD StartTime DATETIME2 GENERATED ALWAYS AS ROW START DEFAULT GETUTCDATE() ,EndTime DATETIME2 GENERATED ALWAYS AS ROW END DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999') ,PERIOD FOR SYSTEM_TIME (StartTime, EndTime) GO |
Now, enable System-versioned Temporal Table:
|
1 2 3 |
ALTER TABLE tbl_Employees SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE=dbo.tbl_EmployeesHistory)) GO |
Leave a Reply