Tracking table data changes using SQL Server temporal tables

Published on 04 July 2018 by Perry. Reading time: 7 minutes.

One of the requirements of a recent project I was working on, was maintaining history of database table data as part of an audit trail. My first thought about the technical solution was to use triggers, but after some research I learned about temporal tables.

Temporal tables have been introduced in SQL Server 2016 and are also available in Azure SQL databases. They maintain history of their data for a given retention period. The history is managed by the SQL Server database engine and only requires some configuration when creating the table. All table records will have a time context so point-in-time analysis can be applied.

 

Creating temporal table

In the following SQL script, a temporal table is created which contains data about persons and their cars.
CREATE TABLE dbo.Persons(
    ID BIGINT IDENTITY(1,1) NOT NULL,
    FirstName NVARCHAR(50) NOT NULL,
    Car NVARCHAR(50) NOT NULL,
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START,
    ValidTill DATETIME2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTill),
    CONSTRAINT PK_Persons PRIMARY KEY CLUSTERED (Id ASC)
)
WITH
(
	SYSTEM_VERSIONING = ON
	(
		HISTORY_TABLE = dbo.PersonsHistory,
		HISTORY_RETENTION_PERIOD = 1 YEARS
	)
 )
GO

 

The most of the SQL statement is pretty common, only the 2 parts of extra configuration are different:

  • At lines 5 through 7 the time context for every record is defined. ValidFrom contains the timestamp when the record became current and ValidTill contains the timestamp when the record became obsolete.
  • On line 14 we define the table where the historical data is stored with a retention period which is defined at line 15.

The history table will be created automatically, and the primary key defined at line 8 will be used to maintain the relationship of the data in the current table and the history table.

 

2 SQL Versioning Table Detail

 

After running this SQL script, in SQL Server Management Studio you can see the current table and history table as child of the that table.
The history table has been created with the same columns as the current table. The only difference is that the primary key of the ID column has been removed. This is done because a record can have multiple changes and this would lead to more then one record in the history table with the same ID value.

 

Inserting data

In order to create a record in the current table, only the columns that are not automatically generated by the database engine (ID, ValidFrom & ValidTill) and which are not nullable (FirstName & Car) require data.

 

INSERT INTO dbo.Persons(FirstName,Car) VALUES ('David', 'Bentley')
INSERT INTO dbo.Persons(FirstName,Car) VALUES ('Kevin', 'Ferrari')
INSERT INTO dbo.Persons(FirstName,Car) VALUES ('John', 'Porsche')
GO

   

After the inserts, the current table shows the records with in ValidFrom the timestamp of the creation of the record and in ValidTill the timestamp of the record becoming obsolete. Because this is the current table, the ValidTill will here always contain the maximum date value.

 

4 SQL Result

 

The history table is still empty because there have been no changes for these records.

 

Updating data

Now that the table contains some data, it can be updated using the following SQL script.

UPDATE dbo.Persons SET Car = 'Bugatti' WHERE FirstName = 'David'
GO

  

The following steps are executed by the database engine as a result of running this SQL script:

  1. The record of David is copied from the current table to the history table without changes.
  2. The ValidTill of this record of David in the history table is changed into current datetime.

    6 SQL Result 2

  3. The record of David in the current table is updated with ValidFrom as current datetime.
    7 SQL Result 3

 

After doing several updates on the record of David (with few seconds in between updates), a more extensive history of the record is created.

 

8 SQL Result 4

 

Deleting data

The next step is to delete the record of David from the current table.

DELETE FROM dbo.Persons WHERE FirstName = 'David'
GO

  

In this case, the following steps are executed by the database engine:

  1. The record of David is copied from the current table to the history table without changes.
  2. The ValidTill of this record of David in the history table is changed into current datetime.

    10 SQL Result 5

  3. The record of David in the current table is deleted.
    11 SQL Result 6

 

Querying data

Temporal table data can be retrieved using regular SQL query statements and the new FOR SYSTEM_TIME clause. This clause can be used for point-in-time analysis in combination with one of the following filters:

  • ALL; returns all records from the current and history table.
  • AS OF <datetime>; returns all records that were current at the specified datetime.
  • FROM <start_datetime> TO <end_datetime>; returns all records that were current within the specified datetime range.
  • BETWEEN <start_datetime> AND <end_datetime>; returns all records that were current within the specified datetime range and on the end datetime.
  • CONTAINED IN (<start_datetime>, <end_datetime>); returns all records that were current within the specified datetime range and on the start and end datetime.

The following queries returns all David’s records, even though his records are no longer available in the current table:

SELECT * FROM dbo.Persons
    FOR SYSTEM_TIME
    ALL
WHERE FirstName = 'David'

  

13 SQL Result 7

 

To retrieve David’s records within and on a datetime range, the following queries is used:

SELECT * FROM dbo.Persons
    FOR SYSTEM_TIME
    BETWEEN '2018-06-14 09:27:00.0000000' AND '2018-06-14 09:30:05.1918383'
WHERE FirstName = 'David'

  

15 SQL Result 8

 

Adding columns

Columns which are nullable can be easily added to the current table.

ALTER TABLE dbo.Persons
ADD
	LastName NVARCHAR(50) NULL
GO

  

The column will automatically be added to the history table.

 

17 SQL Altered Table

 

Columns which are not nullable require some coding which is shown below:

ALTER TABLE dbo.Persons
ADD
	LastName NVARCHAR(50) NULL
GO

ALTER TABLE dbo.Persons
SET (
	SYSTEM_VERSIONING = OFF
)
GO

UPDATE dbo.Persons SET LastName = '-unknown-'
UPDATE dbo.PersonsHistory SET LastName = '-unknown-'
GO

ALTER TABLE dbo.Persons
SET (
	SYSTEM_VERSIONING = ON
	(
		HISTORY_TABLE = dbo.PersonsHistory,
		HISTORY_RETENTION_PERIOD = 1 YEARS
	)
)
GO

ALTER TABLE dbo.Persons
ALTER COLUMN
	LastName NVARCHAR(50) NOT NULL
GO

  

First, the column is added as nullable. Because this is done when versioning is enabled the column is automatically added to the history table. Next, versioning is turned off and the column is filled with default values. Finally, versioning can be re-enabled and the column can be changed to not nullable.

Please note that the ValidFrom timestamp in the current table will be reset to the current datetime because of re-enabling versioning.

 

Change existing table into temporal table

Existing tables can be changed into temporal tables by first adding the time context columns. These columns need to have default values because they are required and the table will (probably) already have existing data. After the columns have been added, versioning can be enabled in the same way as when creating a new table.

ALTER TABLE dbo.Products
ADD
	ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START DEFAULT GETUTCDATE(),
	ValidTill DATETIME2 GENERATED ALWAYS AS ROW END DEFAULT CAST('12/31/9999 23:59:59.9999999' AS DATETIME2),
	PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTill)
GO

ALTER TABLE dbo.Products
SET (
	SYSTEM_VERSIONING = ON
	(
		HISTORY_TABLE = dbo.ProductsHistory,
		HISTORY_RETENTION_PERIOD = 5 YEARS
	)
)
GO

 

Resources

All the queries used in this article can be found here.

If you want to read more about temporal tables, here are some other pages you might like.

Tags: Azure

Perry

Published by Perry on 04 July 2018

In short; developer in his mid-forties, living in Alphen aan den Rijn (The Netherlands), married with Carola and has two awesome kids named Martijn and Jeroen. At young age I was fascinated by the possibilities of my first computer; a Commodore 16 with no less than 16 KB RAM. After getting my degree in electrical engineering I first started programming PLC’s for process automation. After a couple of years I gradually entered the world of Microsoft software development starting with Visual Basic 3.0 and Visual InterDev 6.0 as development environment. In recent years I have focussed on Azure technologies. I like to experiment with new technologies and solve complex issues with simple solutions.

 

Comments? Share them below

Learn more about ETTU

And the way we work