Monday, 6 June 2016

Altering an Existing Table to Support Temporal Data

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.

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:

Altering an Existing Table to Support Temporal Data

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:

Altering an Existing Table to Support Temporal Data

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.