Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


What Are Temporal Tables in SQL Server?
Published: Nov 29, 2020
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.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

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

Categories


© Copyright 2020 SQLTraining Ltd.