Temporal Tables – Part 5 – Start and End Type Precision

Share to social media

In this fifth (and maybe final until at least getting to the concurrency chapter prep) blog about temporal I wanted to briefly cover the precision of the ROW START and END times. You can use any of the datetime2 types for these values. The precision will let you choose how many changes would be seen by the user. Even with 6 digits of precision from datetime2(7), there is no guarantee that every change will be visible to the user via the temporal settings on a FROM clause, but it is generally much more likely than if you are using datetime2(0) as we will see.

In this blog, I will use datetime2(0) to give the least possible precision possible to show what can occur. Note that (not unsurprisingly) you have to use the same precision for both ROW START and END times or you get the following error:

I will use the same basic structures I have used in previous examples, so if you have created the tables, you will need to drop the table and the history table:

To generate some data, I will use SEQUENCE object that I will format to put out a hexedecimal value, which I will put into a default constraint so the repeating code will be easier to read.

Next let’s create a sequence of history rows, starting with a simple insert, wait a second, then three sets of 5 inserts.

The final state of the row has the Data column = ‘0000000010’, and you can see the complete progression from ‘0000000001’ through ‘000000000F’.

Starting at the first second that was recorded, the first thing you can see is the row where Data = ‘0000000001’:

But the next row you will see will not be ‘0000000002’, it will actually be ‘0000000006’. Using fractional times will be truncated. Such as if we try ‘2016-05-02 02:53:09.9’.

This returns the same thing that 2016-05-02 02:53:09 does:

If you use the next second, you will get ‘0000000006’:

This returns:

The only way you will see rows ‘0000000001’ – ‘0000000005’ is to query the history table. We can only see rows where ValidStartTime <> ValidEndTime. In the following query I will get all of the rows that you can see in the table using each second:

Which returns:

Note that this is the same output you will see if you execute the following query that returns all data:

Or

So you will definitely want to set your precision to the level that you will have the most likelihood of seeing all changes in your data. Once multiple connections are making simultaneous changes, you wouldn’t wnt to lose data that would be interesting to the user.

Louis Davidson

Simple Talk Editor

See Profile

Louis is the editor of this Simple-Talk website. Prior to that, has was a corporate database developer and data architect for a non-profit organization for 25 years! Louis has been a Microsoft MVP since 2004, and is the author of a series of SQL Server Database Design books, most recently Pro SQL Server Relational Database Design and Implementation.

359

1