SQL Server 2016 Temporal Tables and Type II Data Warehouse Dimensions

SQL Server 2016 introduces temporal tables, an interesting and “data warehouse-y” addition to the relational database.

So how do temporal tables work? They add a couple columns to a table that are used to track the date a row is inserted or last updated, based on the primary key. Suppose you have a row that looks like this:

If you begin tracking this data temporally, the row could appear similar to this:

Note the dateTime2 data type used for the date-range fields. A ToDate value of 9999-12-31 23:59:59.9 indicates this row is “current.” The value in the FromDate field indicates when the row was inserted or last updated, whichever happened last.

How did we get here? Let’s build out a demo. But first, a shout-out to Damian Widera, who’s SQLBlog post titled “Temporal table behind the scenes – part 1” raised my interest in temporal tables.

Demo

You can begin by creating a database. I created TemporalTests using this following statement:

If Not Exists(Select name
          From sys.databases
          Where name = ‘TemporalTests’)
begin
       Create Database TemporalTests
end
go

Next, I create a table named TestTable in TemporalTests:

Use TemporalTests
go

If Not Exists(Select name
              From sys.tables
               Where name = ‘TestTable’)
begin
  Create Table dbo.TestTable
  (
       Id Int Identity(1,1)
        Constraint PK_TestTable Primary Key,
       Name VarChar(20),
       Value Int,
       FromDate DateTime2(1) Generated Always As Row Start Not NULL,
       ToDate DateTime2(1) Generated Always As Row End Not NULL,
       Period For System_Time (FromDate, ToDate)
  ) With (System_Versioning = ON)
end
go

I won’t go into all the details of the syntax, but I will point out three requirements:

1. The table needs a primary key. This serves to identify the row and, I believe, should not be updated. This makes the primary key function a lot like a surrogate key in a data warehouse dimension.
2. The table needs two DateTime2 fields configured similar to those shown (FromDate and ToDate) and a clause identifying these fields as the temporal value columns – Period For System_Time (FromDate, ToDate).
3. The table needs the key phrase With (System_Versioning = ON) at the end of the Create Table statement (when creating a temporal table).

When created, the table in Object Browser appears as shown:

Note you can supply a name for the temporal system table, but if you don’t supply a name SQL Server will create one for you.

Let’s load some data!

Insert Into dbo.TestTable
   (Name, Value)
Values
   (‘A’, 0)
, (‘B’, 1)
, (‘C’, 2)
, (‘D’, 3)
, (‘E’, 4)

If we query the table, we’ll see all the rows have the same FromDate and ToDate:

Now, if we update a few rows:

Update dbo.TestTable
Set Value = Value * 10
Where Value > 2

The table now has some rows – the last two – with different FromDate values:

We can now exercise a Type-II-like dimension query to examine the state of the rows at some time before the latest update:

Select *
From dbo.TestTable
For SYSTEM_TIME As Of ‘2015-09-18 22:13:00.0’

The results? The values as they were at the time specified in the For SYSTEM_TIME As Of ‘2015-09-18 22:13:00.0’ clause:

Cool? I thought so too.

I still have unanswered questions about temporal tables for Type-II dimension tracking. The largest question regards performance at scale. We’ll see once we get out of the SQL Server 2016 CTPs…

:{>