If you are looking for a way to track the history of all the data changes to a table then SQL Server 2016 has you covered with the new temporal table support. With Temporal tables SQL Server is able to store all the older data records into in a history table while keeping the current records in the original table. In this article I will explore using the temporal table feature of SQL Server 2016 to create a history table for an existing SQL Server table.
What Is a Temporal Table?
A temporal table is just another SQL Server table that contains the old rows for a corresponding SQL Server table. It is basically just a history table of old rows. Every time an existing record is updated, the old row is placed in the associated temporal table automatically. A temporal table can also be called a history table. Using this new feature in SQL Server 2016 means you can now track changes to a table overtime without having to write any application logic. SQL Server will place the older rows in the temporal tables automatically.
SQL Server manages the movement of records between the original table and the temporal history table. The original table and the temporal table contain a set of period columns. The period columns, consist of a begin date and an end date column for the record. These two dates represent the period of time that a record is active, and are defined as datetime2 columns. When a record is update the SQL Server engine automatically updates the end date on the record being updated to the current UTC time, and then moves the existing record to the temporal table. When your application creates a new record in the normal or original table the period begin date is set to the UTC time based on a default value for the column, and then the end date is set to the default value for the end date column.
To better understand how this works let me show you an example.
Business Problem
Management has decided they would like to store the old versions of rows in their Product table. The Product table contains the price of each product they sell. By keeping older versions of records they will be able to track the price changes of products over time.
Current Product table
In order to demonstrate how to use a temporal table to track the changes to the Product table over time I will first need to create the Product table and populate it with some rows of data. To create and populate the Product table I will use the following code:
CREATE TABLE dbo.Product ( ID int Identity, ProductName varchar(50), ProductPrice decimal(20,2)); INSERT INTO dbo.Product VALUES ('Widget',33.49), ('Doo-Hickey',21.76), ('Thing-A-Ma-Jig',20.16);
In this code I created a table named dbo.Product and then populated it with three different products.
Setting up Temporal Data on Product Table
In order to start collecting historical information for my dbo.Product table I will need to alter the table so it will support temporal data. A SQL Server 2016 temporal table requires a table to have a primary key and a couple for date/time columns. The two date/time columns will be used to determine the period of time for when the record is valid. Therefore the first thing I need to do is alter my Product table to meet the temporal data table requirements. To do that I will run the following code:
ALTER TABLE dbo.Product ADD CONSTRAINT PK_ProductID PRIMARY KEY (ID), BeginDate datetime2 GENERATED ALWAYS AS ROW START NOT NULL DEFAULT SYSUTCDATETIME() , EndDate datetime2 GENERATED ALWAYS AS ROW END NOT NULL DEFAULT CAST('9999-12-31 23:59:59.9999999' AS datetime2), PERIOD FOR SYSTEM_TIME (BeginDate,EndDate);
Here you can see I’ve created a primary key name PK_ProductID and two dates named BeginDate and EndDate. The primary key is needed to be able to match records from the dbo.Product table and the temporal table. Lastly two datetime2 columns where added. These two fields identify a time period when a Product table record is valid. Note that I set the BeginDate column value to the current data/time, in UTC format and then EndDate to a date/time that is way into the future. The reason I used UTC is because support for the temporal table time period is based on UTC time and not the current time zone for my SQL Server instance.
The next step in setting up a temporal table is to identify a history table that goes along with my dbo.Product table. To do that I will run the following code:
ALTER TABLE dbo.Product SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ProductHistory)); GO
Here you can see I identified that the table name ddbo.ProductHistory would be my system versioned temporal table. After doing this I looked in Object Explore in SQL Server Management Studio (SSMS) to see how I can identify that the dbo.Product table now has a history table associated with it. Below is what I see when I browse my Temporal_Demo database:
SSMS Object Explorer Temporal_Demo
Note that my dbo.Product table now says it is a “System-Versioned” table. Additionally there is a new node under the dbo.Product table that identifies the history table dbo.ProductHistory. Note how this table is identified as a “History” table. I then expand the history table named dbo.ProductHistory. Below you can see the definition for the history table in SSMS:
SSMS Object Explorer History Table
Here you can see that the history table looks exactly like the dbo.Product table. At this point I can actually select data from this history table. But since I have yet to update, or delete an existing row there are no records in the history table.
Processing Update Statement Against a “System-Versioned” Table
In order to show you how a temporal table can hold the history records let me perform an UPDATE statement against dbo.Product table. To perform that update I will be using the following script:
UPDATE dbo.Product SET ProductPrice = 34.65 WHERE ProductName = 'Widget'; SELECT * FROM Product; SELECT GETDATE() CurrentTime, GETUTCDATE() UTCTime; SELECT * FROM ProductHistory;
When I run this code I get the following output:
ID ProductName ProductPrice ----------- -------------------------------------------------- -------------- 1 Widget 34.65 2 Doo-Hickey 21.76 3 Thing-A-Ma-Jig 20.16 BeginDate EndDate --------------------------- --------------------------- 2016-05-21 18:03:06.4258725 9999-12-31 23:59:59.9999999 2016-05-21 18:01:39.2698652 9999-12-31 23:59:59.9999999 2016-05-21 18:01:39.2698652 9999-12-31 23:59:59.9999999 CurrentTime UTCTime ----------------------- ----------------------- 2016-05-21 11:03:06.427 2016-05-21 18:03:06.427 ID ProductName ProductPrice ----------- -------------------------------------------------- -------------- 1 Widget 33.49 BeginDate EndDate --------------------------- --------------------------- 2016-05-21 18:01:39.2698652 2016-05-21 18:03:06.4258725
Note that the above output has been modified for readability.
If you look at my code above you can see that I first displayed all the records in my dbo.Product table. Here you can see the updated ProductPrice. I then displayed the local and UTC time on my local machine, followed by the data from the temporal data table dbo.ProductHistory on my local machine. The data displayed from my temporal table was the old “Widget” record prior to me updating it. As you can see SQL Server automatically set the EndDate on this record to the current UTC date. Remember this is not the local time on my SQL Server machine. This is because temporal data uses UTC dates when calculating the end date of a record. If you compare the UTCTime column that I displayed with the EndDate you can see they are close to the same time. Whereas the CurrentTime column is very different, and represents the local time on my machine.
Displaying Period Values in Local Time Format
Remember the begin date and end dates for my temporal tables are updated with the UTC time, and not the local time zone of the SQL Server machine. When looking at date ranges for temporal tables it might be nice to be able to display the period begin and end date in local time. To accomplish this you can run the following code:
SELECT ProductName, ProductPrice, DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), BeginDate) AS BeginDate_Local, BeginDate , DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), BeginDate) AS EndDate_Local, EndDate FROM dbo.ProductHistory;
When I run this code I get the following output:
ProductName ProductPrice -------------------------------------------- ------------- Widget 33.49 BeginDate_Local BeginDate --------------------------- --------------------------- 2016-05-21 11:01:39.2698652 2016-05-21 18:01:39.2698652 EndDate_Local EndDate --------------------------- --------------------------- 2016-05-21 11:01:39.2698652 2016-05-21 18:03:06.425872
Note this output has been reformatted for readability.
If you review the output above you will see that the BeginDate_Local and the EndDate_Local will represent the local time on my machines, whereas the BeginDate and EndDate columns contain the UTC time.
Limitations of Temporal Tables
Remember this is the first version of temporal table support. Like any new version feature there are a number of limitations for temporal tables. This is a partial list of some of those limitations:
- History tables need to be created on the same database as the table that is being versioned.
- You are not able to truncate the history table.
- You are not allowed to modify the rows of data in the history table with an INSERT, UPDATE, and DELETE statement.
- History tables cannot have a primary key, foreign key, or column constraints.
If you are interested in a complete list of limitation and considerations then you should consider reviewing this documentation from Microsoft: https://msdn.microsoft.com/en-US/library/mt604468.aspx.
Track Your Data Table Changes with Temporal Tables
Having a historical temporal data table that is automatically populated with data is a great feature. This keeps you from having to write the code for the period date range values. You are able to track the changes of individual rows over time by having temporal tables. Having history records in the temporal table allows you to historically determine what a record looked like for any given time period in the past. If you are looking for adding a history table in the future, consider whether or not a temporal table will provide you the functionality you need to track how a record changes over time.