Temporal tables are a feature added in SQL Server 2016 that allows us to track the history of a record, and to be able to query the data at a selected point in time.
To enable the temporal features there a a few columns and settings to add when we create (or alter) a table. Here is an example table:
create table TestTable ( RecordId int not null identity(1,1) primary key, RecordName varchar(20) not null, CreatedAt datetime not null, StartTime datetime2 generated always as row start not null, EndTime datetime2 generated always as row end not null, period for system_time (StartTime, EndTime) ) with (system_versioning = on)
So we add two datetime2 columns, one to store the starting effective date (StartDate) and a second for the ending effective date (EndDate). Any name can be used for the columns, the ‘generated always as row start/end’ will designate their purpose. We also include a period clause to show these two columns together will represent the range that the record is (or was ) active. Lastly, we turn in system versioning. We can later alter the table to turn off versioning, if we need to alter the table’s schema, for example.
This process will create a history table that will capture any changes, additions or deletions made. The history table isn’t queried, we can write queries against the base table and designate if we want to see historical data.
The EndDate will come back as ‘9999-12-31 23:59:59.9999999’ for the active records. Otherwise, the EndDate will be the date/time when the record was updated or removed. Both start and End values are in UTC time.
So there are several different ways to query for historical information. Probably the most useful is using ‘as of’ which will return how records looked at a specific point in time.
select * from TestTable for system_time as of '2015-07-19 17:45:00';
Or we can specify a range of datetimes to return all records that were active within that range:
select * from TestTable for system_time from '2015-07-19 17:45:00' to '2015-07-19 17:50:00';
There are other query options, but these two seemed to be the most useful.
You’re allowed to change the primary key values in a temporal table, which could be a little confusing when trying to view the record history. Performing this update will set the EndDate for the original record, so in the history it will look like the record was deleted and a record with the new ID was inserted. The Start date for the new record and the End date for the original record will be the same, so perhaps matching the two will allow a user to figure out what happened in the case of an audit.
A company I used to work at implemented functionality similar to Temporal Tables, where a trigger would record any changes to an Archive table. These triggers would roll back if a user attempted to update the primary key value(s), in order to keep the audit chain for that record, the record would have to be explicitly deleted.
CDC (Change Data Capture) is a similar feature that was added in SQL Server 2012 to track data changes. When enabled, it also captures changes made to a record and allows this table to be queried. It stores the operation type that caused the change, which Temporal Tables doesn’t. However, CDC makes is a little more difficult to determine when a change was made. You would have to link to a separate table using LSN values to link to a transaction. So it would be possible to write a query to see the values at a certain point in time, but Temporal Tables do make it a lot easier to do this.
Set SYSTEM_VERSIONING off to drop table – doesn’t drop the archive table