SQL 2016 : Temporal Tables

Posted: 05/09/2015 in SQL 2016
Tags: ,

This is another interesting feature which Microsoft has introduced as part SQL 2016 release. We had traditional methods like magic tables and CDC to audit and log the history of a table, with the introduction of this feature Microsoft is bringing up new detailed feature called temporal tables. note this is not replacement for CDC, this feature help to track the life span of each row in a table.

Temporal means “relating to time” – all we are going to log is life span of each record. If you enable the property on a table, SQL will create a version table and your each row history would be kept in the newly created table. So with this feature I can easly identify that there was a row exist with these values in a particular time.


In the above screenshot you can see the create statement on the top of the query window, the statement is as same as we use to create table. But to enable the property we should mention WITH(SYSTEM_VERSIONING = ON) to create a history table and also we have to add 2 more extra columns to store the life span of each record(starttime  and endtime).

In the above example I have created a table named TestTemporal, since the system version property is on, SQL creates a history table as shown in the figure. The history table contains the same number of columns and the history being stored in this table.

As shown in the picture I have inserted 2 rows to the table and updated a row and also deleted one of them, after these operations I have selected my TestTemporal and it’s history table. In the history table’s result we can see the old inserted and deleted record with the period those records were present in the main table. In the main table record end time is showing as infinite because the record is still exist in the table.


Now let discus about design changes on a version-ed table, if you create a table system version property on, you will not be able to delete of change the table definition without changing the property of the table. If you change the version-ed table to normal table, the history table will be converted to a normal table, once we revert back normal table to version table then a new history table will be created.

Hope you understand the feature, please do leave your comments.

Watch Demo to know more about temporal tables.


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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s