System-Versioned Temporal (Time Travel) Tables in SQL Server 2016

SQL Server 2016 introduces some significant new features. An interesting enhancement is System-Versioned Temporal Tables. Microsoft say these can be used to “Time Travel” a table without the need for a Flux Capacitor. These tables look just like any other tables but can be queried in a rather interesting way; you can query them to return how they looked at a previous date and time. The SELECT statement has a FOR clause that can be used as a simple “at this specific point in time” or it can be a more complex time range. Interestingly, if a range is used, then multiple versions of the same row could be returned with duplicate values in the primary key column.

 

A System-Versioned Temporal Table has two extra columns. One is a “valid from” datetime2 field and the other is a “valid to” datetime2 field. These columns are automatically populated. Additionally, a second table is created as a history table that automatically stores previous versions of rows from the table. The results of a query can include current rows from the temporal table and previous versions of rows from the history table. I am sure you can think of interesting, and perhaps useful, applications for this functionality including, amongst others, data auditing and trend analysis. Of course, the history table will use a large amount of disk storage which may give you problems.

 

I should just add that Microsoft did not invent System-Versioned Temporal Tables. They were included in the ISO/IEC 9075:2011 standard and was first implemented in IBM DB2 and Oracle Server. Nevertheless, it is still nice to see them in SQL Server. For more information about System-Versioned Temporal Tables, please attend our SQL Server Querying and Programming course!