Temporal tables with SQL Server
My favorite feature introduced with SQL Server 2016 is temporal tables.
System versioned temporal tables the keep a full history of a table’s data and managed by the SQL Server engine.
Use Cases
- Auditing: answer the “who did that” question
- Point in time view: “well the system said we had three items available as of yesterday”
- Slowly changing dimensions
- Data recovery: Rolling back from those “oops I didn't mean to delete that” moments
How a temporal table functions
A temporal table consists of two tables: a current table and a history table. These tables must have a period start and period end column with the data type datetime2.
For row INSERTS, the start period is assigned when the transaction started. Note that on insert there is no corresponding row in the history table. That’s because there is no history yet.
For row UPDATES, a new row is added in the history table, recording row's state at the time of the update. The end period is set the time of the update. In the current table, the start period is updated to the start of the update transaction.
For DELETES, a row is entered in the history table like updates, only there is no longer a row in the current table.
A little more in deletes
Until temporal tables, a common table design practive has been to add an Active or IsActive column. Instead of deleting a row when the data is no longer wanted or needed, queries returning data for the UI would include sotmthin like a IsActive = 1 in the WHERE clause. Deletes had this air of danger. What if we need that later? The answer is the AS OF expression to retrieve a row that existed at of a given time. This is something of a brain shift, but one I have embraced.
Summary
There more design considerations to cover when choosing to temporal tables, data retention and space limitations. But for auditing and managing data from a historical perspective, temporal tables are a compelling feature.
Posted 15 April 2018