System versioned Tables in SQL Server 2016

In SQL Server 2016, System versioned tables are the tables whose data is maintained in the history table. This history is maintained by SQL Server itself. All you need is to specify 2 additional datetime2 columns (SysStartTime and SysEndTime in the example below) and a clause with these two columns as : PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)

To create a system versioned table;

------ System versioned table

CREATE TABLE Department  

(   

     DeptID int NOT NULL PRIMARY KEY CLUSTERED 

   , DeptName varchar(50) NOT NULL 

   , ManagerID INT  NULL 

   , ParentDeptID int NULL 

   , SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL 

   , SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL 

   , PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)    

)   

WITH (SYSTEM_VERSIONING = ON)  

;

The system versioned table creates a temporal table which maintains the history of the data. By default, the name is as MSSQL_TemporalHistoryFor_1541580530. But you can specify the name of this table in the CREATE TABLE statement above;

------ System versioned table with name the temporal table

CREATE TABLE Department  

(   

     DeptID int NOT NULL PRIMARY KEY CLUSTERED 

   , DeptName varchar(50) NOT NULL 

   , ManagerID INT  NULL 

   , ParentDeptID int NULL 

   , SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL 

   , SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL 

   , PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)    

)   

WITH (SYSTEM_VERSIONING = ON ( HISTORY_TABLE = dbo.DepartmentHistory ) )  

;

In above example, dbo.DepartmentHistory is created as temporal table.

In the management studio, the system versioned tables are shown as below;

The above diagram shows the default name of the temporal table. If you have specified the name of your temporal table (dbo.DepartmentHistory in our example), then it will be shown;

You can also rename the temporal table. In sys.objects, these tables are listed as a user_table. To check if any table have a temporal table or not you can query the “OBJECTPROPERTY” for property “TableTemporalType”;

Select OBJECTPROPERTY(OBJECT_ID(N'dbo.Department', N'U'), N'TableTemporalType')

INSERT / UPDATE / DELETE

When we insert any data in the main table (dbo.Department), no data is inserted in the history / temporal table. In case of UPDATE you will see the latest data in main table and the history data in the temporal. The SysStartTime and SysEndTime are updated as per the time of UPDATE statement;

Update [dbo].[Department]

Set DeptName = 'A'

where DeptID = 3

Select * from [dbo].[Department]

Select * from [dbo].[DepartmentHistory]

You can see above that DeptName for DeptID=3 is changed to ‘A’ and the old value ‘D3’ is available in the temporal table. Please note that the copy of the complete record is maintained in the temporal table.

You cannot delete the data directly from Temporal table. When you delete data from main table, the data is stored in temporal table and deleted from the main table.

Delete from [dbo].[Department] where DeptID = 3

Select * from [dbo].[Department]

As you can see the data is deleted from main table. But the record is available in temporal table;

Select * from [dbo].[DepartmentHistory]

When you try to delete from the DepartmentHistory temporal table, it will generate error;

Delete from [dbo].[DepartmentHistory] where DeptID = 3

So, you cannot delete data from temporal table.

WHAT ELSE YOU CANNOT DO

TRUNCATE TABLE

1.       You also cannot TRUNCATE the system versioned and the temporal table. You will get the following error;

Msg 13545, Level 16, State 1, Line 35

Truncate failed on table 'Test16.dbo.Department' because it is not supported operation on system-versioned tables.

2.       You cannot change the schema of the temporal table.

3.       You cannot add trigger to temporal table

YOU CAN ADD INDEX

You can index the temporal table and the main table as per your requirement.