Sign up for the ITPro Today newsletter
Stay on top of the IT universe with commentary, news analysis, how-to's, and tips delivered to your inbox daily.
Temporal Tables: SQL Server 2016’s Time Machine
Temporal tables enable you to view a table as it was at any given time. Essentially, temporal tables are system-versioned tables.
Michael Otey
June 20, 2016
3 Min Read
One of the most interesting new features that Microsoft added to SQL Server 2016 is temporal tables. Temporal tables originated as a part of the ISO/ANSI SQL:2011 standard. You can think of temporal tables as SQL Server’s time machine. Temporal tables enable you to view a table as it was at any given time. Essentially, temporal tables are system-versioned tables.
A table without system versioning only contains the current set of rows. You can query the table as it is in its current state but you cannot query past versions of the table or access rows that have been deleted. Temporal tables enable SQL Server to maintain a history of the data in the table when system versioning has been enabled. When versioning is enabled, SQL Server stores both the current and the past states of modified rows. SQL Server temporal tables have two explicitly defined datetime2 columns, which are referred to as period columns. These period columns are used to record the period of validity for each row. SQL Server is responsible for maintaining the different versions of the rows based on the time the user modifies the data.
Temporal tables enable several new capabilities including:
Data auditing – You can use temporal table to see what values a specific column has had.
Track slowing changing dimensions – One of the other uses for temporal tables is tracking slowing changing values where you can see the changes in a given piece of data over its lifetime.
Repair data corruption errors – You can also use temporal tables to correct data corruption by retrieving data from the history table and using it to overlay corrupted data in the base table.
Microsoft has added new FOR SYSTEM_TIME clauses, which let you return the data from a temporal table for a specific time or period. The FOR SYSTEM_TIME clause has several sub clauses that you can use to retrieve different sets of data.
AS OF -- Returns values for a specific data and time.
FROM TO -- Returns a range of values between the specified date and time.
BETWEEN AND -- Returns a range of values between the specified date and time.
CONTAINED IN (, ) -- Returns a range of values based in the supplied date and time values.
ALL – Return all values.
Requirements and Limitations
Temporal tables in SQL Server 2016 have a few prerequisites:
A primary key must be defined.
The table option SYSTEM_VERSIONING must be set to ON.
Two DATETIME2 columns must be defined to record the start and end date.
There are also some limitations:
In-memory OLTP cannot be used.
Temporal and history table cannot be FILETABLE.
INSTEAD OF triggers are not allowed. AFTER triggers are only allowed on the current table.
The history table cannot have any constraints.
Data in the history table cannot be modified.
Going Back in Time
Temporal tables enable SQL Server to automatically track the history of the data in a table. You might wonder how temporal table are different from Change Data Capture (CDC). CDC is intended to capture changes in a table and then feed those changes to some external process like an ETL job. CDC is not intended to keep data for long periods of time. Temporal table are designed for auditing purposes and they may keep historic data for very long periods. If you’re using temporal tables with large tables you might want to consider using Stretch Databases or partitioned tables to help manage the storage requirements.
HPE and Microsoft are the underwriters of this article.
About the Author
You May Also Like
