SQL Server 2016 - Temporal tables

Another new feature available in SQL Server 2016.

Temporal database feature originated as part of ISO/ANSI SQL:2011 standard which got formally adopted in December 2011.

IBM DB2 version 10(released in the year 2012) is the first database to have an implementation in compliance of this feature under the name “Time travel queries.” Oracle introduced this functionality in Oracle 12c (released in 2014). Microsoft added this feature in SQL Server 2016 CTP2.0 with SYSTEM_VERSIONING.

A system-versioned temporal table is a new type of user table, designed to keep a full history of data changes and allow the easy point in time analysis. Temporal table brings built-in support for providing information about data stored in a table at any point in time. This type of table is referred to as a system-versioned temporal table because the period of validity for each row is system managed (i.e. in database engine).

The system-versioned temporal table is implemented as a pair of tables, current table, and history table, both of these tables contains two additional datetime2 columns(known as period columns), which are used to define a period of validity of each row.

Creating a System-Versioned Temporal table:

There are three ways in which we can create these tables, based on the way we are specifying history table

1. Temporal table with an anonymous history table: In this system generates history table with auto generated name

Example:

CREATE TABLE dbo.Employee

(

[EmployeeID] int NOT NULL PRIMARY KEY CLUSTERED

, [Name] nvarchar (100) NOT NULL

, [Position] varchar (100) NOT NULL

, [Department] varchar (100) NOT NULL

, [Address] nvarchar (1024) NOT NULL

, [AnnualSalary] decimal (10,2) NOT NULL

, [ValidFrom] datetime2 (2) GENERATED ALWAYS AS ROW START

, [ValidTo] datetime2 (2) GENERATED ALWAYS AS ROW END

, PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)

)

WITH (SYSTEM_VERSIONING = ON);

Note:

System versioned temporal table must have a primary key
They must have non-nullable PERIOD columns for start time and end time, and data types for these columns must be datetime2
The history table must always be schema-aligned with the current table, in terms of number of columns, column names, ordering and data types
Anonymous history table which will automatically be created under the same schema will have following format MSSQL_TemporalHistoryFor__[suffix]
History table will be set up as a row store table, and PAGE compression is applied if possible
History table will have default clustered index containing PERIOD columns

2. Temporal table with a default history table: In this system generates history table with user generated name

Example:

CREATE TABLE dbo.Employee

(

[EmployeeID] int NOT NULL PRIMARY KEY CLUSTERED

, [Name] nvarchar (100) NOT NULL

, [Position] varchar (100) NOT NULL

, [Department] varchar (100) NOT NULL

, [Address] nvarchar (1024) NOT NULL

, [AnnualSalary] decimal (10,2) NOT NULL

, [ValidFrom] datetime2 (2) GENERATED ALWAYS AS ROW START

, [ValidTo] datetime2 (2) GENERATED ALWAYS AS ROW END

, PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)

)

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

Note:

Schema name is mandatory for HISTORY_TABLE parameter and specified schema must be present
If the table specified by the HISTORY_TABLE parameter already exists, it will be validated against the newly created temporal table in terms of schema consistency and temporal data consistency. If you specify an invalid history table, the CREATE TABLE statement will fail.

3. Temporal table with a user-defined history table: In this user, can define specific storage options and additional indexes in history table

Example:

CREATE TABLE dbo.EmployeeHistory

(

[EmployeeID] int NOT NULL PRIMARY KEY CLUSTERED

, [Name] nvarchar (100) NOT NULL

, [Position] varchar (100) NOT NULL

, [Department] varchar (100) NOT NULL

, [Address] nvarchar (1024) NOT NULL

, [AnnualSalary] decimal (10,2) NOT NULL

, [ValidFrom] datetime2 (2) NOT NULL

, [ValidTo] datetime2 (2) NOT NULL

);

CREATE CLUSTERED COLUMNSTORE INDEX IX_EmployeeHistory

ON EmployeeHistory;

CREATE NONCLUSTERED INDEX IX_EmployeeHistory_EmployeeID_Period

ON EmployeeHistory(ValidFrom, ValidTo, EmployeeID);

CREATE TABLE dbo.Employee

(

[EmployeeID] int NOT NULL PRIMARY KEY CLUSTERED

, [Name] nvarchar (100) NOT NULL

, [Position] varchar (100) NOT NULL

, [Department] varchar (100) NOT NULL

, [Address] nvarchar (1024) NOT NULL

, [AnnualSalary] decimal (10,2) NOT NULL

, [ValidFrom] datetime2 (2) GENERATED ALWAYS AS ROW START

, [ValidTo] datetime2 (2) GENERATED ALWAYS AS ROW END

, PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)

)

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

Note:

In case history table is used to run analytic query then creating a clustered columnstore index will provide high compression and query performance
If history table is used as audit table (searching historical changes for a single row) then good choice is to create rowstore history table with a clustered index
We cannot define primary key, foreign keys, unique indexes, table constraints or triggers.

Enabling existing table as System-Versioned Temporal table:

ALTER TABLE dbo.Employee ADD

[ValidFrom] datetime2 (2) GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,

[ValidTo] datetime2 (2) GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,

, PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);

ALTER TABLE dbo.Employee

SET (SYSTEM_VERSIONING = ON);

Note:

Keyword HIDDEN is optional feature, that maximizes compatibility with existing application (as columns will be invisible unless we specify explicitly)
If table contains existing rows, then we need to add default constraints to new columns (where start time can be anytime in past and end time should be maximum datetime value, i.e. ‘9999-12-31 23:59:59’)
When an existing history table is specified when enabling SYSTEM_VERSIONING, a data consistency check will be performed across both the current and the history table. It can be skipped if you specify DATA_CONISTENCY_CHECK = OFF as an additional parameter.

Removing System-Versioning on existing Temporal table:

ALTER TABLE dbo.Employee

SET (SYSTEM_VERSIONING = OFF);

ALTER TABLE dbo.Employee

DROP PERIOD FOR SYSTEM_TIME;

ALTER TABLE dbo.Employee

DROP COLUMN ValidFrom, ValidTo;

Note:

If any existing default constraints are there on PERIOD columns, they need to be dropped first

Querying Temporal tables:

Example:

INSERT INTO dbo.Employee([EmployeeID], [Name], [Position], [Department], [Address], [AnnualSalary]) VALUES

('16590', ‘Ashutosh Sharma’, ‘Engineer’,’Analytics’,’Dallas Street, US’,1500000),

('171015', ‘Basavraj Kande’, ‘Engineer’,’Sales’,’ Lincoln street, US’,2500000),

('10881', ‘Nayan Kumar’, ‘Engineer’,’Analytics’,’Dallas Street, US’,1100000)

);

WAITFOR DELAY '00:00:02';

UPDATE dbo.Employee SET

Address = ‘Alambagh, Lucknow’,

AnnualSalary = 1900000

WHERE EmployeeId = 'E16590';

UPDATE dbo.Employee SET

Position = ‘Manager’,

Department = ‘Services’

WHERE EmployeeId = 'E10881';

WAITFOR DELAY '00:00:02';

DELETE FROM dbo.Employee

WHERE Address LIKE '%Lucknow%';

Now let’s check contents of Employee and Employee_History tables: