Concept and basics of Temporal tables in SQL Server 2016

In this article I’ll cover all aspects of a new SQL Server 2016 feature, Temporal Tables (System-Versioned), including:

Introduction
What is a temporal table?
Why Temporal table?
How does temporal table work?
Consideration and limitation
Temporal tables vs CDC
Creation and configuration
Clean up and removal
References

Introduction

AS we know, Microsoft released SQL Server 2016 RTM version (13.00.1601.5) and in November of 2016 updated it by the latest CU (Security Bulletin MS16-136 (CU) KB #3194717) (13.0.2186.0) you can check this update from here.

What are temporal tables?

Temporal (system-versioned) tables store and save data changes in a table so that you can return back to it in any time. This feature is very helpful, in day to day work, for developers because many times they asked about old backups to check on some data issues or changes. With this feature there is no need now since everything will be hosted in this system-version tables themselves. With temporal table the value of each record in any time can be determined easily rather than just the current value of each record.

Why Temporal tables?

Data auditing – to check the values of a specific column
Track slowing changing dimensions –to be able to track the slowing changing values
Repair data corruption errors –we can retrieve the data from the historical data and by this we can fix any corrupted data
Real data sources are dynamic
No change in programming model or effect on the application

And one of the biggest advantages is that we don’t need to do workarounds to retrieve historical data and we don’t need to create custom data tracking. We just need to focus on the business logic of the solution and use the newly introduced query construct to query the data.

How does temporal table work

After enabling the temporal table feature, in any table, SQL Server 2016 will manage the two tables; the current table and the historical table (System-Versioned). The current table (physical table) will contain the last data, after the modification, and the historical table will contain the pervious values for each row after the modification.

SQL server 2016 introduced query constructs query data from these tables in a single query by using one of the following operations.

Point in time: AS OF <date_time>
Exclusive bounds: FROM <start_date_time> TO <end_date_time>
Inclusive lower bound, exclusive upper bound: BETWEEN <start_date_time> AND <end_date_time>
Inclusive bounds: CONTAINED IN (<start_date_time> , <end_date_time>)

Considerations and limitations:

There are some requirements and limitations for temporal tables we should be aware of including

A Primary Key is required in the current table (System-versioned)
A History table must be created in the same database as the current table
Linked servers are not supported
INSERT and UPDATE statements cannot reference SYSTEM_TIME period columns
The Truncate table operation is not supported on temporal tables
Always On supported
A System-Versioned table does not allowed any constraints
You can’t modify the data in history table “System-Versioned”
Durable memory-optimized tables can be system-versioned
Temporal table support Partitioning and column store index

For more information check this Microsoft Reference.

Temporal tables vs CDC

It depends on your business case as each feature has different functionality and usage

Temporal Tables can give us a version of the current table at any point in time
Change data capture change tracking (aka CDC) can help us for determining changes but it will not help us for reviewing (or working with) historical data

Creation and configuration

Create a new table with the enabled system-versioned table feature

To do it we should consider some important things in our implementation for this table

System-Versioned table should have primary key to manage the relation between current table and historical table
The Table should have two columns with data type DATETIME2 to use for the start and end of the row period
Two columns used in the row period with data type (DATETIME2) should be NOT NULL

These two-period columns we can mark as HIDDEN columns, and they will not appear in the results:

USE SQLSHAKDEMO

---Creating a Temporal Table

CREATE TABLE [dbo].[Countries](

[CountryID] [int] Primary KEY IDENTITY(1,1) NOT NULL,

[CountryArName] [nvarchar](255) NULL,

[CountryEnName] [varchar](255) NULL,

[IsReserved] [bit] NOT NULL CONSTRAINT [DF_Countries_IsReserved] DEFAULT ((0)),

[StartTime] DATETIME2 GENERATED ALWAYS AS ROW START

HIDDEN NOT NULL,

[EndTime] DATETIME2 GENERATED ALWAYS AS ROW END

HIDDEN NOT NULL ,

PERIOD FOR SYSTEM_TIME (StartTime, EndTime)

)

WITH(SYSTEM_VERSIONING=ON (HISTORY_TABLE=dbo.CountriesHistory))

Now, after we executed the above script, if we opened SQL Server management studio we will find the new table as system-versioned, and inside this table you will find the corresponding historical table for it.

DML operations on Temporal Tables:

Insert: we will push some data in the table “Countries” by using simple insert statement:

Use SQLSHAKDEMO

INSERT INTO [dbo].[Countries]

([CountryArName]

,[CountryEnName]

,[IsReserved])

VALUES

('Egypt'

,'Egypt'

,1)

I haven’t mentioned the two DATETIME2 columns (Starttime , Endtime) because they are implemented as hidden columns and won’t impact anything in the application

After we executed the above Select statement we found the data inserted in the source table but there was not any record in the historical table. By default, when selecting using ( * ) the hidden period columns don’t not appear. To query these columns you must reference them explicitly, like this:

Select * , Starttime , Endtime from [Countries]

Update:

Let’s EXEC this update statement to update the CountryEnName and the check the execution plan to see which tables are affected by this statement:

Update [Countries] Set

[CountryEnName] = 'KSA'

where CountryID = 1

As we see in the execution plan, this update statement inserted data in the historical table. Now let us check the historical table:

We will discover that the data that existed before the record was updated was inserted into the historical table and we can found the StartTime and EndTime for this operation in the historical table:

Cleaning up temporal tables

Delete

Delete from [dbo].[Countries] where CountryID = 1

After the execution of the delete statement you will find that is affected on the both tables {Current table, historical table}

Truncate

Now if we need to truncate the source table “temporal table” it will not work

TRUNCATE TABLE dbo.[Countries]

Note: We can’t also do any DML operation on the history table “system-versioned”

Drop

To drop the temporal table, we should do follow the below sequence to be able to drop the table

First if you executed the simple drop statement like this it will not work.

Drop table [dbo].[Countries]

So we need to follow the below sequence for cleaning the Temporal table:

ALTER TABLE dbo.Countries SET (SYSTEM_VERSIONING = OFF)

ALTER TABLE dbo.Countries

DROP PERIOD FOR SYSTEM_TIME

DROP TABLE dbo.Countries

DROP TABLE dbo.CountriesHistory

Mustafa EL-Masry

Mustafa EL-Masry is a Senior database consultant and one of the experts in Database performance tuning in the Middle East.

Currently, he is working as a Senior consultant production DBA and Development DBA in many projects in multiple government sectors. He is a Top SQL Server blogger in the Middle East, founder of the community mostafaelmasry.com, and is the second Arabic author on Microsoft MSDN in SQL Server.

Based on his current position, he solved fairly interesting problems on fairly large databases and highly sensitive performance cases.

View all posts by Mustafa EL-Masry

Latest posts by Mustafa EL-Masry (see all)

Concept and basics of DBCC Commands in SQL Server - March 31, 2017
Hybrid Cloud and Hekaton Features in SQL Server 2014 - February 28, 2017
How to analyze Storage Subsystem Performance in SQL Server - February 23, 2017