SQL 2016 – Possible bug in temporal tables

By | January 7, 2016

I am not sure if this has been raised before, it seems like a very common scenario that I am sure most people will encounter but I wasn’t able to find any details on the internet or the connect website so here goes. While testing out temporal tables I found that if a record is updated multiple times in quick succession (sub sec or sub milli second timeframes) then the records are captured accurately within the temporal and historical tables but the records are not returned accurately when they are queried using the Temporal query syntax FOR SYSTEM_TIME BETWEEN or FROM.

It looks like temporal tables expect the end time to be datetime2(0) and as a result it loses granularity at the sub second level for updates. So if two rows are updated at the exact same time only the latest row is returned as part of the output.

If you have encountered this issue or have an explanation for why this behavior is expected please reply to the below connect item.

I have also created a YouTube video showing this behavior in action.

Connect item

https://connect.microsoft.com/SQLServer/feedback/details/2215250/missing-data-when-using-temporal-tables

Contains script to reproduce the issue as an attachment

 

Category: Databases

About admin

This post was written by Jayanth Kurup. A Microsoft SQL Server Consultant and Trainer based out of Bangalore, India. Jayanth has been working on MS SQL Server for over 15 years. He is a performance tuning and Business Intelligence expert. Having worked with companies like Microsoft, DELL, Wells Fargo, Thomson Reuters and many other fortune 100 companies. Some other technologies Jayanth works on include Microsoft Azure, PowerBI, Python and AWS. When he isn’t consulting or training, Jayanth like to travel, paint and read. He is also very active in social causes and the founder of Enabled Business Solutions. Visit his company by clicking the link in the menu or email him directly.

Leave a Reply