Jamie Wallis

15 March 2017

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 than only the data that is correct at that specific moment.

Why are temporal tables needed?

Simply put, they make it much easier to analyze historic data. Analysts can use temporal tables to map the evolution of data sources for many different purposes, such as auditing, reconstructing data from a specific time, calculating trends, and recovering from errors.

A system-versioned temporal table keeps a full history of data changes with the period of validity for each row managed by the database engine.

How do temporal tables work?

Each table has two columns, called period columns, with a datetime2 data type. The system uses these to record the period of validity for each row when it is modified – a period start column (SysStartTime), and a period end column (SysEndTime).

The temporal table also contains a reference to another table, the history table, with a mirrored schema that automatically stores the previous versions of each row as it gets updated or deleted with the current table.

The current (or temporal) table contains the current value for each row. The history table contains the historical values for each row, with a start and end time for the period it was valid.

To query a temporal table, there’s a new clause in the SELECT statement FROM<table>. Called FOR SYSTEM_TIME, it has five sub-clauses to query data across the current and history tables.

For example, you can search for a specific point in a temporal table using the sub-clause BETWEEN ‘<date time>’ AND ‘<date time>’.

This is supported directly on a single table, propagated through multiple joins, and through views on top of multiple temporal tables.

How does SQL Compare help?

In SQL Compare 12.2 we‘ve added support for the comparison and deployment of temporal tables in SQL Server 2016.

This means you can include temporal tables when deploying changes and comparing differences to your SQL Server databases. You can now see what’s changed in your current table, plus what will be added to the history table.

When you’re ready to deploy, the script will include the expanded temporal table:

We’ve also added a handy warning that lets you know if you’re deploying to an older version of SQL Server that doesn’t support temporal tables:

Support for temporal tables is available in SQL Compare 12.2 onwards. Make sure you’re on the latest version of SQL Compare by running Check for updates from the Help menu.

If you’re new to SQL Compare, get your free 14-day trial today and give it a go for yourself.