Problem Statement –

Historical data are a key source for trend analysis and are considered critical to business success. Traditional databases don’t provide insights into how data has changed over a period of time. There are workarounds to achieve this, however they are complex and expensive. Also there is always a chance of incorrect update of archive tables and the related performance hit.

As a developer, imagine if SQL Server provides this information out of the box, and there is no need to write any custom code to achieve this functionality. Wouldn’t it be awesome? Think about how much time this would save, if SQL Server is automatically able to track data changes. Developer’s life will be so good if they don’t have to deal with all the heavy duty work of implementing the data archival logic by using Triggers and other level of customization.

What is Temporal Table?

Temporal Table in SQL Server 2016 provides inbuilt support for tracking old versions of data over a period of time, without any need for additional programming.

A temporal table or system-versioned table is a new type of user table that holds the entire history of data changes. When the data in the base table changes, SQL Server engine automatically moves the old row versions into a history table.
Besides the base table, a temporal table contains a reference to a ‘history’ table, with an exact mirrored schema.

Why is Temporal required?

Time Travel and better insight into historical data —
Temporal gives us the ability to reconstruct the state of the data, as it was at a point of time. We have the ability to find out how the database looked like 6 months back.

Analyzing trends over a period of time —
Business can generate a particular type of report on today’s date and a similar report 3 months back and compare the trend.
It assists in historical data analysis.

Simplify Data Audit –
It tracks all changes that has happened to your data – who/what/when data changed. Every record in the table has full history of its state.

Correct any data corruption due to manual mistakes –
We have the provision of going back to a point of time and fetching the correct data, which got wiped out/corrupted and correct the current state of the data.

How Temporal Data works?

Temporal is a new table type and is implemented as a pair of tables —

• Temporal/Primary table – Contains the current state of data.
• History table – Contains old versions of data. A record is inserted with time stamp when the actual table is modified(Row Update or Delete operation)

A Temporal table has 2 period columns of ‘datetime2’ data type – StartTime and EndTime. These columns are used by the SQL database engine to record the validity of each row, whenever the row is modified or deleted.

While doing an INSERT Operation, the Start Time is set as the begin time of the current transaction(UTC time zone) and End Time is set as the maximum value, by default , 9999-12-31.
SQL is smart to figure out when to pull data from History Table vs Actual Table. From a user perspective you will always be querying against the Actual Table.

How to create a Temporal table?

1. The simplest way is to create a temporal table with an ‘anonymous’ history table.Once we create the temporal table, the history table is automatically created –

In the Object Explorer, when we check the Temporal table, we will notice the nested History table with the auto naming convention –

MSSQL_TemporalHistoryFor_(TemporalTableObjectID)

Let’s go ahead and drop the existing tables. We need to note that to drop the tables, first of all we would need to mark the SYSTEM_VERSIONING as OFF, else it wont allow modifying the table schema –

2. Another way to create a temporal table is with a ‘named’ History table – ‘dbo.EmployeeHistory’ –

In the Object Explorer, when we check the Temporal table, we will notice the nested History table with the expected name –

Real time Demo of Time Travel and viewing historical data –

Let us insert some data into our temporal table —

We see that there are 3 rows in the Primary table and no rows in the History table (since no updates have been made to the Primary table yet)

Now let us make some updates to the Primary table and see how SQL Server Engine updates the History table –

We see above that after the updates, the Primary table contains 2 rows and the History table contains 2 rows of the older version of data ( 1 modified row and 1 deleted row)

How to access Historical data?

To access historical data, you need to query the base table itself, and not the history table. We can use the FOR SYSTEM_TIME clause with one of the below Temporal extensions to perform any time-based analysis –

• 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>)

Now I will illustrate an example to fetch the historical data using the temporal extension –AS OF

If we want to reconstruct the state of the data at any specific time in the past, we can use the AS OF clause. In the example below, I am trying to reconstruct the data as of ‘2016-05-04 02:34:09.9419923’

We see above that the History table contains the previous state of the data before the Update and Delete statements were run.

If we generate the Execution Plan of the above query, we will see that even though we are querying the Primary Temporal table, SQL Server Engine internally queries both the Primary and History table and concatenates the result –

In the example below, I want to reconstruct the data for EmployeeID = 2 at a specific time in the past –

We see above that the History table exactly shows the previous state of the row at a point of time.

I have created a new Gist containing the script used for the purpose of this article in GITHUB.

Conclusion –

With SQL Server 2016 providing Temporal tables as an ‘out of the box’ feature, it eases out life of both developers and database administrators. We now have an easy access to time-varying data, without any need of custom code or workarounds. This is one of the powerful features of SQL Server 2016, and I am sure will gain lot of love and appreciation from the community.

My other related articles on SQL Server 2016 –

Built-in JSON Support in SQL Server 2016

Compare Execution Plans in SQL Server 2016

Query Store in SQL Server 2016

Live Query Statistics in SQL Server 2016

Categories: SQL Server, SQL Server 2016