Create SQL Server 2016 Temporal Table and History Table

SQL Server 2016 introduce temporal table aka system-versioned temporal table which contains current data and historical data to query data which changes with time. SQL Server 2016 temporal features enable SQL developers to query dynamic data at a particular point of time.

SQL Server vNext or known as SQL Server 2016 introduces a new type of database table named as Temporal and a secondary table type named as History. Besides of these two new table types (temporal and history tables), SQL Server Database Engine has enhancements in SQL syntax and new constructs to create, manage and query temporal data in SQL Server data platform.

Up to SQL Server 2016 temporal table structure is introduced, database administrators worked with table data which represents the current view. SQL Server did not provide a built-in structure which enables Transact-SQL programmers to query a table data to get its snapshot at a specific time in past. Now temporal features of SQL Server 2016 provide a built-in means for storing and querying dynamic data for its values at any point of time.

Microsoft claims temporal table enhancement as a time travel in data's history in SQL Server 2016.

Create Temporal Table in SQL Server 2016

Let's start examining SQL Server 2016 temporal table enhancement with creating our first current data and history table using the basic CREATE TABLE command.


create table currencyrates (

 id int identity(1,1) not null primary key clustered,

 fromCurrency char(3),

 toCurrency char(3),

 rate money,

 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)

Code

CREATE TABLE syntax is extended for temporal tables with addition of Period definition which requires two datetime2 fields and addition of System_Versioning = ON construct.

As developers can see the history table has the same columns with the temporal table (system-versioned table) which is the source keeping the current data.

SQL Server 2016 temporal and history table
Temporal (System-Versioned) table and History data

I know the history table name "MSSQL_TemporalHistoryFor_565577053" which is created automatically for the temporal table or system-versioned table is not a good choice for naming. Who wants to use MSSQL_TemporalHistoryFor_565577053 instead of a name like currencyratesHistory, or a similar name by just adding a tag at the end of the original table holding the current data.
In the following sections in this T-SQL tutorial I will share alternative methods to create temporal table table.

By the way, the number 565577053 following "MSSQL_TemporalHistoryFor_" is the object_id of the original database table (known as temporal or system-versioned table). Database administrators or SQL developers can query the sys.tables for the temporal and history tables using the SQL query in below section.

List Temporal and History Tables in a SQL Server Database

A SQL Server 2016 database administrator or a SQL developer can get the list of temporal tables created in a database by executing following SQL query.
In sys.tables system view, a self-join over history_table_id column will give us the system-versioned aka temporal table and its corresponding history table as pairs.


select

 t.object_id,

 t.name,

 t.temporal_type,

 t.temporal_type_desc,

 h.object_id,

 h.name,

 h.temporal_type,

 h.temporal_type_desc

from sys.tables t

inner join sys.tables h on t.history_table_id = h.object_id

Code

sql query to list temporal tables in a SQL Server 2016 database
Temporal (System-Versioned) and history table pairs list on SQL Server 2016

Create Temporal Table with History Table Defined

If you want to have a standart naming for the history tables of your system versioned (temporal) tables in your database, you can explicitely define the history table name. Please pay attention to the last line in the following CREATE TABLE syntax. Right after the "System_Versioning = ON", developers can define the secondary table as in demo script.


create table stockMaterial (

 id int identity(1,1) not null primary key clustered,

 materialId varchar(12),

 binId varchar(5),

 quantity int,

 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.stockMaterialHistory) )

Code

Now we have a nicer history table name of our temporal table.

temporal table and named history table in SQL Server database
SQL Server 2016 Temporal (System-Versioned) table and and history table explicitly set

Create History Table for existing Database Table

In this section of temporal table tutorial, I want to show how SQL developers can change an existing table into a temporal table. In other words, developers can learn if and how it is possible to create history table for existing database table.

Assume that in our sample database, we have following systemParameters SQL Server database table. I have shared the CREATE script of the SQL table.


create table systemParameters (

 ParameterId int identity(1,1) not null primary key nonclustered,

 Description varchar(100),

 Value nvarchar(400),

)

Code

Before we continue to our SQL tutorial on SQL Server temporal tables, please note that the temporal table aka the system-version source table must have a primary key defined. Otherwise while setting System_Versioning to ON or enabling the system_versioning, following error will be thrown by the SQL Engine.

System versioned temporal table 'master.dbo.systemParameters' must have primary key defined.

The first thing for the SQL database administrator or the SQL developer is to decide whether to create the history table manually or let it created automatically by the SQL Engine.

If you want to create the history table for your temporal table candidate, create a table which has exactly the same columns with the temporal table. Of course with two new additional columns which will be the system period datetime2 columns in both temporal and history table pair.


create table systemParametersHistory (

 ParameterId int not null,

 Description varchar(100),

 Value nvarchar(400),

 SysStartTime datetime2 Not Null,

 SysEndTime datetime2 Not Null

)

Code

Also note that the primary key is not created in the history table. It is not allowed for the identity columns in the history table as well.

Either you decided to create the history table manually or let the creation managed by SQL Server 2016, we have two add two datetime2 fields for validity start and end date identification on source temporal table. Additionally add missing period column which is the System_Time of the temporal table.
Before we create the System_Time, the start and end time columns should be added to the source table. We will use the same datetime2 field names that we used during creation of the history table here. If you did not create history table manually, you are free to use any name here.

Here is the ALTER TABLE script for adding the datetime2 data type columns, and adding the period column using these fields on the main database table.


ALTER TABLE systemParameters

Add SysStartTime datetime2 Generated Always as Row Start Not Null,

 SysEndTime datetime2 Generated Always as Row End Not Null,

 Period for System_Time (SysStartTime, SysEndTime)

Code

As the last step for creating the history table of an existing database table, we will enable system versioning on the temporal table. Temporal table is the source table which contains the current data.

To enable system versioning for an existing table, SQL Server 2016 database administrators or developers can execute ALTER TABLE command with SET and value ON for system_versioning settings. Since we have already created a history table manually, we have to pass the history table name using the history_table parameter.


alter table systemParameters

set (system_versioning = on (history_table = dbo.systemParametersHistory))

Code

If we did not choose to create history table manually, this means there is not a table named systemParametersHistory in the database. Above command will create automatically the history table for the temporal, system-versioned table.

If we created history table manually considering the above notes, above ALTER TABLE command will assign the history table to the temporal table and activate system versioning on the main database table.

Of course, if our SQL Server 2016 database administrator is too lazy to create the copy table (history table) or pass a history table name in the SQL ALTER TABLE statement, following command will also work. Unfortunatelly at the end of the execution, in our database there will be a new history table named in the format "MSSQL_TemporalHistoryFor_{object_id of temporal table}" like MSSQL_TemporalHistoryFor_1013578649


alter table systemParameters set (system_versioning = on)

Code

Here is an other note to take care: If you create history table manually, do not add SYSTEM_TIME period column as you do in the source temporal table. Otherwise SQL Engine will throw following error:

Msg 13574, Level 16, State 1, Line 23
Setting SYSTEM_VERSIONING to ON failed because temporal history table 'kodyaz.dbo.salesHistory' contains SYSTEM_TIME period.

After SQL developers and database administrators create temporal tables on a SQL2016 database, next step will be to create test data for temporal table on SQL Server 2016.