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.

Temporal tables 1

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:

Temporal tables 2

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:

Temporal tables 3

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.

Tools in this post

SQL Compare

Compare SQL Server schemas and deploy differences fast.

Find out more

Share this post.

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

Related posts

Also in Blog

SQL Monitor plans for the second half of 2017

SQL Monitor's dev team has made huge improvements to the product over the last year. In the first half of 2017 alone, they released reporting capabilities, support for collecting metrics from VMWare h...

Also in Redgate products

SQL Data Mask: new masking features, constants and composition

SQL Data Mask is the latest prototype to come out of Foundry, Redgate’s research and development division. It copies your database while anonymizing personal data. You can use it to mask your databa...

Also about SQL Compare

How to deploy a database plus static data using SQL Compare and SQL Data Compare

We can use the SQL Compare Pro command line with PowerShell to automate the process of creating a new build script for a version of the database, from the object scripts in the VCS. However, what if w...