What Are Temporal Tables in SQL Server?
Published: Aug 24, 2019
Temporal Tables (also known as system-versioned temporal tables) are essentially tables which return data based on a specific point in time.

These are ideal for use with slowly changing dimensions in data warehouses, or simply if you wish to easily and simply keep a queryable history of your data for audit or reporting purposes.

As this is a new (well, since SQL 2016) technology, there is a new syntax to remember. Luckily it’s not that complicated as it’s been built into the standard CREATE TABLE statement.

A few months back I was talking about Slowly Changing Dimensions and therefore, if you have a look through, you’ll see that the only real ways to track history in older versions of SQL Server were to use Triggers, CDC etc. which were cumbersome and frustrating. This was made even worse when trying to query the objects based on a date range as those queries themselves sometimes had to use UNION ALL queries and were generally hidden behind views or stored procs.

This is where Temporal Tables come into their own as they remove this problem. I’ll explain all this in more detail in further posts but, for the moment, I’ll simply show you a quick example:

I have a DimCompany table which has only one record, for a company now called Pear Computers. They were previously known as Pear Technology Ltd and therefore we would normally need to query both history and current tables in order to establish what they were at a given point in time… but not with temporal tables:

select *
from dbo.DimCompany
for system_time as of '2018-02-01'

select *
from dbo.DimCompany
for system_time all



As you can see, we needed just a little special syntax rather than writing UNION ALL statements across multiple tables in order to obtain our results. This makes Temporal Tables incredibly powerful. They effectively do all the hard versioning work for us under the covers.

In the next post I’ll show how to create and use them…

Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron