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

Related posts

Also in Hub

Finding code smells using SQL Prompt: procedures that lack schema-qualification

SQL Prompt implements a static code analysis rule, PE001, which will check code automatically, during development and testing work, for occurrences of a stored procedure being called, via the EXECUTE ...

Also in Product learning

How SQL Monitor Monitors Azure-based SQL Server Failover Cluster Instances

If you're working with SQL Server, one of the most appealing features of cloud-based computing services, such as Azure and AWS, is that they make it so much easier to design systems that are both high...

Also in SQL Compare

How to Customize Schema Comparisons using Auto Map in SQL Compare

It's a tedious task to have to compare two versions of a SQL script, side by side, for example to find differences between the version of the script on Production, and the one on the Test system. As a...

Also about Temporal tables

Time travelling with SQL Compare

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...