I’m happy to submit this post as part of the T-SQL Tuesday series, started by Adam Machanic. I’m particularly happy because my colleague and friend, Matt Gordon, is hosting this month. Thanks, Matt!The Shiny New SQL Server Toy I want to share is temporal tables. Temporal tables are new to SQL Server with the 2016 release, and are available in Azure SQL DB.
The Old Problem
Database designers and consumers often want to understand how records change in a database. They want to answer questions such as:
- When was a record created?
- When was a record updated?
- What was the value of FieldX before the update?
- How often are these records updated?
- Which records are updated most frequently?
- Are we deleting records from this table?
- How to I know if a record has been deleted?
Temporal tables are designed to answer these questions.
Prior to SQL Server 2016, there were a handful of tools and techniques available to answer these same questions, including Change Data Capture and Change capture. I’ve also seen custom solutions including custom triggers to record changes.
I would argue temporal tables are simpler to implement than any of these.
In the spirit of the T-SQL Tuesday theme, I feel I should describe a situation where I implemented this new feature to solve an old problem. The best example I can provide, however, is a case where I recommended them.
A client I was working with had an issue where values in one field in a table were being updated, and they were struggling to understand why/how this was happening. Of course, this is a strange problem and an odd occurrence. But I had an idea!
Open the Shiny New Toy
I suggested adding temporal table functionality because it would enable them to identify the exact time the updates occurred. Once the issue was identified and resolved, the temporal functionality could be removed.
Unfortunately, this client had not yet upgraded to SQL Server 2016, so this option was not available to them.
But, had it been available to them, enabling temporal functionality on an existing table is pretty simple. It requires adding two fields to the existing table to track effective start/end dates, and adding a separate history table to store changes.
Once the temporal functionality is enabled, changes to any record are recorded in the history table, and the Effective Start Date of the “current” record is updated to reflect the date and time of the most recent change.
Play With the Shiny New Toy
The following sample outlines how to enable temporal functionality on an existing table. The table in this example is available in the WideWorldImporters sample database.
-- 1) Add sys start/end fields, set PERIOD -- *Must provide default values* ALTER TABLE Warehouse.StockItemTransactions ADD SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START --HIDDEN CONSTRAINT DF_StockItemTransactions_SysStartTime DEFAULT '2017-01-01' , SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END --HIDDEN CONSTRAINT DF_StockItemTransactions_SysEndTime DEFAULT ('9999-12-31 23:59:59.9999999') , PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime) -- 2) Enable version tracking, add history table ALTER TABLE Warehouse.StockItemTransactions SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = Warehouse.StockItemTransactions_History) )
A few notes on the script above:
- Start/End date fields MUST use DATETIME2 data type
- Default constraints/values MUST be provided for Start/End date fields. Default for the End date MUST be the maximum value for the DATETIME2 data type
- Using “HIDDEN” keyword (commented out in the sample) prevents the new fields from appearing in a result set of a SELECT * query
- Step 2 creates a history table with the same fields and data types as the current table. Including the HISTORY_TABLE clause allows the designer to provide a specific table name to use. SQL Server will create a table with its own naming convention if a table name is not provided.
Once temporal functionality has been enabled, identifying the most recently updated records is a matter of running a simple query. Following is a simple example of how to identify recently updated or created records on a table with temporal functionality enabled.
SELECT TOP 100 * FROM Warehouse.StockItemTransactions ORDER BY SysStartTime DESC
This query is not elegant, but it is enough to help in this simple case.
I would be remiss to write about querying temporal tables without mentioning the new query functionality available with temporal tables. Historical data in temporal table can be queried with a new FOR SYSTEM_TIME clause. This clause allow us to get details about historical changes to records within certain timeframes. It is powerful functionality, and I encourage you to read this MSDN post for more info. This functionality was not required to help identify this particular problem, however.
After the Shine Wears Off
If you no longer want to use the temporal functionality, removing temporal functionality is simple. Follow the steps in the sample script below to remove temporal functionality.
/*Remove System Versioning completely*/ -- 1) Stop tracking changes ALTER TABLE Warehouse.StockItemTransactions SET (SYSTEM_VERSIONING = OFF) -- 2) Drop temporal constraints and columns ALTER TABLE Warehouse.StockItemTransactions DROP PERIOD FOR SYSTEM_TIME , CONSTRAINT DF_StockItemTransactions_SysStartTime , CONSTRAINT DF_StockItemTransactions_SysEndTime , COLUMN SysStartTime , COLUMN SysEndTime -- 3) Drop history table DROP TABLE IF EXISTS Warehouse.StockItemTransactions_History
In a webinar I presented on temporal tables, one of the most frequently asked questions I received was whether or not we can track the user ID associated with the data change. The short answer is that is no. There is no support for including the user ID of the record creator or modifier. I’d like to see this added later as an option.
People also expressed concern about overhead in using temporal tables. Is there overhead? Yes. But any of the solutions listed above also bring overhead.
I don’t have specific stats, but the overhead is generally low, and can be managed. The overhead on an INSERT is limited to including the Start/End period timestamp values. Overhead on UPDATEs and DELETEs includes writing a new record to the history table.
Have you used temporal tables before? Is the use case I described a compelling reason to try them? Please leave a comment to share successes or struggles you’ve had with temporal tables.