Monthly Archives: March 2017

Temporal Tables – Part 1 – Introduction

SQL Server 2016 has a cool new feature called Temporal Tables. This is how Microsoft describes them:

SQL Server 2016 introduces support for system-versioned temporal tables as a database feature that brings built-in support for providing information about data stored in the table at any point in time rather than only the data that is correct at the current moment in time.

In other words, the temporal table keeps track of your changes. If you query the temporal table normally you get the most current version of the data. You can query the temporal table with an AS OF indicator (or another type of indicator) and get what the data was as of the date you specify. I think this is way cool.

Here is a quick example:

CREATE TABLE dbo.Company
(
    CompanyID INT IDENTITY(1,1) NOT NULL,
    CompanyName NVARCHAR(100) NOT NULL,
    StateIncorporatedIn char(2) NOT NULL,
    FullAddress NVARCHAR(500) NOT NULL,
    Notes NVARCHAR(MAX) 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),
    CONSTRAINT PK_CompanyTest Primary Key CLUSTERED
(
    CompanyID
)
) WITH (SYSTEM_VERSIONING = ON);

In this table definition you can see four items that make this a temporal table. The table definition contains StartTime and EndTime columns with a GENERATED clauses to indicate these are system generated times. It contains a PERIOD clause and it contains a SYSTEM_VERSIONING clause. If you do all of these things you get a temporal table that holds a history of the changes made.

In addition to dbo.Company, behind the scenes this statement is actually creating a secondary table. The second table can be discovered by looking the history_table_id field of sys.objects catalog view to get the object id of the history table.

SELECT
    history_table_id
FROM sys.tables
WHERE
    object_id = object_id('dbo.Company')

or

SELECT
    ht.name
FROM sys.tables t
INNER JOIN sys.tables ht
    ON t.history_table_id = ht.object_id
WHERE
    t.object_id = object_id('dbo.Company');

This shows a yucky system generated name of: MSSQL_TemporalHistoryFor_885578193

We can query the primary table for the current values and we can query the yucky table for the historical data. However we can query the primary table with a clause indicating that we want the values at a certain time and SQL Server will use both tables and return the right data to you.

Here I will insert a record and then update it momentarily later.

INSERT INTO dbo.Company
(
    CompanyName,
    StateIncorporatedIn,
    FullAddress,
    Notes
)
VALUES
(
    'Dale Company',
    'NC',
    '100 Dale Way',
    'Great Company'
);

UPDATE c SET
    Notes = 'Better Company'
FROM dbo.Company c
WHERE
    CompanyName = 'Dale Company';

Looking at the two tables we see the current record and the previous record.

SELECT
    CompanyID,
    CompanyName,
    Notes,
    StartTime,
    EndTime
FROM dbo.Company;

SELECT
    CompanyID,
    CompanyName,
    Notes,
    StartTime,
    EndTime
FROM dbo.MSSQL_TemporalHistoryFor_885578193;

Theses table have a start and end date column that indicates the time period that each record was current. An end time of 9999-12-31 23:59:59.9999999 indicates this is the current record.

We can get the record at a point in time with AS OF in the FROM clause.

SELECT
    CompanyID,
    CompanyName,
    Notes,
    StartTime,
    EndTime
FROM dbo.Company FOR SYSTEM_TIME AS OF '2017-03-07 00:08:44'

To get the historical record we need to specify an AS OF value that lies within the start date and end date for that record.

SELECT
    CompanyID,
    CompanyName,
    Notes,
    StartTime,
    EndTime
FROM dbo.Company FOR SYSTEM_TIME AS OF '2017-03-07 00:08:25'

You can also get multiple rows in different time periods with other clauses such as FROM, BETWEEN and CONTAINS. These have different level of inclusiveness. In this example the SELECT clause returns both of my old and new test records.

SELECT
    CompanyID,
    CompanyName,
    Notes,
    StartTime,
    EndTime
FROM dbo.Company FOR SYSTEM_TIME FROM '2017-02-24 02:45:43' TO '2017-02-24 02:50:00'

Very cool.

If we don’t want to use the ugly name for the historical table we can choose our own. I’ll drop the table from the first example. To drop a temporal table and a history table you have to turn of system versioning first.

ALTER TABLE dbo.Company SET (SYSTEM_VERSIONING = OFF)
DROP TABLE dbo.Company
DROP TABLE dbo.MSSQL_TemporalHistoryFor_885578193

We can add the HISTORY_TABLE clause to SYSTEM_VERSIONING clause to indicate the name of the table to use for history. I am also creating this table with the clustered index on a secondary filegroup and the table on a tertiary filegroup because I want to see where the history table gets created.

CREATE TABLE dbo.Company
(
    CompanyID INT IDENTITY(1,1) NOT NULL,
    CompanyName NVARCHAR(100) NOT NULL,
    StateIncorporatedIn char(2) NOT NULL,
    FullAddress NVARCHAR(500) NOT NULL,
    Notes NVARCHAR(MAX) 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),
    CONSTRAINT PK_CompanyTest Primary Key CLUSTERED
(
    CompanyID
) ON [SecondaryFG]
) ON [TertiaryFG]
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.CompanyHistory))

We can see from sys.tables that our tables are associated.

SELECT
    ht.name
FROM sys.tables t
INNER JOIN sys.tables ht
    on t.history_table_id = ht.object_id
WHERE
    t.object_id = object_id('dbo.Company')

What filegroup was the history table created on?

SELECT
    t.name,
    fg.name
FROM sys.tables t
INNER JOIN sys.indexes i
    ON t.object_id = i.object_id
INNER JOIN sys.filegroups fg
    on i.data_space_id = fg.data_space_id
WHERE
    t.name IN ('Company','CompanyHistory')
    AND i.index_id IN (1,0)

My history table got created on the Tertiary filegroup which is the filegroup I specified for the table not the clustered index. I don’t see anything in the CREATE TABLE documentation that allows me to choose the filegroup of the history table in the SYSTEM_VERSION clause. It is possible I missed it but to create the history table on a different filegroup than the table requires a different solution.

To do this I will create the history table independently of the the SYSTEM_VERSIONING clause and use the ALTER TABLE clause to associate the primary table to the history table.

Before I drop the tables to start again I want to look at how SQL Server handled the primary key name (since these are unique) and the identity column in the history table.

SELECT
    c.name,
    object_name(c.parent_object_id),
    c.type_desc
FROM sys.key_constraints c
INNER JOIN sys.tables t
    ON c.parent_object_id = t.object_id
WHERE
    t.name IN ('Company','CompanyHistory')

SELECT
    t.name,
    c.is_identity
FROM sys.tables t
INNER JOIN sys.columns c
    ON t.object_id = c.object_id
WHERE
t.name IN ('Company','CompanyHistory')
and c.name IN ('CompanyID')

There is no primary key constraint or identity property on the history table. I’ll leave those out of my history table definition. I’ll also strip out the GENERATED and PERIOD clauses out of the columns definitions because I discovered in testing that SQL Server won’t let me use a table with those defined as a history table.

ALTER TABLE dbo.Company SET (SYSTEM_VERSIONING = OFF)
DROP TABLE dbo.Company
DROP TABLE dbo.CompanyHistory

Create the primary table on the secondary filegroup.

CREATE TABLE dbo.Company
(
    CompanyID INT IDENTITY(1,1) NOT NULL,
    CompanyName NVARCHAR(100) NOT NULL,
    StateIncorporatedIn char(2) NOT NULL,
    FullAddress NVARCHAR(500) NOT NULL,
    Notes NVARCHAR(MAX) 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),
    CONSTRAINT PK_CompanyTest Primary Key CLUSTERED
(
    CompanyID
) on [SecondaryFG]
) on [SecondaryFG]

Create the history table the the tertiary filegroup.

CREATE TABLE dbo.CompanyHistory
(
    CompanyID INT NOT NULL,
    CompanyName NVARCHAR(100) NOT NULL,
    StateIncorporatedIn char(2) NOT NULL,
    FullAddress NVARCHAR(500) NOT NULL,
    Notes NVARCHAR(MAX) NULL,
    StartTime datetime2 NOT NULL,
    EndTime datetime2 NOT NULL
) on [TertiaryFG]

I’ll use the ALTER TABLE to start system versioning for the company table.

ALTER TABLE dbo.Company SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.CompanyHistory))

Now verify with the metadata:

SELECT
    t.name,
    fg.name,
    t.temporal_type,
    t.temporal_type_desc
FROM sys.tables t
INNER JOIN sys.indexes i
    ON t.object_id = i.object_id
INNER JOIN sys.filegroups fg
    ON i.data_space_id = fg.data_space_id
WHERE
    t.name IN ('Company','CompanyHistory')
    AND i.index_id IN (1,0)

Since I can create the history table on a different file group this means that I should be able to it on a partitione. I’ll test that in my next post.