SQL Server 2016 – Temporal Tables Part 1

Hello Friends,

Several new features announced by Microsoft in SQL Server 2016. We have seen a few of them in my previous blog posts. In the same row, we will bring a look today on next interesting feature i.e. SQL Server 2016 – Temporal Tables. Actually, temporal feature was  introduced in ANSI SQL 2011. Microsoft has taken some time to implement it and announced this feature in SQL Server 2016. This feature is available in all editions of SQL Server 2016.

Usually, tables in the SQL Server databases stored the current state of data. For example- let’s say we have a table which stores the details of customers. I made a change in customer contact number yesterday. Can I query that same table to see the contact number of the customer along with the contact number before changes made by me yesterday?  By using system-versioned temporal tables, we can see all these details very well.

The full name of this feature is system-versioned temporal tables. Here the term ‘system versioned’ means SQL Server will maintain the period of validity for the row(s). Due to this we have to define two columns with datetime2 data type. These columns will store the period information and these columns will be used by SQL Server internally.

If we want to make a table as a temporal table then we have to create that table along with “WITH (SYSTEM_VERSIONING = ON)”. Let me show you the same thing practically.

------------------ Step 1: Create temporal database -----------------
CREATE DATABASE TEMPORAL;
GO
------------------ Step 2: Create temporal table -----------------
USE [TEMPORAL]
GO
CREATE TABLE TemporalTableDemo
(
	EmpID BIGINT NOT NULL PRIMARY KEY, --One Primary Key is mandatory
	FName VARCHAR(100) NOT NULL,
	MName VARCHAR(100) NOT NULL,
	LName VARCHAR(100) NOT NULL,
	DeptID INT NOT NULL,
	-- two columns with datatime2 data type specified as GENERATED ALWAYS AS ROW START/END
	LifeStart DATETIME2 GENERATED ALWAYS AS ROW START, 
    	LifeEnd DATETIME2 GENERATED ALWAYS AS ROW END,
    	PERIOD FOR SYSTEM_TIME (LifeStart, LifeEnd)
)
WITH (SYSTEM_VERSIONING=ON)
GO

Here LifeStart and LifeEnd with period for system_time both will be used by SQL Server internally to store the valid period information. Both of these columns can not be specified as NULL. Till now, almost everything looks like the normal database table.

Due to SYSTEM_VERSIONING=ON, by default, SQL Server will automatically:

• Creates an another table named as MSSQL_TemporalHistoryFor_<TemporalTableObjectID> under the same schema with same structure except constraints.
• Creates a row store clustered index as ix_MSSQL_TemporalHistoryFor_<TemporalTableObjectID>

This automatically created table will store all the information about data changes. You can see the same detail in Object Explorer:

Here the name of the automatically created table is not looking good and even not readable because of object id of the table. You can specify the name for history table by using HISTORY_TABLE as mention in below TSQL code:

---------- Step 3: Create a temporal table with specified history table ---------
USE [TEMPORAL]
GO
CREATE TABLE TemporalTableDemo2
(
	EmpID BIGINT NOT NULL PRIMARY KEY, --One Primary Key is mandatory
	FName VARCHAR(100) NOT NULL,
	MName VARCHAR(100) NOT NULL,
	LName VARCHAR(100) NOT NULL,
	DeptID INT NOT NULL,
	-- two columns with datatime2 data type specified as GENERATED ALWAYS AS ROW START/END
	LifeStart DATETIME2 GENERATED ALWAYS AS ROW START, 
    LifeEnd DATETIME2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (LifeStart, LifeEnd)
)
WITH (SYSTEM_VERSIONING=ON (HISTORY_TABLE = dbo.History_TemporalTableDemo2) )
GO

In the next blog post we will look into some more details about temporal tables.

HAPPY LEARNING!

Regards:

Prince Kumar Rastogi

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook

Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook