Friday, July 8, 2016

What is a temporal table in SQL Server?

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:


Notice the necessity of a history table, and the extra columns to keep track of time line of content.

While you can create a new temporal table you can also modify existing table to be temporal.

No comments: