Temporal Tables–Simple Guide to implement History Tables using SQL Server 2016/2017

Temporal Tables is a new feature introduced in SQL Server 2016, which makes database/application developers life easy to implement History feature in their application without having to write Triggers or any extra logic in stored procedures

Method 1: Creating Temporal Tables for New Table without explicitly specifying History table name

Please add these 3 columns to your table definition and use WITH (SYSTEM_VERSIONING = ON)

CREATE TABLE dbo.Demo
(
DemoID int NOT NULL PRIMARY KEY CLUSTERED
, DemoName varchar(50) NOT 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);

In below screenshot, you can see that Demo table is created with History table, but History table name is generated by SQL Server itself, the default naming conversion is dbo.MSSQL_TemporalHistoryFor<OBJECTID of Table>

Method 2: Creating Temporal Tables for New Table with explicit History table name

CREATE TABLE dbo.Demo2
(
DemoID int NOT NULL PRIMARY KEY CLUSTERED
, DemoName varchar(50) NOT 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.Demo2History));

In the below screenshot, you can see the dbo.Demo2 table is created with History table name

Method 3: Altering an existing table to implement Temporal table

The below table dbo.Demo3 already exists

ALTER TABLE dbo.Demo3 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)

ALTER TABLE dbo.Demo3
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Demo3History))

In below screenshot you can see Demo3 table is modified to include

In next post, we will see how INSERT/UPDATE/DELETE operation ran on main table generates History data and how we can access it

Hope you find this post helpful !!!

Author: Arunraj

I am a Microsoft Certified Technology Specialist (Database Developer). I work on SQL Server programming since SQL Server 7.0 specializes in SQL Server Programming and Performance Tuning and has 14 years of hands-on experience. I hold a Master Degree in Computer Applications. I am also part of NJSQL User Group and Northern New Jersey .Net User Group. View all posts by Arunraj