An Introduction to Temporal Tables


Going along the new features of SQL Server 2016, we’re going to take a look at temporal tables. This feature adds the ability to track historical values in a table.  It’s different than Change Data Capture in that is simply tracks values as opposed to specific operations performed.  It’s also much simpler to set up.  Way, way, way simpler to set up.  Let’s take a look.

How do temporal tables work?

Simple in nature, when implemented, a secondary table is created to store historical values through two Start and End datetime2 columns.  Both the ‘live’ or ‘current’ and the historic table have these two columns.  Their behavior during various DML operations can be summarized in the table below. Merge statements follow the Insert/Update/Delete patterns based on their operation.
Operation
Current Start Time
Current End Time
History Start Time
History End Time
INSERT
UTC time of Insert
Max value of 9999-12-31 23:59:59.9999999
UTC time of Insert
Max value of 9999-12-31 23:59:59.9999999
UPDATE
UTC time of Update
Max value of 9999-12-31 23:59:59.9999999
New record - UTC time of update
Previous record is updated with UTC time of Update, new record has the max value set
DELETE
Record is deleted
Record is deleted
No change
Newest record has end time set to UTC Date of delete statement

Creating temporal tables

Temporal tables are very simple to create.  You can create them alongside new tables or implement them to existing tables.  Here’s an example of creating a temporal table from scratch:

CREATE TABLE [dbo].[Account](
    [AccountID] [int] NOT NULL PRIMARY KEY CLUSTERED,
    [Balance] [decimal](10, 2) NULL,
    [SysStartTime] [datetime2](7) GENERATED ALWAYS AS ROW START NOT NULL,
    [SysEndTime] [datetime2](7) GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime])
) ON [PRIMARY]
WITH
(
SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[AccountHistory] , DATA_CONSISTENCY_CHECK = ON )
)
GO

There are a few key areas and words to add, but all in all it’s relatively simple.  Here are the steps to update an existing table.  The requirements here are two not nullable datetime2 columns designated as ‘generated always’ and ‘row start/end’, and PERIOD FOR SYSTEM_TIME specifying the datetime2 columns.  Additionally, you must set system_vesioning on. Specifying the history table is actually optional, and SQL Server will generate a name if not included.  DATA_CONSISTENCY_CHECK is optional as well. This option is on by default and verifies there are no entries in the history table where the start system time is greater than the end system time for a row.
Adding a temporal table to an existing table is easy as well. 
ALTER TABLE Account
ADD PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime),
SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL DEFAULT getutcdate(),
SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL DEFAULT getutcdate()
GO
ALTER TABLE Account4
SET (SYSTEM_VERSIONING = ON);

For existing history tables, the datetime2 columns must be added or existing columns must be altered to datetime2, the PERIOD and system version statements must be executed.

Querying temporal tables

If you work in management studio, you will notice something immediately about temporal tables.  The history tables aren’t listed directly under the database tables tree.  They are actually located one step down, within the normal table’s tree.  The normal and history tables have ‘System-Versioned’ and ‘History’ in parenthesis appended to their names, respectively.  




















Querying the tables is straight forward.  You can query them individually, join them, do most of the things you can to a normal table. For the simplest historical queries, you can add a simple FOR SYSTEM_TIME clause as part of the FROM statement to your query.  This clause specifies that specified version of data is returned from the table’s corresponding temporal table. Complete the FOR SYSTEM_TIME with one of the following statements:
·         AS OF <date_time>
·         FROM <date_time> TO <date_time>
·         BETWEEN <date_time> TO <date_time>
·         CONTAINED IN (<date_time>, <date_time>)






































Limitiations

There are some limitations to the DDL statements you can execute while a table is participating in system versioning.  To circumvent these, you must disable system versioning, perform your DDL and re-enable system versioning.  

That's a quick intro to temporal tables.  As you can see, they're very simple to create and work with.  If anyone is interested in reading more on temporal tables, here's a link to the msdn page. https://msdn.microsoft.com/en-us/library/dn935015.aspx?f=255&MSPPError=-2147217396




Labels: , , ,