If you've ever designed a database you've most certainly run into the need for what we called history tables, those tables that stored the history of row changes (sometimes the whole row and sometimes just certain columns) and that were populated through insert/update/delete triggers defined on the "parent" table. With SQL Server 2016 there's no need to manually implement history tables anymore, you just define a table as a system-versioned temporal table and let the SQL Server engine take care of maintaining the row change history.
With system-versioned temporal tables pulling data from the current and the associated history table at the same time is very easy and efficient using the FOR SYSTEM_TIME clause.
System-Versioned temporal tables are supported on SQL Server 2016 and SQL Azure.
This is all you need to know in order to decide if and when you may need to use temporal tables.
PS our Schema and Data Compare tools support comparing and synchronizing System-Versioned Temporal tables.
------------------------------------------------------------------------------------------------------------------
If you wish to spend more than 60 seconds here is a brief explanation on how this works.
- The live, temporal table has two explicitly defined datetime2 type columns referred to as period columns that are used exclusively by the system to record period of validity for each row whenever the row is modified (the columns are typically denoted as SysStartTime and SysEndTime)
- The temporal table contains a reference to the history table (SQL Server can automatically create the history table or you can specify it yourself)
- ON INSERT on the temporal table the system sets the SysStartTime to the begin time of the current transaction and SysEndTime to max value (999-12-31)
- ON UPDATE/DELETE on the temporal table
- a new row is inserted in the history table with the SysStartTime coming as is from the temporal table and the SysEndTime being set to the begin time of the current transaction.
- On the live table either the row is deleted (in case of a delete operation) or the SysStartTime is updated to the begin time of the current transaction (in the case of an update).
More details from the source:
- Creating a System-Versioned Temporal table: https://msdn.microsoft.com/en-us/library/mt590957.aspx
- Querying System-Versioned Temporal tables: https://msdn.microsoft.com/en-us/library/mt591018.aspx
