Historical data with MS SQL System-Versioned (Temporal) Tables

Before the existence of system-versioned temporal tables, tracking changes in a database table forced us to implement some custom solutions based on triggers, stored procedures etc.

System-versioned temporal tables offer us the possibility to keep a full history of data changes and allowing us easy point in time analysis. Using this solution, we can easily time travel through the data and see what the exact state of the data in a point of time was.

A temporal table contains a reference to another table with a mirrored schema. The system uses this table to automatically store the previous version of the row each time a row in the temporal table are updated or deleted. This additional table is referred to as the history table, while the main table that stores current (actual) row versions is referred to as the current table. The system uses a PERIOD FOR SYSTEM_TIME to be able to determinate the state of the record in a point of time.

Prerequisites and limitations

To be able to create or use a system-versioned temporal table in SQL there are some prerequisites and limitations. On the first look you can think that the list is big, but all the items are here for a simple reason to protect data consistency.

Main table:

  • Primary key – the table must have a primary key defined
  • Start - the start column must be set to system time
  • End - the end column must be set to max date value
  • Period - It must have one PERIOD FOR SYSTEM_TIME defined with two DATETIME2 columns
  • Data – The table cannot be FILETABLE and cannot contain FILESTREAM data type
  • Update - INSERT, UPDATE, and MERGE statements cannot reference and modify period
  • columns
  • Triggers - INSTEAD OF triggers are not allowed
  • Truncate - TRUNCATE TABLE is not supported
  • Blob data types – The system-versioned tables support blob data types (e.g. (n)varchar(max), image etc.) but because of performance issues (storage cost) is better to take care when using these data types
  • Cascade - ON DELETE CASCADE and ON UPDATE CASCADE are not permitted on the current table*

* Cascade options are supported from MS SQL Server 2017 CTP 2.0

History table:

  • Constraints – The history table cannot have constraints defined (e.g. primary keys, foreign keys, check, table, or column constraints).
  • Data – The table cannot be FILETABLE and cannot contain FILESTREAM data type
  • Modifications – Modifying data in the history table is not allowed
  • Alter – You can’t alter the history table
  • Drop – You can’t drop the history table
  • Schema – The schema of the history table must be same as the main table (data types, columns and even column ordinal position)
  • Triggers – No INSTEAD OF and AFTER triggers are allowed
  • Compression – By default the history table is PAGE compressed
  • Partitions - If current table is partitioned, the history table is created on default file group because partitioning configuration is not replicated automatically from the current table to the history table
  • Database - History table must be created in the same database as the current table
  • Objects / properties - The following objects/properties are not replicated from the current to the history table when the history table is created
    • Period definition
    • Identity definition
    • Indexes
    • Statistics
    • Check constraints
    • Triggers
    • Partitioning configuration
    • Permissions
    • Row-level security predicates

Creating temporal tables

To be able to create a system-versioned table we can use the new WITH (SYSTEM_VERSIONING = ON) clause added to the well-known CREATE TABLE statement.

For testing purposes, I will use a brand-new created test database named "TestSystemVersioned", but you can do it on any database you like.

Example:

CREATE TABLE dbo.TestTable (
    IDTest INT NOT NULL CONSTRAINT PK_TestTable PRIMARY KEY
    , DrinkName NVARCHAR(256) NOT NULL
    , IsAlcoholic BIT NOT NULL
) WITH (SYSTEM_VERSIONING = ON);

Since my table does not meet the minimum requirements for creating system-versioned table an exception was raised by the engine.

Cannot set SYSTEM_VERSIONING to ON when SYSTEM_TIME period is not defined.

The main problem is that our table does not contain one PERIOD FOR SYSTEM_TIME defined with two DATETIME2 columns. And like you could notice before this is a prerequisite for creating system-versioned tables.

To create a temporal table this requirement must be met:

  • Start column - the start column of type DATETIME2 so that the system knows since when the row is valid
  • End column - the end column of type DATETIME2 so that the system knows until the row is valid
  • Period - period for system time by using previously defined start and end columns
  • Set the SYSTEM_VERSIONING table attribute to ON

We can create a system-versioned table in different ways. So, let’s see some of them.

System-versioned table with default name

Now, we will create a new system-versioned table with all the requirements for a creation but will leave to the database engine to decide the name of the history table.

Example:

CREATE TABLE dbo.Drinks (
    IDDrink INT NOT NULL CONSTRAINT PK_Drinks PRIMARY KEY
    , DrinkName NVARCHAR(256) NOT NULL
    , IsAlcoholic BIT NOT NULL
    , ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL
    , ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL
    , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
) WITH (SYSTEM_VERSIONING = ON);

The table is created. We will expand the tables in SQL Server Management Studio and the table itself to be able to see what happened.

As you can see the new table “dbo.Drinks” have a small clock icon indicating that the system-versioning is set up.

Under the table name, you can see its history table. Note that columns in both tables are identical (column names, data types, etc.). The history table does not have constraints (primary key).

Since we didn’t specify the name of the history table the system picked the name for us. The name for the history table is:

MSSQL_TemporalHistoryFor_<number>_[suffix]

  • Number - current_temporal_table_object_id
  • suffix - optional and it will be added only if the first part of the table name is not unique.

As told before, the history table is compressed by default with Page compression.

System-versioned table with custom name (user defined)

When creating a system-versioned table we can specify the name of the history table we want to use.

Example:

CREATE TABLE dbo.PriceList (
    IDPriceList INT NOT NULL CONSTRAINT PK_PriceList PRIMARY KEY
    , DrinkName NVARCHAR(256) NOT NULL
    , IsAlcoholic BIT NOT NULL
    , Price MONEY
    , ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL
    , ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL
    , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
) WITH (SYSTEM_VERSIONING = ON(HISTORY_TABLE = dbo.PriceListHistory));

The result is that the history table has the exact name like required “dbo.PriceListHistory”.

Alter an existing table to a system-versioned table

We can alter an existing table to become a system-versioned table. To be able to do this, first we must meet the requirements and at the end we can turn on the system versioning. Suppose we have a standard, non-versioned table “dbo.NonHistoryTable” on witch we want start tracking changes.

Example:

CREATE TABLE dbo.NonHistoryTable (
    IDPriceList INT NOT NULL CONSTRAINT PK_NonHistoryTable PRIMARY KEY
    , DrinkName NVARCHAR(256) NOT NULL
    , IsAlcoholic BIT NOT NULL
    , Price MONEY    
)

First step is to add the required columns (start, end) and the period of time.

Example:

ALTER TABLE dbo.NonHistoryTable ADD 
	ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL CONSTRAINT DF_Validfrom DEFAULT '1991-01-01 00:00:00.0000000'
	, ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL CONSTRAINT DF_ValidTo DEFAULT '9999-12-31 23:59:59.9999999'
	, PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);

At the end we can just turn on the system-versioning.

Example:

ALTER TABLE dbo.NonHistoryTable SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.NonHistoryTabletHistory));

Our non-system-versioning table “dbo.NonHistoryTable” is now set up as a system-versioning table storing it’s history in a “dbo.NonHistoryTabletHistory” table.

Altering temporal tables

If a table is already set up to be a system-versioned table, we can normally alter it adding new columns. Let’s use our previously created “dbo.PriceList” table and add new columns in it.

Example:

ALTER TABLE dbo.PriceList ADD
	Quantity SMALLINT NOT NULL CONSTRAINT DF_Category DEFAULT 0
	, Remarks NVARCHAR(256) NULL;

The new columns are added in both main and history table.

The new columns are added to both main and history tables. So far, so good. Let’s try to add a computed column.

Example:

ALTER TABLE dbo.PriceList ADD
	Total  AS (Quantity * Price);

And we have an exception:

"System-versioned table schema modification failed because adding computed column while system-versioning is ON is not supported."

As seen we can’t completely add any new column we would like when system-versioning is on. There are some limitations in adding computed columns, sparse columns, identity columns.

You can expect to get some of the following errors in that cases:

Cannot alter table ''table_name' because the table either contains sparse columns or a column set column which are incompatible with compression.

System-versioned table schema modification failed because history table 'table_name' has IDENTITY column specification. Consider dropping all IDENTITY column specifications and trying again.

Some of cases (e.g. computed column) can be solved by setting SYSTEM_VERSIONING attribute to false to convert the tables to non-temporal tables, perform the changes, and then return to a temporal table.

Take care about that adding LOB or BLOB columns (e.g. NVARCHAR(MAX)) will cause a mass update in both the current and the history table. In case of large tables, this can take a long time and during this time, both tables are locked out.

Dropping temporal tables

Let’s drop our system-versioned table using a standard DROP TABLE statement.

Example:

DROP TABLE dbo.PriceList;

The exception message is clear:

"Drop table operation failed on table 'TestSystemVersioned.dbo.PriceList' because it is not a supported operation on system-versioned temporal tables."

It is not possible to drop a system-versioned temporal table. Both current and history tables are protected from drop operation until the SYSTEM_VERSIONING attribute of the current table is set to ON.

To be able to drop a system-versioned table you must first set the SYSTEM_VERSIONING attribute to OFF. In that moment, both tables automatically become non-temporal tables and are fully independent of each other. They become "normal" tables.

Example:

ALTER TABLE dbo.PriceList SET (SYSTEM_VERSIONING = OFF);

You can update them totally in depended. You can change them, drop the period or even the From and To columns. As you like. Changing the values in the main table does not inset new records in the history table as before. Off course you can drop them now.

Querying system-versioned tables

For purpose of this part of the post I created a system-versioned table named “[dbo].[PriceList]” and inserted/manipulated some data inserts. You can find the preparation script in the attached SQL script at the bottom of this article. Here you can see the data contained in both, the current and the history table:

SELECT * FROM [dbo].[PriceList] 
SELECT * FROM [dbo].[PriceListHistory] ORDER BY ValidFrom;

Notice that the "Umbrella" record exists only in the history table. The reason for that is that I deleted this record entirely using the DELTE statement. I did not use the UPDATE statement with this record. You can exactly see when the record was inserted and when deleted.

Based on our needs there are several methods to query data from system-versioned tables. Let’s see what our possibilities are.

Get all data (current and history) - FOR SYSTEM_TIME ALL

If we would like to see all data (current and history) for a certain drink, we could easily join the current and the history table.

Example:

SELECT * FROM [dbo].[PriceList] WHERE DrinkName = 'Gin tonic'
UNION ALL
SELECT * FROM [dbo].[PriceListHistory] WHERE DrinkName = 'Gin tonic' ORDER BY ValidFrom;

The other possibility is to use the FOR SYSTEM_TIME ALL clause and just select the data from the main table. The SQL engine will do the join by himself and we do not need to know the exact name of the history table that is more convenient.

Example:

SELECT * FROM [dbo].[PriceList] FOR SYSTEM_TIME ALL WHERE DrinkName = 'Gin tonic' ORDER BY ValidFrom;

The result set of these two queries is totally the same:

Get current data (most recent data state)

If we want to retrieve the current state of the data, we must just make a normal select on the main/current table.

SELECT * FROM [dbo].[PriceList] ORDER BY IDPriceList;

Let’s look at the execution plan of this query. Notice that the SQL engine did query only the current table. There is no evidence of any select in the history table.

Get data in certain point of time - FOR SYSTEM_TIME AS OF

If we want to retrieve data in certain point of time we must use the FOR SYSTEM_TIME AS OF clause in our SELECT statement. Let’s try to get the data in a certain point of time.

Example:

SELECT * FROM [dbo].[PriceList] FOR SYSTEM_TIME AS OF '2018-01-03 08:00:00'  ORDER BY IDPriceList;

All the data returned were valid at the specified date and time in our query. Let’s look at the execution plan of this query.

Because we used the FOR SYSTEM_TIME AS OF clause the SQL engine to be able to perform this task was forced to look at the current and in the history table. Let’s try to retrieve the current state of the data but using the FOR SYSTEM_TIME AS OF clause.

Example:

DECLARE @RightNow AS DATETIME2 = SYSUTCDATETIME();
SELECT * FROM [dbo].[PriceList] FOR SYSTEM_TIME AS OF @RightNow  ORDER BY IDPriceList;

The result set is the same when non-using the FOR SYSTEM_TIME AS OF clause but let’s look at the execution plan of the query.

The query with no usage of the FOR SYSTEM_TIME AS OF clause did look only in the current table. When using the FOR SYSTEM_TIME AS OF clause, even when retrieving the current data based on SYSUTCDATETIME() forced the SQL engine to look in the current and in the history table. Long story short, don’t use the FOR SYSTEM_TIME AS OF clause when retrieving the current data state.

Other data retrieving options

If you would like to get the data that was valid in a certain period (like BETWEEN) you can use two options in a system-versioned table:

  • BETWEEN start AND end - will show all rows that were visible at any point between two specified points in time. It works inclusively, a row visible exactly at start or exactly at end will be shown too. BETWEEN start AND end clause returns all the row that satisfied the following criteria:

StartTime <= end_date_time AND EndTime > start_date_time

  • FROM – TO – will show all rows that were visible at any point between two specified points in time, including start, but excluding end. FROM - TO clause returns the data from both tables system versioned and history. It will return all rows that satisfies the following criteria:

StartTime < end_date_time AND EndTime > start_date_time

  • CONTAINED IN - returns all the row that satisfied the following criteria:

StartTime >= start_date_time AND EndTime <= end_date_time

The first two sub-clauses return row versions that overlap with a specified period (i.e. those that started before given period and ended after it), while CONTAINED IN returns only those that existed within specified period boundaries.

Last words

Delete rows from a history table

If you try to delete records from the history table while system-versioning is turned on, you will get an exception.

"Cannot delete rows from a temporal history table 'table_name'."

Example:

DELETE FROM [dbo].[PriceListHistory];

Truncate table

You can't truncate the main and the history table while system-versioning is turned on. You will get an exception.

"Truncate failed on table 'table_name' because it is not a supported operation on system-versioned tables."

Example:

TRUNCATE TABLE [dbo].[PriceListHistory];
TRUNCATE TABLE [dbo].[PriceList];

Change the schema of the history table

You can't alter the schema of the history table while system-versioning is turned on. You will get an exception. For example:

"Add column operation failed on table 'table_name' because it is not a supported operation on system-versioned temporal tables."

Example:

ALTER TABLE [dbo].[PriceListHistory] ADD NewColumn BIT NULL;

Add triggers to a history table

You can't add a trigger to the history table while system-versioning is turned on. You will get an exception.

"Cannot create a trigger on a system-versioned temporal table 'table_name'."

Example:

CREATE TRIGGER dbo.trg_TestMe
   ON  [dbo].[PriceListHistory]
   AFTER INSERT,DELETE,UPDATE
AS 
BEGIN
	SET NOCOUNT ON;
END;

Index system-versioned tables

Yes, you can! You can index the temporal table and the main table as per your requirement.

Hidden columns

Usually the ValidFrom and ValidTo (period columns) are used only to support the temporality of data and have no business logic. You can use the HIDDEN clause to hide the new PERIOD columns to avoid impact on existing application logic.

Example:

CREATE TABLE dbo.Hidden(
    ID INT NOT NULL PRIMARY KEY,
    DrinkName NVARCHAR(256) NOT NULL,
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.HiddenHistory));
INSERT INTO dbo.Hidden(ID, DrinkName) VALUES(1, 'Gin tonic');
SELECT * FROM dbo.Hidden;

As you can see the ValidFrom and ValidTo are not visible in the result of the select statement, but you can force them to show if you explicitly define them in the select statement.

Example:

SELECT *, ValidFrom, ValidTo FROM dbo.Hidden;

*You can download the complete SQL Script with all examples from the post here: SQL Script.sql (8.31 kb)