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:
for system_time as of '2018-02-01'
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…