How to use SQL Server temporal tables? T-SQL

Introduction to SQL Server Temporal Tables to manage data versioning automatically.

Temporal tables in SQL Server, also known as system-versioned tables, are a feature introduced in MS SQL Server 2016. They provide built-in support for storing historical data related to changes made to data in a table. This feature is beneficial for auditing or business data analytics purposes, where it’s crucial to understand the state of data at any given point in time. And also for all companies subject to regular regulatory reporting with legal transparency obligations.

Table of Contents

Basic MS SQL temporal table syntax

Let’s start by creating a basic temporal table in Transact-SQL. So the T-SQL code below creates a table named Employee with system-versioning. The columns SysStartTime and SysEndTime are used to record the period of each row. To make a small recap, 3 additional elements are necessary to create and manage a temporal SQL Server table:

SysStartTime system column to mark the start of the period, only supports the DATETIME2 data type.
SysEndTime to mark the end, only supports the DATETIME2 data type.
A system history table, here it is named dbo.EmployeeHistory, with same structure, except the keys.

The create table statement is specific

The table definition is special, however, and that’s the goal, inserting data into a system-versioned table is similar to inserting data into any other SQL Server table. Indeed, system-versioned tables, i.e. temporal table are designed to automatically manage data versioning, particularly useful to mange dimensions, and slowly changing dimensions, called SCD.

CREATE TABLE [Expert-Only].dbo.Employee
(
   EmployeeId INT PRIMARY KEY,
   EmployeeName NVARCHAR(100),
   Position NVARCHAR(100),
   Salary DECIMAL(18, 2),
   SysStartTime  DATETIME2 GENERATED ALWAYS AS ROW START,
   SysEndTime    DATETIME2 GENERATED ALWAYS AS ROW END,
   PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON (
   HISTORY_TABLE = dbo.EmployeeHistory)
);

Insert data into the temporal table

So this basic T-SQL INSERT statement will insert a new row into the Employee table, and it will update the history table associated when the data change.

INSERT INTO [Expert-Only].dbo.Employee (EmployeeId, EmployeeName, Position, Salary)
VALUES (1, 'John Doe', 'Developer', 80000);

*Create a SQL Server system-versioned temporal table and insert data*

System-versioned and history table’s structure

As you can see in the screenshot below, the Employee table is now system-versioned and the EmployeeHistory table is located just behind it in the SSMS explorer. Both tables has the same structure, except of course the primary keys, because the history table needs to stores multiple version of lines with the same IDs.

*History table under the system-versioned table*

Update SQL data in temporal tables

When you update a record in a system-versioned table, SQL Server automatically inserts a copy of the old row into the history table. So you can analyse any past change to better understand your data. As a result, after executing this SQL command, the EmployeeHistory table will have a record with the old salary. To illustrate the concept, the following query triggers an insert with the old value of the line, directly inside the history table.

UPDATE [Expert-Only].dbo.Employee
   SET Salary = 85000
   WHERE EmployeeId = 1;

Delete data from system-versioned tables

Deleting records in a system-versioned table will not remove the data entirely. The deleted records will be moved to the history table. After running the T-SQL command below, the EmployeeHistory table will have the deleted record.

DELETE FROM Employee
   WHERE EmployeeId = 1;

So of course you need to be aware of how much often the data changes, and also about the data volumes to avoid ending with huge databases and backups. You can see it like the same principle of the Windows Recycle Bin, indeed you can have multiple versions of the same file, deleted but still in the bin, till you empty it.

A solution to this is to put in place a purge system that periodically delete old records from the history table.

T-SQL query syntax to check data from temporal tables

SQL Server provides the FOR SYSTEM_TIME clause to query data from a system-versioned table. This SQL command will return the data as it was at the specified point in time. You can use more details of course to be more specific. You can go till

SELECT *
  FROM Employee
  FOR SYSTEM_TIME AS OF '2023-10-10';

SELECT *
  FROM Employee
  FOR SYSTEM_TIME AS OF '2023-10-10 15:30:45.1234567';

Test a temporal data to see how it works

To do a more in depth test, lets recap all actions performed, and add one, in this order to see if the table properly keeps track of our changes. Between each steps, wait a few minutes for better visibility in the history table.

Insert the first line with a salary of 80’000 dollars (made in second section of the tutorial)
Update the salary to 85’000 dollars
Delete the Employee from the table.
Insert again the same employee but this time with a 90’000 $ salary and a Developer Senior role.
Delete again the Employee from the table. Not visible on the Screenshot but available in the T-SQL code below.

UPDATE [Expert-Only].dbo.Employee
   SET Salary = 85000
   WHERE EmployeeId = 1;

-- *** Wait a few minutes *** -- 
DELETE FROM [Expert-Only].dbo.Employee
   WHERE EmployeeId = 1;

-- *** Wait a few minutes *** -- 
INSERT INTO [Expert-Only].dbo.Employee
   (EmployeeId, EmployeeName, Position, Salary)
VALUES (1, 'John Doe', 'Developer Senior', 90000);

-- *** Wait a few minutes *** -- 
DELETE FROM [Expert-Only].dbo.Employee
   WHERE EmployeeId = 1;

SELECT * FROM dbo.EmployeeHistory;

-- First insert
SELECT * FROM [Expert-Only].dbo.Employee
  FOR SYSTEM_TIME AS OF '2023-10-10 10:56:00';

-- Update to 85000 $
SELECT * FROM [Expert-Only].dbo.Employee
  FOR SYSTEM_TIME AS OF '2023-10-10 11:03:00';

-- Second insert as Developper Senior
SELECT * FROM [Expert-Only].dbo.Employee
  FOR SYSTEM_TIME AS OF '2023-10-10 11:06:00';

Then check the result using SSMS to select the content of the history table and also the Employee table art very specific points in time. As a result, the Employee table is now empty. the three archived lines from the EmployeeHistory table are available using a SELECT statement on the Employee table itself in 3 different points in time.

*Data from the history table is visible in the temporal versioned table.*

Modify an existing system-versioned tables

Of course, as data models evolves in the database over time, you need to maintain the history table when the versioned table changes. To modify the schema of a system-versioned table, for example to add a new column to the Employee table, use these 3 steps, in this order:

First turn off the system versioning
Make the changes to the temporal table
Then turn the system versioning back on.

ALTER TABLE Employee SET (SYSTEM_VERSIONING = OFF);

ALTER TABLE Employee ADD Email NVARCHAR(100);

ALTER TABLE Employee SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));

Delete a system-versioned table

As it is a specific type of table, to delete it, you also need in this case to stop the versioning manually before dropping the table. To drop a system-versioned table without error, use the generate script option from SSMS to generate the complete statement. As you can see below, it performs 3 operations:

Stop the versioning
Drop the system-versioned table
Drop the temporal table that contains the history

USE [Expert-Only]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Employee]') AND type in (N'U'))
ALTER TABLE [dbo].[Employee] SET ( SYSTEM_VERSIONING = OFF  )
GO

DROP TABLE IF EXISTS [dbo].[Employee]
GO

DROP TABLE IF EXISTS [dbo].[EmployeeHistory]
GO

If you try to drop the table without stopping the versioning, the system returns this error:

Msg 13552, Level 16, State 1, Line 1
Drop table operation failed on table ‘Expert-Only.dbo.Employee’ because it is not a supported operation on system-versioned temporal tables.

Conclusion on setting-up SQL Server temporal tables

In conclusion, SQL Server Temporal Tables provide a built-in solution for keeping track of data changes over time. They can be valuable in situations where auditing or data analysis is required. For enterprise real cases scenarios, when you had to do all the archiving steps with custom code or SCD, it was complex and heavy to maintain. Using this system-versioned table features makes it much more straightforward to implement.

By understanding how to create, insert, update, delete, and query data from these tables, one can leverage this powerful feature in MS SQL Server. To go further and discover more uses cases for temporal tables, a typical real life example is when loading a datawarehouse dimension table using an package SSIS.