SQL Server 2016 - System-Versioned Temporal Tables

Although the word "Versioning" was not used, we have been maintaining versions of records, or more precisely history of records using different techniques. SQL Server offers various ways of handling or maintaining history records, or changes that have been done to our records, using features like CDC, CT, Optimistic Isolation Levels. Most of these features do not support actual "versioning" of records but these features can be used for handling different scenarios.

SQL Server 2016 introduces a new feature called System-Versioned Temporal Tables that provides the entire history of our records related to the changes done. This feature records versions of the records based on  update and delete operations with the validity period of the version, allowing us to see not only the current record, state of the record during any given period, or allowing us to do point-in-time analysis. This feature is based on ANSI SQL 2011 standard but SQL Server current 2016 release does not support all the features that describes with it.

Let's write a code and see how it works. The following code creates a Database called Sales and a Table called Customer in Sales. Code creates Customer as a Temporal Table that requires few additional elements with the CREATE TABLE statement. It requires two datetime2 columns for maintaining the validity period. In addition to that, it needs SYSTEM_VERSIONING = ON and optionally a name for the history table. If the name is not mentioned, system will create one for us.

CREATE DATABASE Sales;
GO

USE Sales;
GO

CREATE TABLE dbo.Customer
(
 CustomerId int identity(1,1) Primary Key
 , FirstName varchar(100) null
 , LastName varchar(100) not null
 , CreditLimit money not null
 , ValidFrom datetime2(0) GENERATED ALWAYS AS ROW START -- datetime2(any precistion)
 , ValidTo datetime2(0) GENERATED ALWAYS AS ROW END -- datetime2(any precistion)
 , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.CustomerHistory));

Let's insert three records and query both tables;

-- Inserting records
INSERT INTO dbo.Customer
 (FirstName, LastName, CreditLimit)
VALUES
 ('Dinesh', 'Priyankara', 10000)
 , ('Jack', 'Wilson', 15000)
 , ('John', 'Knight', 3500);

-- Checking records
SELECT * FROM dbo.Customer;
SELECT * FROM dbo.CustomerHistory;

As you see, history table does not show any records and last two datetime2 columns have been automatically filled. Now let's make some changes. Note that records have been inserted on 14/04/2016 and will be updating records on 16th, 17th and 19th.

-- Modifying a record - 2016-04-14 01:56:23
UPDATE dbo.Customer
 SET CreditLimit = 12000
WHERE CustomerId = 1;

-- Deleting a record - 2016-04-17 01:57:17
DELETE dbo.Customer
WHERE CustomerId = 3;

-- Modifying the same record - 2016-04-19 01:57:26
UPDATE dbo.Customer
 SET CreditLimit = 20000
WHERE CustomerId = 1;

-- Checking records
SELECT * FROM dbo.Customer;
SELECT * FROM dbo.CustomerHistory;

As you see, three records are in the history table; 2 records for the Customer Id 1 for two modifications made and 1 record for the Customer Id 2 for the deletion. This table exactly says how recorded are changed and when they have been changed, not only that it allows us to see the state of the record based on the validity period.

Let's see how we can retrieve records. There are multiple ways for querying records. We simply query the table without considering the history or we can go through the history using new clause given; FOR SYSTEM_TIME. Note the different between BETWEEN and FROM.

-- Retrieving the current record
SELECT *
FROM dbo.Customer
WHERE CustomerId = 1;

-- Retrieving for a date
-- Better include time too
SELECT *
FROM dbo.Customer
FOR SYSTEM_TIME
 AS OF '2016-04-17 00:00:00'
WHERE CustomerId = 1;

SELECT *
FROM dbo.Customer
FOR SYSTEM_TIME
 BETWEEN '2016-04-16 00:00:00' AND '2016-04-19 00:38:43'
WHERE CustomerId = 1
ORDER BY ValidFrom;

SELECT *
FROM dbo.Customer
FOR SYSTEM_TIME
 FROM '2016-04-16 00:00:00' TO '2016-04-19 00:38:43'
WHERE CustomerId = 1
ORDER BY ValidFrom;

You can read more on this at msdn.

Here is the cleaning code if required;

ALTER TABLE dbo.Customer SET ( SYSTEM_VERSIONING = OFF );
DROP TABLE dbo.Customer;
DROP TABLE [dbo].[CustomerHistory];
GO
USE master;
GO
DROP DATABASE Sales;