Starting from zero – Temporal tables, part 1

The ability to query and make use of historical data is very important for the end users. We are obliged to keep the historical data for some period of time. It depends of the kind of a business or a project. Sometimes it could be months but it could be several years as well. The shortest history I have ever kept in the data warehouse project was two weeks. The database was just too big as I loaded over 300 millions of rows on a daily basis. And it was many years ago.

So far we have had to prepare such queries but it requires an additional programming work. One might use triggers, stored procedures or other features like Change Tracking or Change Data Capture to achieve the goal.
The triggers and stored procedures are present in all editions of the SQL Server. The seems to be easy to create and use. But you might get into trouble if the base table changes its structure. That means that the maintenance of them might not be that easy as it seems to be. In general changing the base table structure leads the developer to change the trigger or the stored procedure body and of course to alter the table that contains the historical data. This operation could be risky and of course, the altered object needs to be tested.

What has changed in the SQL Server 2016? There is a solution and it’s called temporal tables. That is exactly what we need. The definition taken from the MSDN page is as follow:

“A system-versioned temporal table is a type of user table designed to keep a full history of data changes and allow easy point in time analysis. This type of temporal table is referred to as a system-versioned temporal table because the period of validity for each row is managed by the system (i.e. database engine).”

Let’s try to do some demos. In this article, I am going to prepare a sample database and two temporal tables.

Here is the database definition – I know, it is just a regular database

USE master

GO

IF EXISTS(Select * FROM sys.databases where name=N'TemporalDemo')

BEGIN alter database TemporalDemo

set single_user with rollback immediate

drop database TemporalDemo

END

GO

CREATE DATABASE TemporalDemo

GO

In the second step I will prepare two tables. The first table will be constructed that way that the corresponding historical data table is created by the SQL Server.

CREATE TABLE dbo.Products

(

ProductId INT IDENTITY NOT NULL PRIMARY KEY,

Name NVARCHAR(100) NOT NULL,

Number INT NOT NULL,

Price MONEY NOT NULL,

SYSSTART DATETIME2(1) GENERATED ALWAYS AS ROW START NOT NULL,

SYSEND DATETIME2(1) GENERATED ALWAYS AS ROW END NOT NULL,

PERIOD FOR SYSTEM_TIME (SYSSTART, SYSEND)

)

WITH ( SYSTEM_VERSIONING = ON);

GO

The table contains columns to store the regular data but you can easily identify two new columns:

• Sysstart will be used to determine the start time of the period
• Sysend will be used to determine the end time of the period
• The period is defined in the PERIOD FOR_SYSTEM_TIME phrase

Both of the columns must be not nullable and be datetime2 type of any precision.

The magic started as I declared the table with SYSTEM_VERSIONING=ON. That creates a temporal table which can be understand as a pair of tables: the base table that holds the current state of the records and the historical table that stores the changed data. For every data change we will see a corresponding entry in the historical table. Tthe historical table name was chosen by the SQL Server and it looks like this:

MSSQL_TemporalHistory_For_ObjerctId

The historical table has been created in the same schema like the base table. What is also interesting is that the historical table has PAGE compression turned on which seems to be very good choice in most cases.

Let’s now take a look at the historical table structure. It is exactly the same as it is in the base table. However the clustered index is different:

CREATE CLUSTERED INDEX [ix_MSSQL_TemporalHistoryFor_565577053] ON [dbo].[MSSQL_TemporalHistoryFor_565577053]

(

[ProductId] ASC,

[SYSSTART] ASC,

[SYSEND] ASC

) ON [PRIMARY]

GO

I will dig into that topic in the future posts but it is now worth to mention that:

• The base table must have a primary key defined
• The historical table will not have the primary key
• The historical table will have the clustered index defined on all columns that are part of the key on the base table plus the two system columns described earlier
• Clustered columnstore index can exist in the base table
• Partitioning can be applied on the base table but the schema will not be propagated on the historical table. You have to do it manually – which seems to be a good option
• The historical table cannot have any constraints: primary key, foreign keys and constraints are forbidden
• Truncate table will not work on historical table if the SYSTEM_VERSIONING = ON
• DML operations are forbidden on the columns that describe the period of time
• The historical table must be placed in the same database

Let’s now check if we could control the process of historical table creation. It is of course possible, and we can control the historical table schema and name as well as we can required additional logical checks that could be done during this operation. I will describe that process in separate blog post, too. Here is the second table I will be using in my demos

CREATE TABLE dbo.ProductsV2

(

ProductId INT IDENTITY NOT NULL PRIMARY KEY,

Name NVARCHAR(100) NOT NULL,

Number INT NOT NULL,

Price MONEY NOT NULL,

SYSSTART DATETIME2(1) GENERATED ALWAYS AS ROW START NOT NULL,

SYSEND DATETIME2(1) GENERATED ALWAYS AS ROW END NOT NULL,

PERIOD FOR SYSTEM_TIME (SYSSTART, SYSEND)

)

WITH ( SYSTEM_VERSIONING = ON ( HISTORY_TABLE = dbo.ProductsV2Hist ) );

GO

After we created the second table we now see that the name of the table is controlled by us but the structure of the table is exactly the same like it was in the previous example. The only change which maybe is not seen at the first glance is that the historical table is not compressed by default.

This is all for today. I will create some more tables in the second part of this series. You will see the different scenario in that case. Now we start from a very simple point, where you start with an empty database and just add temporal tables to the database

Stay tuned

Cheers,

Damian