7 December 2017
7 December 2017

Keeping track of history: SQL Compare and 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.

Share this post.

Share on FacebookShare on Google+Share on LinkedInTweet about this on Twitter

You may also like

  • Article

    Deploying schema changes to multiple databases using the SQL Compare command line

    Sometimes, you need to synchronize multiple target databases with the same source database, or with the source code for that database, in version control. You may need to push out the schema changes to ten copies of the database on ten different servers, as part of a deployment, for example. Or there may be cases

  • Article

    How to build multiple database versions from the same source: post-deploy scripts

    This is the second post in a three-part series that explains how to use SQL Compare to maintain a single source of truth in version control for a SQL Server database which can be deployed to multiple production instances, despite the fact that some production instances have customizations. My previous article explained how to use SQL

  • Article

    How to examine differences with SQL Compare Summary View and SQL View

    During the recent Redgate Hub Launch Livestream Steve Jones introduced the new Summary View, which is available now in SQL Compare. The brand new Summary View is a tab that sits alongside SQL View and provides a more concise breakdown of the differences between two objects. We have achieved this by getting rid of the

  • Article

    Eliminating 'noise' from database deployment scripts using SQL Compare project options

    A database comparison tool like SQL Compare removes a lot of the pain behind the task of comparing SQL Server database schemas, generating a synchronization script, and then deploying the changes from the source to the target database. However, depending on how you have SQL Compare configured, there may still be some manual work to

  • Article

    A whole new way to see differences in SQL Compare

    In the latest release of SQL Compare, we’ve added a brand new way to examine the differences between two objects in your database. Until now, SQL Compare has relied on the SQL difference viewer to convey the differences between an object in the source database and its counterpart in the target database: Don’t worry, this

  • Forums

    SQL Compare Forum

    Forum for users of SQL Compare schema synchronization utility