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.