By: Dinesh Asanka | Updated: 2018-11-16 | Comments | Related: > Temporal Tables
Problem
For many applications, it's a common requirement to get details about data modifications that have occurred in a SQL Server table. Tables can have multiple updates and deletes for different reasons, and there may be a need to keep the different versions of records. Prior to SQL Server 2016 you would have to use triggers to keep track of data changes in tables, but now we can use the built-in temporal table feature that was first released in SQL Server 2016.
Solution
With SQL Server 2016 and later, there is a database feature called system version tables also know as temporal tables where we can store data changes automatically for a table.
Business Requirements
There are multiple reasons why we need to have versioned records in a table.
Auditing
A very common case for this functionality is to have a data audit. In case of auditing needs, you need to understand when and what has changed for a specific record. Although, who made the changes is also an important factor, but this is not possible with temporal tables.
Data Recovery (DDR)
How often you have seen data in your important tables get deleted and you are left with nothing. Of course, you have restore mechanisms like point in time recovery, but you know how difficult it is to recover data with this technique. With the temporal tables, data recovery is much easier.
Slowly Changing Dimensions (SCD)
A historical aspect of data is needed in a data warehouse where Type 2 Slowly Changing Dimensions are used. A major problem with the SCDs are extracting the changes of source data and this can be solved with temporal tables.
Troubleshooting
In some cases, you might need to understand how data was changed for troubleshooting purposes.
Other Options for SQL Server Versioned Records
Triggers have been the most common and most popular way of achieving versioned records. However, triggers add complexities to your code as well as maintainability issues.
Another approach developers use is to write to an audit table directly within the stored procedures, but again you will end up with same maintainability issues of triggers.
SQL Server Temporal Table Example
Temporal Tables were introduced with SQL Server 2016. This feature is also available with Azure SQL database, but not with Azure SQL Server Data warehouse.
Following is the script to create a temporal table.
CREATE TABLE Customers (
CustomerID INT IDENTITY PRIMARY KEY CLUSTERED,
CustomerName VARCHAR(100) NOT NULL,
City VARCHAR(50) NOT NULL,
Status VARCHAR(1) NULL,
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.CustomersHistory));
GO
In the above script, there are few important things to highlight. Apart from table creation, you can see there is additional syntax WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.CustomersHistory)). This means that CustomerHistory will be the history table for the Customer table. Schema should be mentioned in the script as the default schema will not work here. If the table versioning table does not already exist, this table will be created automatically.
Also, there needs to be two additional columns in the table. Though in the above example they are named as SysStartTime and SysEndTime, you can name them as you wish, but the data types have to be DATETIME2.
After the table is created, you can see the table structure in SSMS as shown below.
First of all, you will see a new icon for the table that indicates it is a versioned table. Under this table, you will see the history table which has the same structure of the base table.
Let's insert a few records into this table using following script.
INSERT INTO Customers (CustomerName, City, Status) VALUES ('John','New York', 'A')
INSERT INTO Customers (CustomerName, City, Status) VALUES ('Sam','Denver', 'A')
INSERT INTO Customers (CustomerName, City, Status) VALUES ('Robs','New York', 'D')
When querying the Customer table, the following records are retrieved.
From the above figure, it can be seen that SysStartTime and SysEndTime are updated automatically. SysEndTime is set to 9999-12-31 and SysStartTime is the record update date and time. The history table does not have any data after the first inserts as there are no versions of data yet.
Let's update the table with the following.
UPDATE Customers SET City = 'New York' WHERE CustomerID = 2 UPDATE Customers SET Status = 'D' WHERE CustomerID = 2
After the above updates, the CustomerHistory table is updated as follows.
If you carefully analyze the data, you will see the history table has the historical values not the current values. The advantage of this feature is that you don’t need to query the history table, but query the base table to get the details.
SELECT * FROM [Customers] FOR SYSTEM_TIME BETWEEN '2018-01-01' AND '2019-01-01' WHERE CustomerID = 2 ORDER BY SysStartTime
When the above query is executed, it will combine the base table and the history table and return the data for all of the versions.
You can verify this by analyzing the query plan as it shows both the base and the history tables are used for the query.
There are other ways of getting this data using following queries.
The following query returns ALL the data without limiting to any dates.
SELECT * FROM [Customers] FOR SYSTEM_TIME ALL WHERE CustomerID = 2 ORDER BY SysStartTime
If you need details about a specific time, you can execute the following query.
SELECT * FROM [Customers] FOR SYSTEM_TIME AS OF '2018-10-20 13:30' WHERE CustomerID = 2
Similar to the BETWEEN syntax, there is FROM and TO syntax as well. The only difference is that the FROM and TO syntax boundary values are not included where they are included when using BETWEEN.
SQL Server Data Recovery Using Temporal Tables
Let's assume all the records in the customer table were deleted. Even after you delete from the base table, you will still have data in history table, where you can recover your data without much hassle.
However, you are not allowed to TRUNCATE the base table when system versioning is enabled and you will get the following error message.
Limitations of SQL Server Temporal Tables
You need to have a primary key for the base table to enable system versioning. Both base table and the temporal table should be in same database. You cannot use constraints, primary key, foreign keys or column constraints for the temporal tables however you can create indexes including columnstore indexes on the temporal tables. FILETABLE or FILESTREAM are not supported for temporal tables, but it supports blob data types such as nvarchar(max), varchar(max), varbinary(max), ntext, text, and image, but you need to consider the storage. Obviously, you can’t modify data in the temporal table.
Things to Note with Temporal Tables
After enabling system versioning for a table, you can add columns to the base table and it will automatically add the columns to the temporal table. Also, you can drop the columns from the base table where the temporal table will be updated automatically.
When system versioning is enabled, you can’t drop either the base table or the temporal table. If you try you get the following error.
To drop the table, first you need to disable system versioning as shown below.
ALTER TABLE Customers SET (SYSTEM_VERSIONING = OFF) GO DROP TABLE CustomersDROP TABLE CustomersHistory
Also, you can enable system versioning for table that is already created as follows.
CREATE TABLE Customers1 (
CustomerID INT IDENTITY PRIMARY KEY CLUSTERED,
CustomerName VARCHAR(100) NOT NULL,
City VARCHAR(50) NOT NULL,
Status VARCHAR(1) NULL
)
ALTER TABLE dbo.Customers1
ADD SysStartTime datetime2 GENERATED ALWAYS AS ROW START,
SysEndTime datetime2 GENERATED ALWAYS AS ROW END ,
PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
GO
ALTER TABLE dbo.Customers1
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Customers1History))
GO
Next Steps
Check out the following resources:
About the author
Dinesh Asanka is a 10 time Data Platform MVP and frequent speaker at local and international conferences with more than 12 years of database experience.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2018-11-16
