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.
Also in Blog
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 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
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...