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.
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.
Also in Hub
Using TOP in a SELECT statement without a subsequent ORDER BY clause is legal in SQL Server, but meaningless because asking for the TOP 10 rows implies that the data is guaranteed to be in a certain o...
Also in Product learning
People can, and do, argue a great deal about the relative merits of table variables and temporary tables. Sometimes, as when writing functions, you have no choice; but when you do you’ll find that b...
Also in SQL Compare
I'm a big fan of using SQL Compare during proof-of-concept (POC) development. During the very early stages, I'm often unsure of the value of my current coding efforts and am not ready to commit unstab...
Also about Temporal tables
In SQL Server 2016, Microsoft introduced support for system-versioned temporal tables. Temporal tables are a database feature that provides information about data stored at any point in time, rather t...