System-Versioned Temporal Tables

Every once in a while, I like to take a moment and learn something new about the latest SQL Server gizmos and gadgets. Today I came across system-versioned temporal tables and it peeked my interest, so I figured I’d investigate and share my finding with you.

How many of you have need to track data changes over time? I’ve needed this many times for things like auditing, investigating data changes, data fixes, and trend analysis of values over time. Having to do this is the past has been a very daunting task at times and sometimes nearly impossible. This is where system-versioned temporal tables will really help out. They have given us a new way to do just that with a new user table type. It keeps a full history of those data changes and gives us a way query it order to do point in time analysis. What I really like about this is that you can’t INSERT or UPDATE data into the datetime columns as they are automatically generated with the insert, which is great for auditing.

Syntax for Temporal Table Creation

Note we now have 2 required datetime2 fields that will be populated with our temporal history data for each row.

CREATE TABLE dbo.[Department](

       [DepartmentID] [smallint] NOT NULL PRIMARY KEY CLUSTERED,

       [Name] varchar(50) NOT NULL,

       [GroupName] varchar(50) NOT NULL,

       [BeginDate] datetime2 (2) GENERATED ALWAYS AS ROW START, 

       [EndDate] datetime2 (2) GENERATED ALWAYS AS ROW END,

       PERIOD FOR SYSTEM_TIME ([BeginDate], [EndDate]) 



) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory));


Lets insert some records using INSERTS and see how the data looks.
INSERT dbo.Department (DepartmentID, Name, GroupName)

      SELECT 4, 'John', 'DBA'  UNION

      SELECT 5, 'Joe', 'DEV'  UNION

      SELECT 6, 'Jessica', 'DEV'  UNION

      SELECT 7, 'Tim', 'DBA'  UNION

      SELECT 8, 'Sam', 'DBA'



UPDATE dbo.Department

SET GroupName = 'DB Operations'

WHERE DepartmentID % 2 = 0


This great illustration from Microsoft shows just how the history is tracked. For each INSERTED record, the SysStartTime will be populated in our BeginDate field. Each additional UPDATE/DELETE/MERGE our current record is copied to a history table and EndDate is updated with SysEndTime.

How do you query it?

It uses a new clause FOR SYSTEM_TIME that you can now query using it combined with AS OF, FROM TO, BETWEEN AND, CONTAINED IN, ALL

SELECT * FROM DEPARTMENT

    FOR SYSTEM_TIME   

    BETWEEN '2017-08-01' AND '2017-08-31'  

    ORDER BY BeginDate; 


Results

Note the current record has end date of  9999-12-31 23:59:59 because its the current state of the record. It hasn’t been modified yet so it gets the default future datetime.

If you are lucky enough to be using SQL Server 2017 I highly recommend playing around with this new gadget, it may be of significant use to you.

About Monica Rathbun

Monica Rathbun lives in Virginia, is a Microsoft MVP for Data Platform and Microsoft Certified Solutions Expert. She has nearly two decades of experience working with a wide variety of database platforms with a focus on SQL Server and the Microsoft Data Platform. She is a frequent speaker at IT industry conferences on topics including performance tuning and configuration management. She is the Leader of the Hampton Roads SQL Server User Group. She is passionate about SQL Server and the SQL Server community, doing anything she can to give back. Monica can always be found on Twitter (@sqlespresso) handing out helpful tips. You can find Monica blogging at sqlespresso.com 
Bookmark the permalink.

2 Comments

  1. Is there an easy way to amend an existing table to support this? There seems to be no obvious way of doing this.

    I could generate scripts to create the tables and populate them but I assume that there is a shortcut.

  2. Sorry, I have just one more query.

    Where there are parent/child relationships is there a solution, out of the box, for identifying the correct parent record to each child? I thought of using RowVersion columns but if these are defined as RowVersion in the history table, will SQL Server preserve the value of generate a new value in the history table?

    Any ideas on avoiding hand coded solutions would be more than welcome.

Comments are closed