Posted in T-SQL Tuesday

T-SQL Tuesday – June 2016: Temporal Tables

It’s T-SQL Tuesday. This month, the assignment is to write about SQL Server 2016. (A big thanks to Michael Swart (b|t) for hosting!) I have decided that I really do want to use SQL Server 2016 over SQL Server 2014 for my longer project. While I don’t have that set up just yet (more about that another time), I decided to test out the Virtual Labs with SQL 2016 for this.

One of the new features I’m excited to look into is the Temporal Table. It’s a table that where SQL Server creates a history of the records changes (inserts, updates and deletes) but will only display the “current” version by default, based on the criteria set up as designated upon creation. Here’s the more info about it:

I’ve worked with a project where we have separate activity tables to keep track of all types of changes. The application and any changes we’ve had to do in scripts for deployment had to define and execute that code separately. So I can see where something like this would be useful.

So let’s create an order table where we need to keep track of how much is owed:

1 - CreateTable

Next, let’s create our first record:

2 - Initial Insert & Select & Results

Notice how the SysStartDate and SysEndDate which were marked as Hidden as part of the table creation script are not returned when you don’t specify the column list.

Now, let’s do a simple update:

3 - Update & Initial Results

Then we can look at our hidden columns and see what’s been changed:

3 - Update & Full Results

Simple enough – two records are returned for that order.

So what happens when I set up multiple updates? Let’s create a new order and set up a loop to update in smaller increments:

4 - Insert & Results

Here’s the executed update statement, so you can see multiple updates occurred:

4 - While Loop Query Executed

And our results:

4 - While Loop Query with Results

Wait a minute . . . There are only two rows returned. We know there were multiple updates. So why am I only seeing 1 change record for this OrderID?

Way back in my SQL Server 2000 days (and possibly SQL Server 7.0 but I don’t remember for sure), I worked on a project where we had some tables that required a unique datetime field. But the inserts for multiple records were coming in so fast that we got duplicate value errors in the table. It turned out that datetime values rounded to the nearest 3 millisecond so we had to write logic to work around that limitation. So my thought is that even though we’re using the datetime2 field which goes out the microsecond, could there be some 3 millisecond rounding issue causing this?

To test this theory, I’m going to add a new record and modify the loop to add a WAITFOR DELAY using milliseconds. I can use the number of loops we’re doing to help us figure if that’s the case.

5 - Insert & Results

5 - Update Loop

And here’s the results:

First, here are the print statements so you can see the delay wait times:

5 - Update Loop - Results

Here’s the query to check and the results:

5 - Update Loop Check Query5 - Check Query Results

So based on these results, setting the waitfor delay by milliseconds didn’t make a different. All 20 results appeared properly. Maybe this was doing too much in terms of creating the waitfor delay value and added extra milliseconds or microseconds that allowed all the records to record properly.

Since this is an experiment, let’s try seeing if I can do the same thing but set the delay for microseconds. I created another new order and modified the update statement to try to set the wait time out one more decimal place, as you can see in the highlighted change:

6 - While Loop Query

But when I ran this, I got an error:

6 - Error

<insert Sad Trombone here>

So, what does this prove? You cannot set wait times to the microsecond, which makes a lot of sense to me. But this also means I can’t really prove my theory about the 3 millisecond rounding.

However . . . .

While running this test and creating these screenshots, I made a syntax mistake and got an error that I felt could throw the results I’d like to show. So I started over and I went through each step one by one again to make sure the results were the same. And on the initial while loop for OrderID = 2, a.k.a. the one that started all of this, I got these results:

7 - Proof that it can be tricky

Looking at the differences in the SysStartDate values in the second row returned, it took approximate 2-3 milliseconds for 3 updates that it took to for the AmountDue to go from 30.00 to 0.00. So I do think there is a timing issue to be aware of, even if I can’t quite pin down a more exact time at which that happens with this simple test.

[UPDATE: I had a chance to look into this some more. Here’s what I found out: Temporal Tables Take 2]

Depending on how exact you need to be in logging changes to a table, this may not be perfect in you have a very fast transactional system. You can also connect temporal tables to other tables to be this history table. But that will definitely require a little more research and testing to see if that solves the problem or if we’d see the same thing we do here.




6 thoughts on “T-SQL Tuesday – June 2016: Temporal Tables

    1. Thanks, Ewald!

      And thanks for the confirmation about the time precision issue. I was a little worried that I remembered that wrong since it had been so long since I looked with it.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s