Practical .NET

Tracking History in SQL Server 2016 and Azure SQL Database

SQL Server 2016 and Azure SQL Database both give you tables that automatically keep track of changes to your data. Here's how to both create those tables and alter existing tables so they track the history of your data.

• By Peter Vogel
• 12/12/2017

On Monday morning, your boss comes to you and says, "We got some complaints from customers saying that they couldn't order any Wibble-Wobbles on Friday afternoon. The system said we were out of stock. Inventory says we had plenty in stock. Are they wrong or is it a bug in our application?"

After your boss leaves, you open a database window and do a quick search on the InventoryStock table and the ReservedOrders table. Subtracting the quantity in stock from the quantity reserved shows that you have about 3,500 Wibble-Wobbles available right now. However, a quick search on the Receiving table shows that about 5,000 Wibble-Wobbles came in late Friday afternoon … which is sort of ominous, given that there's only 3,500 available now. Another query on the Orders table shows that you have, in fact, sold about 1,500 Wibble-Wobbles since Friday. With that information, it's certainly possible that you were out of Wibble-Wobbles during part of Friday afternoon … but, depending on when that shipment came in, you may have had more than enough.

It's at this point that you think about the PurchaseOrder table. For that table, you had the foresight to create a parallel audit table that shows the state of each purchase order after any change. That audit table has been very helpful in resolving vendor complaints. If only, you think, you'd done that for the InventoryStock table. If you had, you might be able to roll through the history of the inventory changes to see what was actually in stock at a specific time on Friday. Without it, you're in for a couple of hours of investigation, reconciling those three tables. Of course, making sure that the purchase orders audit table was always properly updated has been a royal pain.

If you've ever faced the problem of determining what the state of your data was at a specific moment in time, you can appreciate the value of one of the new features in SQL Server 2016 and Azure SQL Database: temporal tables. A temporal table lets you retrieve the state of your data at any point in time. If you have several temporal tables that are related to each other, you can retrieve all related rows as they were for a specific moment in the past. Having that history also lets you "roll forward" through time to see how your data has changed, either to do trending analysis or to recover from some unfortunate event. To use this feature all you have to do is tell SQL Server which tables you want to track their own history.

Creating a Temporal Table

To create a new, temporal table, you'd use a SQL statement like this specifying two datetime columns and turning on versioning:

Create Table dbo.SalesOrder   
(    
  ID int Not Null Primary Key Clustered,   
  ...remaining columns...
  StartDate datetime2 GENERATED ALWAYS AS ROW START HIDDEN,  
  EndDate datetime2 GENERATED ALWAYS AS ROW END HIDDEN,  
  PERIOD FOR SYSTEM_TIME (StartDate, EndDate)  
)    
WITH (SYSTEM_VERSIONING = ON 
      (HISTORY_TABLE = dbo.SalesOrderHistorical));

SQL Server will take care of updating the datetime columns (which you can call whatever you want) and generating the history table. You can, if you wish, explicitly create your own history table, which must look just like your original table, but without the Period column and without SYSTEM_VERSIONING. Explicitly creating your own history table lets you define different indexes or storage options for your historical table.

While I've skipped creating my own history table, I have taken advantage of some options in my example. You don't have to use the HIDDEN option on your datetime2 columns but, if you don't, any Select * query on the table will return the columns. That's probably not useful, so I'd recommend hiding the columns. Besides, HIDDEN columns aren't really hidden -- if you do want to see the columns, all you have to do is include them by name in your Select statement.

You also don't have to specify a name for your history table, but anonymous tables make me uncomfortable so I've assigned the name SalesOrderHistorical to my table. In Azure SQL Database you can also set a HISTORY_RETENTION_PERIOD (and should be able to do it in SQL Server 2017) to control how far back in time your history will extend.

Converting Existing Table

If you're modifying an existing table to turn it into a temporal table, you need to not only add the two datetime2 columns, but you must also provide values for existing rows. The critical thing here is that the ROW END column must be set to the DateTime(MAX) value. You also need to specify those columns can't be NULL. Only with those changes in place can you then turn on versioning, so your Alter Table looks like this:

Alter Table dbo.Customers
  Add PERIOD FOR SYSTEM_TIME (StartTime, EndTime),   
    StartTime datetime2 GENERATED ALWAYS AS ROW START HIDDEN Not Null Default GetUtcDate(),   
    EndTime datetime2 GENERATED ALWAYS AS ROW END HIDDEN Not Null Default Convert(datetime2, 
    '9999-12-31 23:59:59.99999999'); 

Alter Table dbo.Customers
  Set  (SYSTEM_VERSIONING = ON 
       (HISTORY_TABLE = dbo.SalesOrderHistorical));

Once you've created a temporal table, it does have some restrictions. Some things are taken care of for you: If you alter the schema of a temporal table (by adding a new column, for example), SQL Server will take care of copying the change to your historical table. But, for some changes to your table (like adding a computed column), you'll need to set System_Versioning to OFF until you've finished making all your changes.

Given the existence of the history table and the time period columns, you could carefully construct SQL queries to give a time-based version of your data. Fortunately, there are a bunch of additions to SQL that will make retrieving data for a point in time much easier. I'll look at those features in an upcoming column.