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

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>

capture20171019184815324

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

capture20171019193451893

Method 3: Altering an existing table to implement Temporal table

The below table dbo.Demo3 already exists

capture20171019194304832

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

capture20171019194022657

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 !!!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: