While temporal data support is something that has existed in the past within other database platforms, it is a newly available feature with the RTM version of SQL Server 2016. In case you haven’t heard of temporal data values (or for some, “bitemporal”), here is a brief explanation. When querying a database table, the result set is typically consistent with the current time, that is, at the time the query is executed. However, the ANSI SQL 2011 update to the standards specified that relational databases should also support the ability to query the table from the point of view of any point in time.
In a regular table, when I query data I’ll get a result set that is consistent with the data as of today. However, with temporal data, when I query data I could ask for a result set that was consistent with some other point of time in the past. For example, I could query all of the employees from the HumanResources schema and the Employee table in this way:
SELECT e.BusinessEntityID, e.ValidFrom, e.ValidTo
FROM HumanResources.Employee AS e
WHERE e.BusinessEntityID = 4;
But that would show me the state of the data as-of today. What if I wanted to perform the same query, as some other time in the past? In such a situation, SQL Server 2016 now allows you to write queries that look similar to this:
SELECT e.BusinessEntityID, e.ValidFrom, e.ValidTo
FROM AdventureWorks2016.HumanResources.Employee AS e
WHERE e.BusinessEntityID = 14
FOR SYSTEM_TIME AS OF ‘20160619 19:03:00’;
By issuing the SELECT statement against a temporal table FOR SYSTEM_TIME AS OF ‘20160619 19:03:00’, I’ll get a result set consistent with that point of time rather a result set consistent with the here and now. You can also query FOR SYSTEM_TIME FROM <start_date_and_time> TO <end_date_and_time>, FOR SYSTEM_TIME BETWEEN <start_date_and_time> AND <end_date_and_time>, FOR SYSTEM_TIME CONTAINED IN ( <start_date_and_time> , <end_date_and_time> ), or even ALL to return a union of all rows belonging to the current and history table.
Using Temporal Tables in Your Database Design
Temporal tables require a bit of preparation during your database design phase. Basically, you’ll want to enable system-versioning for the source table to a history table which you’ve decided will be temporal. That being the case, you’ll need a couple additional columns of datetime2 data type, the VALIDFROM and VALIDTO columns, which tells SQL Server which snapshot of the temporal table to use when querying for data from a specific time in the past. Thus, your CREATE TABLE might appear similar to this:
CREATE TABLE HumanResources.Employee
(
[employee_id] int NOT NULL PRIMARY KEY CLUSTERED
, [name] nvarchar(100) NOT NULL
, [position] varchar(100) NOT NULL
, [department] varchar(100) NOT NULL
, [address] nvarchar(1024) NOT NULL
, [annual_salary] decimal (10,2) NOT NULL
, [valid_from] datetime2 (2) GENERATED ALWAYS AS ROW START
, [valid_to] datetime2 (2) GENERATED ALWAYS AS ROW END
, PERIOD FOR SYSTEM_TIME (valid_from, valid_to)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = HumanResources.EmployeeHistory));
Once defined, SQL Server will automatically keep up with the rest. Note that there are several special rules about using temporal tables. For example, you may not create INSTEAD OF triggers on a temporal table.
Be sure to read the detailed Books OnLine article about temporal tables before you begin. To learn more, start reading here.