Time Traveling with Temporal Tables on SQL Server 2016

One of the new features of SQL Server 2016 is the ability to time travel in your databases and visit a specific table at a specific point of time in history. You can also use this feature to audit changes or “undo” whole data warehouse updates.

This article will give you an introduction to temporal tables and teach you how to use them:

Introduction
Creating temporal tables
Enabling temporal support on existing tables
Removing temporal support from a table
Querying temporal tables
Altering temporal tables
Cleaning up history
Combining with In-Memory tables or Stretch databases

Introduction

Temporal is a database feature that was introduced in ANSI SQL 2011 and is now supported in SQL Server 2016. This feature is also available in Azure SQL Databases. Basically temporal tables are tables with automatic history tracking, supporting new query syntax for historic querying.

A temporal table actually consists of two ordinary tables: a main table (with current data) and a history table. For example, the screendump below shows the main Inventory table and its history table.

There are a few of requirements on temporal tables in SQL Server:

The main table must have a primary key.
They must have columns for start time and end time. These must be of type datetime2 (with any precision).
The history table must be schema-aligned with the main table, meaning that it has the same columns (names, data types, ordering).

The history tables can either be created manually or automatically be SQL Server.

Scenario

Let’s now imagine a scenario where we are running a company selling automobile parts. We have an inventory application that keeps track of the current quantity in stock and quantity reserved:

The quantities are written to the table Inventory. As the table contains no date or time, no history is kept by the current inventory application. The application just keeps overwriting the data in a row with new values.

Our management wants to get reports on statistics and historical trends on our inventory. However, we are not able to do any changes to our inventory application, so we can’t change the SQL statements that the application is executing. How can we create these statistics?

Creating temporal tables

Let’s first look into the simplest way of creating a temporal table:

CREATE TABLE [dbo].[Inventory](
                             [ProductId] nvarchar(20) PRIMARY KEY CLUSTERED,
                             [QuantityInStock] int NOT NULL,
                             [QuantityReserved] int NOT NULL,
                             [SysStartTime] datetime2(0) GENERATED ALWAYS AS ROW START NOT NULL,
                             [SysEndTime] datetime2(0) GENERATED ALWAYS AS ROW END NOT NULL,
                             PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime])
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[Inventory_History]));

The important details here, that makes it a temporal table is:

SysStartTime and SysEndTime
PERIOD FOR SYSTEM_TIME
SYSTEM_VERSIONING = ON

Actually SysStartTime and SysEndTime could be renamed to anything, but you would then need to rename them also in the PERIOD FOR SYSTEM_TIME. clause

This CREATE-statement will automatically create the history table Inventory_History for you (if you haven’t created it already). You could also omit the HISTORY_TABLE part. The name would then be MSSQL_TemporalHistoryFor_something.

Enabling Temporal Support on Existing Tables

In our example scenario with the inventory application, we’ll want to make the existing Inventory table into a temporal table. We’re assuming the table already exists and has the ProductId, QuantityInStock and QuantityReserved columns.

We’ll have to alter this table in two steps: first add the new columns and then enable system versioning:

ALTER TABLE [dbo].[Inventory] ADD
                             [SysStartTime] datetime2(0) GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
                             [SysEndTime] datetime2(0) GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
                             PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime])
ALTER TABLE [dbo].[Inventory]
                             SET (SYSTEM_VERSIONING = ON);

If the table contains existing rows, you will have to add default-constraints to the new columns. The end time must be the maximum datetime2-value. The start time could be any time in the past.

ALTER TABLE [dbo].[Inventory] ADD
                             [SysStartTime] datetime2(0) GENERATED ALWAYS AS ROW START HIDDEN NOT NULL CONSTRAINT DF_Inventory_SysStartTime DEFAULT '1900-01-01 00:00:00',
                             [SysEndTime] datetime2(0) GENERATED ALWAYS AS ROW END HIDDEN NOT NULL CONSTRAINT DF_Inventory_SysEndTime DEFAULT '9999-12-31 23:59:59',
                             PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime]) 
ALTER TABLE [dbo].[Inventory] 
                             SET (SYSTEM_VERSIONING = ON);

After adding the new time period columns, you can drop the default-constraints.

Note the keyword HIDDEN on the new columns. This is an optional feature that will maximize compatibility with the existing application. It means the new columns will be invisible unless you explicitly specify them in your query. For example, they are hidden in a SELECT * but they are visible in a SELECT ProductId, QuantityInStock, SysStartTime, SysEndTime.

HIDDEN can also be specified when creating tables.

Removing temporal support from a table

You can set SYSTEM_VERSIONING to OFF at any time to pause the history tracking. However, to fully convert a temporal table into an ordinary table you also probably want to drop the special time period columns. Here is how to do it:

ALTER TABLE [dbo].[Inventory] SET (SYSTEM_VERSIONING = OFF);
ALTER TABLE [dbo].[Inventory] DROP PERIOD FOR SYSTEM_TIME;
ALTER TABLE [dbo].[Inventory] DROP COLUMN [SysStartTime], [SysEndTime];

Note that if you have any default-constraints on the time period columns, you will have to drop the default-constraints before you can drop the columns.

Querying temporal tables

Let’s now continue our example scenario by adding some data to our Inventory table.

INSERT INTO dbo.Inventory(ProductId, QuantityInStock, QuantityReserved) VALUES
('OilFilter1', 59, 5),
('OilFilter2', 23, 2),
('FuelFilter1', 120, 0),
('FuelFilter2', 35, 5),
('FuelFilter3', 10, 10);
WAITFOR DELAY '00:00:02';
UPDATE dbo.Inventory SET
                             QuantityInStock = 54,
                             QuantityReserved = 0
WHERE ProductId = 'OilFilter1';
UPDATE dbo.Inventory SET
                             QuantityInStock = 21,
                             QuantityReserved = 0
WHERE ProductId = 'OilFilter2';
WAITFOR DELAY '00:00:02';
DELETE FROM dbo.Inventory
WHERE ProductId LIKE 'FuelFilter%';

Now let’s check the contents of the Inventory and the Inventory_History tables:

SELECT ProductId, QuantityInStock, QuantityReserved, SysStartTime, SysEndTime
FROM dbo.Inventory;
SELECT ProductId, QuantityInStock, QuantityReserved, SysStartTime, SysEndTime
FROM dbo.Inventory_History;

The result should be something like this (your dates/times might vary):

On the top is the current Inventory table, which contains only oil filters. On the bottom is the history table, which shows the previous values of the oil filter quantities and the deleted fuel filters.

Point in time queries

We could now query the contents of the Inventory table at any point in time:

SELECT ProductId, QuantityInStock, QuantityReserved, SysStartTime, SysEndTime
FROM dbo.Inventory
FOR SYSTEM_TIME AS OF '2016-09-30 06:47:57' ;

This will give the following results (since the oil filter quantities were updated, but the fuel filters were not deleted yet):

What would happen if we choose a query time exactly at the border between changes? Let’s pick 06:47:58.

SELECT ProductId, QuantityInStock, QuantityReserved, SysStartTime, SysEndTime
FROM dbo.Inventory
FOR SYSTEM_TIME AS OF '2016-09-30 06:47:58' ;

And the result is:

So SysStartTime <= {the point in time} < SysEndTime.

Interval queries

Interval queries are useful for auditing and tracking changes. There are three options:

FROM … TO
BETWEEN … AND
CONTAINED IN ()

The difference between these are subtle and can be a bit confusing. It is probably best to look at a few examples, but here is an illustration of how they handle border values:

FROM … TO and BETWEEN … AND are very similar. They include rows that were active during the time interval. The different is the border case for the upper bound:

SELECT ProductId, QuantityInStock, QuantityReserved, SysStartTime, SysEndTime
FROM dbo.Inventory
FOR SYSTEM_TIME FROM '2016-09-30 06:47:55' TO '2016-09-30 06:47:56';
SELECT ProductId, QuantityInStock, QuantityReserved, SysStartTime, SysEndTime
FROM dbo.Inventory
FOR SYSTEM_TIME BETWEEN '2016-09-30 06:47:55' AND '2016-09-30 06:47:56';

With the following results (note that BETWEEN is more generous with the upper bound):

Finally, the CONTAINED IN will look only at historical records and only include those that completely occurred within the time window:

SELECT ProductId, QuantityInStock, QuantityReserved, SysStartTime, SysEndTime
FROM dbo.Inventory
FOR SYSTEM_TIME CONTAINED IN ('2016-09-30 06:47:54', '2016-09-30 06:47:57');

Which gives us only the first updates:

The ALL option

Finally, we have the ALL option:

SELECT ProductId, QuantityInStock, QuantityReserved, SysStartTime, SysEndTime
FROM dbo.Inventory
FOR SYSTEM_TIME ALL;

This will give you a union of all current and all historical rows.

Altering temporal tables

Adding columns, altering columns and dropping columns is supported on temporal tables. When adding a column, it will need either to be nullable or have a default value. Your changes will automatically be applied both to the current table and the history table.

However, there are cases that present more difficulty. For instance, if you want to add a column with a different default value for the historical records than the current records. Fortunately, we can do that kind of changes within a transaction:

BEGIN TRANSACTION;
ALTER TABLE dbo.Inventory
SET (SYSTEM_VERSIONING = OFF);
GO

ALTER TABLE dbo.Inventory ADD

City nvarchar(20) NOT NULL CONSTRAINT DF_City1 DEFAULT('NewCityName');

ALTER TABLE dbo.Inventory_History ADD

City nvarchar(20) NOT NULL CONSTRAINT DF_City2 DEFAULT('OldCityName');

ALTER TABLE dbo.Inventory SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[Inventory_History], DATA_CONSISTENCY_CHECK = ON)); COMMIT;

Remember that you must make exactly the same changes both to the current and the history table.

Note also the option DATA_CONSISTENCY_CHECK. Enabling it will enable SQL Server to check your time intervals before activating system versioning. It will ensure you can’t get more than one version of a row at a specific time. If you have any overlapping time intervals it won’t let system versioning be enabled on your table.

Cleaning up history

You might want to limit the time history is being kept. The history table could otherwise grow enormously and become too expensive to maintain. The solution for this depends if you are running on Azure or on SQL Server 2016.

Azure SQL Database

Azure SQL Databases have built-in support for retention policies. First you will have to enable it on a database level. Then you can set the retention period per table.

-- Enable temporal history retention if it isn't already enabled
ALTER DATABASE CURRENT
SET TEMPORAL_HISTORY_RETENTION ON
GO
-- Set the length of the retention period for the Inventory table
ALTER TABLE [dbo].[Inventory]
SET (SYSTEM_VERSIONING = ON (HISTORY_RETENTION_PERIOD = 30 DAYS));

A background task will clean up aged rows in your tables.

SQL Server 2016

SQL Server 2016 on-premise databases don't have any built-in support for history retention. You will have to provide your own solution.

The history table is “read only”. You cannot issue any insert, update or delete statements against a temporal table history table. So, how can you clean up history?

Fortunately, you can use the same method as for altering temporal tables. Wrap your changes in a transaction where you first disable and then enable system versioning:

ALTER TABLE dbo.Inventory
SET (SYSTEM_VERSIONING = OFF);
GO
DELETE FROM dbo.Inventory_History
WHERE SysEndTime <= '2016-09-30 06:47:56';
ALTER TABLE dbo.Inventory
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[Inventory_History], DATA_CONSISTENCY_CHECK = ON));
COMMIT;

Another option (which I will not cover here) is to use partition switching to switch out history data.

Combining with In-Memory tables or Stretch databases

Now, this all might sound great, but how about tracking history on memory Optimized (“In-Memory”) tables?

Yes, it is supported! The syntax is very similar:

CREATE TABLE [dbo].[Inventory](
                             [ProductId] nvarchar(20) PRIMARY KEY NONCLUSTERED,
                             [QuantityInStock] int NOT NULL,
                             [QuantityReserved] int NOT NULL,
                             [SysStartTime] datetime2(0) GENERATED ALWAYS AS ROW START NOT NULL,
                             [SysEndTime] datetime2(0) GENERATED ALWAYS AS ROW END NOT NULL,
                             PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime])
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA, SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[Inventory_History]));

Notice however, that the history table can’t be an In-Memory table. The history table that gets automatically created won’t be memory optimized. The MEMORY_OPTIMIZED option is only allowed on the current table (you can’t use it on a pre-created history table).

What about Stretch databases?

As you probably know, Stretch databases is a new functionality in SQL Server 2016 to automatically migrate data from a local on-premise database to Azure. It can be an option to store very large volumes of data. It currently works only with “insert-only” data (you can’t do updates or deleted to stretched data).

Temporal tables support the usage of Stretch databases for history tables only. They can be stretch-enabled like any other tables. You don’t even need to disable SYSTEM_VERSIONING while stretch-enabling them. So, enabling stretch is very simple:

ALTER TABLE dbo.Inventory_History  
SET (REMOTE_DATA_ARCHIVE = ON (MIGRATION_STATE = OUTBOUND));

How about combining an In-Memory current table and a stretched history table? No problems. Just combine the two.

Time Traveling with Temporal Tables on SQL Server 2016

Introduction

Scenario

Creating temporal tables

Enabling Temporal Support on Existing Tables

Removing temporal support from a table

Querying temporal tables

Point in time queries

Interval queries

The ALL option

Altering temporal tables

Cleaning up history

Azure SQL Database

SQL Server 2016

Combining with In-Memory tables or Stretch databases

References

Rate

Share

Categories

Share

Rate

Time Traveling with Temporal Tables on SQL Server 2016

Introduction

Scenario

Creating temporal tables

Enabling Temporal Support on Existing Tables

Removing temporal support from a table

Querying temporal tables

Point in time queries

Interval queries

The ALL option

Altering temporal tables

Cleaning up history

Azure SQL Database

SQL Server 2016

Combining with In-Memory tables or Stretch databases

References

Rate

Share

Categories

Share

Rate

Related content

Converting a History Table into a System-Versioned Temporal Table

Introduction to SQL Server 2016 Temporal Tables

SQL Server 2016 DBCC CHECKDB with MAXDOP

Real-time Operational Analytics in SQL Server 2016 - Part 1

SQL Server 2016 Temporal Table Query Plan Behaviour