The term “temporal data” refers to records that are versioned in the database. For any given logical record, there is a current version and zero or more prior versions. Both the current and any previous versions are represented by physical rows in the database, though not necessarily in the same table.
Data integrity is hard when working with temporal tables. Each time a row is updated, there needs to be a way to ensure the current versions of the row in copied into the prior version table. This can be done with triggers or stored procedures, but both have their problems.
Querying temporal data is likewise challenging. Though you can easily get the current version of a logical record, retrieving the version of a particular data requires a complex and often error prone query. This often leads developers to invest in databases that are specialized for this type of workload.
SQL Server 2016 is offering another alternative, the new Temporal Table object. Superficially, a Temporal Table looks just like a normal table. It supports most column types, normal indexes, columnstore indexes, foreign keys, etc. CRUD style operations work as expected using normal SQL or an ORM. In fact, you can convert most normal tables into Temporal Tables without changing the stored procedures and applications that use said table.
Implementation wise, a Temporal Table is really two tables. One table contains the current values while another handles the historic versions of the data. The tables are linked so that any UPDATE or DELETE operation in the normal table automatically creates a corresponding history row. (INSERT operations don’t create historic records.)
Accessing Historic Data
You can query a history table directly, but since it doesn’t contain the current values you wouldn’t normally touch it. Instead, you should always query the base table using one of the following operations.
- Point in time:
AS OF <date_time> - Exclusive bounds:
FROM <start_date_time> TO <end_date_time> - Inclusive lower bound, exclusive upper bound:
BETWEEN <start_date_time> AND <end_date_time> - Inclusive bounds:
CONTAINED IN (<start_date_time> , <end_date_time>)
For example, if you want to know which value was active for customer 27 on the first of the year you could write:
… FROM Customer FOR SYSTEM_TIME AS OF '2015-1-1' WHERE CustomerID = 27
If instead you want to see every version of the users records for that day you could write,
… FROM Customer FOR SYSTEM_TIME BETWEEN '2015-1-1' AND '2015-1-2'WHERE CustomerID = 27
Design Rules
- Temporal tables need to have a SysStartTime and SysEndTime column, both being a non-nullable DateTime2. These columns can be named anything and are managed by SQL Server; users are not allowed to insert or update values into these columns.
- The column type FILESTREAM is not supported, as it stores data outside of the database.
- For table Foo, the history table will be named “FooHistory” by default. This can be overridden.
- The history table cannot be modified directly, you can only add to it by updating or deleting data from the current table.
- INSTEAD OF triggers are not supported and AFTER triggers are only allowed on the current table.
Indexing must be manually enabled. Microsoft has some recommendations on this,
An optimal indexing strategy will include a clustered columns store index and / or a B-tree rowstore index on the current table and a clustered columnstore index on the history table for optimal storage size and performance. If you create / use your own history table, we strongly recommend that you create such an index that consists of the primary keys from the current table plus the period columns to speed up temporal querying as well as the queries that are part of the data consistency check. If your history table is a rowstore, we recommend a clustered rowstore index. The default history table has a clustered rowstore index created for you. At a minimum, a non-clustered rowstore index is recommended.
Schema Modifications
You cannot change the schema of a Temporal Table. You can, however, convert the Temporal Table into two normal tables using SET (SYSTEM_VERSIONING = OFF) in an ALTER TABLE statement.
Once you do that, you can modify the two tables and then turn them back into a Temporal Table with SET (SYSTEM_VERSIONING = ON). Note that this statement needs to include the name of the history table and the two sys time columns.
Correction: An earlier version of this article incorrectly stated that the FOR SYSTEM_TIME expression was part of the WHERE clause when in fact it is part of the FROM clause.