Friday, July 8, 2016

Temporal tables in SQL Server 2016 to track history

A temporal table helps you to access historical changes to the content of the table. It is primarliy for Azure SQL Database table but it can also apply to SQL Server 2016. In fact it is one of the new features of SQL Server 2016.

This is what a temporal table in SQL Server 2016 according to Microsoft:
"SQL Server 2016 introduces support for system-versioned temporal tables as a database feature that brings built-in support for providing information about data stored in the table at any point in time rather than only the data that is correct at the current moment in time. Temporal is a database feature that was introduced in ANSI SQL 2011 and is now supported in SQL Server 2016
"
This is how it looks like in Object Explorer with the temporal table using an anonymous history table:


TempTbl_01

Notice the necessity of an history table, and the extra columns to keep track of time line of content. The temporal table and the history table has the same schema which means schema modification would not be possible.

There are three ways to create temporal tables depending on how the history table is created:
Anonymous history table
default history table
user-defined history table


While you can create a new temporal table you can also modify existing table to be temporal.
If you want to use an existing table as temporal table you will use the Alter Table to add the necessary temporal attributes.

Read more here.