Product articles
SQL Compare
Database Builds and Deployments
Keeping track of history: SQL Compare…

7 December 2017

1 Comment

7 December 2017

1 Comment

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.

  • Aris

    Would SQL Data Comparer compare the history table?

You may also like

  • Article

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

    Jamie Wallis introduces the new Summary View, which is a tab that sits alongside SQL View and provides a more concise breakdown of the differences between two objects.

  • Article

    Introducing database automation with the SQL Toolbelt

    For many companies, particularly those in the financial sector, a key technology consideration is risk management because if systems go down, it can cost a lot of money very quickly. Take ABSA Bank, a subsidiary of the Barclays Africa Group. Serving customers in South Africa and Namibia, it provides retail, business, corporate, and investment banking,

  • Event

    SEACON 2018 (The Study of Enterprise Agility Conference)

    SEACON isĀ THEĀ Enterprise Agility conference that brings business and technology together, and Redgate are pleased to be taking part as one of the sponsors. Following the sold out conference in 2017, the 2018 edition will again host FinTech practitioners and Thought Leaders in Enterprise Transformation, Entrepreneurial Leadership, Agile , DevOps, Cloud and Fintech.

  • Article

    Managing the SQL Server Estate

    Rodney Landrum describes how a monitoring tool must help us monitor, analyze and predict resource usage, including costs, across a growing and diversifying estate, and also help the organization to connect server resource usage and error conditions directly to their impact on business processes.

  • Forums

    SQL Compare Forum

    Compares and synchronizes SQL Server database schemas