How to create System-versioned temporal tables? – New database feature of SQL Server 2016

Creating a System-Versioned Temporal Table – There are three ways to create a system-versioned temporal table with regards to how the history table is specified:

1) Creating a temporal table with an anonymous history table

Creating a temporal table with an anonymous name. This is easiest way to create it when you do not concern about history table name. In the example below, a new table is created with system-versioning enabled and name is automatically decide by SQL Server.

· A system-versioned temporal table must have a primary key defined and have exactly one PERIOD FOR SYSTEM_TIME defined with two datetime2 columns, declared as GENERATED ALWAYS AS ROW START / END

· The PERIOD columns are always assumed to be non-nullable, even if nullability is not specified. If the PERIOD columns are explicitly defined as nullable, the CREATE TABLE statement will fail.

· The history table must always be schema-aligned with the current or temporal table, in terms of number of columns, column names, ordering and data types.

· An anonymous history table is automatically created in the same schema as current or temporal table.

· The anonymous history table name has the following format: MSSQL_TemporalHistoryFor_<current_temporal_table_object_id>_[suffix]. Suffix is optional and it will be added only if the first part of the table name is not unique.

· The history table is created as a rowstore table. PAGE compression is applied if possible, otherwise the history table will be uncompressed. For example, some table configurations, such as SPARSE columns, do not allow compression.

· A default clustered index is created for the history table with an auto-generated name in format IX_<history_table_name>. The clustered index contains the PERIOD columns (end, start).

2) Creating a temporal table with a default history table

Creating a temporal table with a default history table is a convenient option when you want to control naming and still rely on the system to create the history table with the default configuration. In the example below, a new table is created with system-versioning enabled with the name of the history table explicitly defined.

· The schema name is mandatory for the HISTORY_TABLE parameter.

· If the specified schema does not exist, the CREATE TABLE statement will fail.

· If the table specified by the HISTORY_TABLE parameter already exists, it will be validated against the newly created temporal table in terms of schema consistency and temporal data consistency. If you specify an invalid history table, the CREATE TABLE statement will fail.

Msg 2714, Level 16, State 6, Line 1

There is already an object named ‘DEPARTMENTHISTORY_ALREADYTHERE’ in the database.

Msg 13523, Level 16, State 1, Line 3

Setting SYSTEM_VERSIONING to ON failed because table ‘SQL2016_FeatureTest.dbo.Department’ has 6 columns and table ‘SQL2016_FeatureTest.dbo.DepartmentHistory_AlreadyThere’ has 1 columns.

3) Creating a temporal table with a user-defined history table

Creating a temporal table with user-defined history table is a convenient option when the user wants to specify a history table with specific storage options and additional indexes. In the example below, a user-defined history table is created with a schema that is aligned with the temporal table that will be created. To this user-defined history table, a clustered columnstore index and additional non clustered rowstore (B-tree) index is created for point lookups. After this user-defined history table is created, the system-versioned temporal table is created specifying the user-defined history table as the default history table.

Action	Temporal table with an anonymous history table	Temporal table with a default history table	Temporal table with a user-defined history table
Create Table with Temporal Table	CREATE TABLE Department_anonymous ( 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)	CREATE TABLE Department_WithHistoryTable ( 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) ) ;	CREATE TABLE DepartmentHistory_UserDefined ( DeptID int NOT NULL , DeptName varchar(50) NOT NULL , ManagerID INT NULL , ParentDeptID int NULL , SysStartTime datetime2 NOT NULL , SysEndTime datetime2 NOT NULL ); GO CREATE CLUSTERED COLUMNSTORE INDEX IX_DepartmentHistory ON DepartmentHistory_UserDefined; GO CREATE NONCLUSTERED INDEX IX_DepartmentHistory_ID_PERIOD_COLUMNS ON DepartmentHistory_UserDefined (SysEndTime, SysStartTime, DeptID); GO CREATE TABLE Department_UserDefined ( 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_UserDefined)) ;
Temporal Table is created along with main table:
Insert data into temporal table :	INSERT INTO DEPARTMENT_ANONYMOUS(DEPTID,DEPTNAME,MANAGERID,PARENTDEPTID) VALUES (111,’FINANCE’,502,101) INSERT INTO DEPARTMENT_ANONYMOUS(DEPTID,DEPTNAME,MANAGERID,PARENTDEPTID) VALUES (117,’WALFARE’,522,111) UPDATE A SET A.MANAGERID= 557 FROM DEPARTMENT_ANONYMOUS A WHERE A.DEPTID =117	INSERT INTO DEPARTMENT_WITHHISTORYTABLE(DEPTID,DEPTNAME,MANAGERID,PARENTDEPTID) VALUES (111,’FINANCE’,502,101) INSERT INTO DEPARTMENT_WITHHISTORYTABLE(DEPTID,DEPTNAME,MANAGERID,PARENTDEPTID) VALUES (117,’WALFARE’,522,111) UPDATE A SET A.MANAGERID= 557 FROM DEPARTMENT_WITHHISTORYTABLE A WHERE A.DEPTID =117	INSERT INTO Department_UserDefined(DEPTID,DEPTNAME,MANAGERID,PARENTDEPTID) VALUES (111,’FINANCE’,502,101) INSERT INTO Department_UserDefined(DEPTID,DEPTNAME,MANAGERID,PARENTDEPTID) VALUES (117,’WALFARE’,522,111) UPDATE A SET A.MANAGERID= 557 FROM Department_UserDefined A WHERE A.DEPTID =117