This article is half-done without your Comment! *** Please share your thoughts via Comment ***
SQL Server 2016 introduced a new type of user table that is called System-versioned Temporal Table to keep a full history of data changes.
This is a very good addition to the SQL Server 2016 because to track data change history is always an important task for a big transactional system.
SQL Server already provides few features to track data change like Change Data Capture (CDC), SQL Server Auditing, can create a manual DDL Trigger.
Now, SQL Server 2016 System-versioned temporal table which is something good and very easy to implement on a specific table.
You can audit all data changes and can perform require analysis on it. In Case of invalid delete or update, you can recover your data.
Rules and Requirements:
- Can create your table using SYSTEM_VERSIONING=ON
- Define your history table name HISTORY_TABLE = TableName, which tracks data change history. (It creates automatically).
- Must define two additional columns [ValidFrom] and [ValidTo] with default ROW START and ROW END and must refer to as SYSTEM_TIME period columns.
- Must have a Primary key column.
- Same number of column and structure between both the tables.
Full demonstration:
Create a sampel System Version Table:
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE tbl_Students ( StudID INT PRIMARY KEY ,StudName VARCHAR(50) ,StudClass CHAR(1) ,ValidFrom DATETIME2 (2) GENERATED ALWAYS AS ROW START ,ValidTo DATETIME2 (2) GENERATED ALWAYS AS ROW END ,PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo) ) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.tbl_StudentsHistory)); GO |
Insert few sample records:
|
1 2 3 4 5 |
INSERT INTO tbl_Students (StudID,StudName,StudClass) VALUES (1,'Roy','A'),(2,'Bony','B'),(3,'Mahi','C') ,(4,'Maria','A'),(5,'Ron','B'),(6,'Murey','C') GO |
Execute few DML statements:
|
1 2 3 4 5 6 |
UPDATE tbl_Students SET StudClass = 'B' WHERE StudID = 4; GO DELETE FROM tbl_Students WHERE StudID = 2 GO |
Check the result of both tables:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SELECT *FROM tbl_StudentsHistory StudID StudName StudClass ValidFrom ValidTo ------- ------------- --------- --------------------------- --------------------------- 4 Maria A 2017-01-06 05:47:23.73 2017-01-06 05:48:54.43 2 Bony B 2017-01-06 05:47:23.73 2017-01-06 05:50:12.52 SELECT *FROM tbl_Students StudID StudName StudClass ValidFrom ValidTo ------- --------------- --------- --------------------------- --------------------------- 1 Roy A 2017-01-06 05:47:23.73 9999-12-31 23:59:59.99 3 Mahi C 2017-01-06 05:47:23.73 9999-12-31 23:59:59.99 4 Maria B 2017-01-06 05:48:54.43 9999-12-31 23:59:59.99 5 Ron B 2017-01-06 05:47:23.73 9999-12-31 23:59:59.99 6 Murey C 2017-01-06 05:47:23.73 9999-12-31 23:59:59.99 |
