Keeping track of history: SQL Compare and Temporal Tables
Asha Patel introduces SQL Compare 13's support for deploying changes to temporal tables,
SQL Compare 13 introduces support for deploying changes to temporal tables, which were introduced in SQL Server 2016. This means that any changes made to the definition of a temporal table in a source database, including to the retention policy for that table, will be included automatically in any deployment script that SQL Compare generates for that database.
A brief overview of temporal tables
Unlike a conventional table, which stores only the current data values, temporal tables also capture a ‘history’ of the data as it existed at any specified time. Associated with each temporal table is a ‘history’ table that stores an ‘audit’ of changes made to the data in the temporal table. Any time a row in the temporal table gets updated or deleted, it is written to the history table, along with the period for which that row was valid, denoted by the values stored in its ValidFrom and ValidTo columns. We can then use the new FOR SYSTEM_TIME clause to query the data as it existed as a specified time, as well as querying the current data in the usual way.
In this way, temporal tables offer a simple way to implement a slowly-changing dimension, as well providing a useful audit trail of data changes, which we can use, for example, to meet the requirements of regulatory compliance, or for analyzing trends in data changes, or even for recovery from row-level corruption.
Of course, temporal tables come with an additional maintenance burden. If we need to retain historical data for a long time, or the data in the temporal table changes rapidly, then the associated history table can grow very large. One way to manage data growth is to include, as part of the temporal table definition, a history retention period. For each temporal table, the history retention periods define for how long, after the system time stored in the ValidTo column, rows in the history table will be retained. Any rows in the history table that exceed this retention period are marked as eligible for automatic data cleanup and removal. This provides a neat way of invisibly maintaining the size of historical tables, as well as granting the flexibility to adjust how much history is retained for each table.
Deploying changes to temporal tables with SQL Compare
In the following example, the temporal table in the source database has a history retention period of 3 months. SQL Compare has detected that the same table in the target database has a retention period of 6 months, and so will include in the resulting deployment script an ALTER TABLE statement to reduce it to 3 months.
Summary
SQL Compare v13, and later, will read and deploy changes to a temporal table, so you can be sure that this table is consistently defined, and the data in its associated history table retained for the length of time you expected, in all databases that contain this table.
Tools in this post
You may also like
-
Article
Practical PowerShell Processes with SQL Change Automation
Starting from the object-level source code for a database, Phil Factor shows how SCA can create a NuGet build package, use it to migrate target database to the same version, and publish web-based database documentation, a report of what changed on the target and the results of a static code analysis assessment.
-
Article
Generating realistic dates using SQL Data Generator and Python
How to generate more realistic dates, in your SQL Server test data.
-
Article
Source controlling database users
Where developers have their own databases, you need to ensure the user accounts are configured identically across all development and test server, or you'll inevitably hear the phrase “…but it works on my machine”. Alex Yates describes, strategically, how this might work.
-
Article
On Quickly Investigating a SQL Monitor Custom Security Alert
Phil Factor offers a clever way to report on a SQL Server intrusion, with a query that shows a full narrative description of all the security-related changes that have been detected by a set of SQL Monitor custom metrics.
-
