Friday, June 17, 2016

SQL Server 2016 Temporal Tables

SQL Server 2016 introduces a new feature ‘Temporal Tables’. Temporal tables, also named system-versioned tables, allow SQL Server to automatically keep history of the data in the table. Before you typically had to maintain this yourself using triggers or other solutions to keep older versions of your data round.

Note that temporal tables are not a replacement for the change data capture (CDC) feature. CDC uses the transaction log to find the changes and typically those changes are kept for a short period of time (depending on your ETL timeframe). Temporal tables store the actual changes in the history table and they are intended to stay there for a much longer time.

When you want to create a new temporal table, a couple of prerequisites must be met:

  • A primary key must be defined
  • Two columns must be defined to record the start and end date with a data type of datetime2. If needed, these columns can be hidden using the HIDDEN flag. These columns are called the SYSTEM_TIME period columns.
  • INSTEAD OF triggers are not allowed. AFTER triggers are only allowed on the current table.
  • In-memory OLTP cannot be used

A table_history table will be automatically generated when you create a system-versioned table. You can also convert an existing history table to a temporal table.

Great feature! Smile

Temporal-HowWorks

More information here: https://msdn.microsoft.com/en-us/library/dn935015.aspx

No comments: