THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Davide Mauri

A place for my thoughts and experiences the Microsoft Data Platform

Temporal Tables

I have delivered a talk about “SQL Server 2016 Temporal Tables” for the Pacific Northwest SQL Server User Group at the beginning of October . Slides are available on SlideShare here:

and the demo source code is — of course — available on GitHub:

The ability of automatically keep previous version of data is really a killer feature for a database since it lift the burden of doing such really-not-so-simple task from developers and bakes it directly into the engine, in a way it won’t even affect existing applications, if one needs to use it even in legacy solutions.

The feature is useful even for really simple use cases, and it allows to open up a nice set of analytics options. For example I’ve just switched the feature on for a table where I need to store that status of an object that needs to pass through several steps to be processed fully. Instead of going through the complexity of managing the validity interval of each row, I’ve just asked the developer to update the row with the new status and that’s it. Now querying the history table I can understand which is the status that takes more time, on average, to be processed.

That’s great: with less time spent doing technical stuff, more time can be spend doing other more interesting activities (like optimizing the code to improve performance where analysis shows they are not as good as expected). 

Published Saturday, October 29, 2016 8:07 PM by Davide Mauri

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Emphyrio said:

Yes, temporal tables work, but not as good as one may expect.....Doing an update of on a row with the same values the columns already hold, will add a row to the temporal table....although no data has changed. Of course updating with the same values is a useless action, however, I would expect some cleverness here...

Also, usually, you wouldn't want to track all columns for a datachange...At the moment it is either all or nothing.

The feauture is promising though....just not ready for production use

October 31, 2016 3:01 AM

AzureDev said:

Agree with below point

Usually, you wouldn't want to track all columns for a data change...At the moment it is either all or nothing.

Is there update for it?

September 22, 2017 3:25 AM

abx said:


May 31, 2018 10:50 PM

dongdong8 said:



June 29, 2018 3:31 AM

dongdong8 said:



July 23, 2018 11:47 PM

chenjinyan said:


August 22, 2018 11:18 PM

chenqiuying said:


October 10, 2018 6:33 PM

Leave a Comment


About Davide Mauri

Director of Software Development & Cloud Infrastructure @ Sensoria, an innovative smart garments and wearable company. After more than 15 year playing with the Microsoft Data Platform, with a specific focus on High Performance databases, Business Intelligence, Data Science and Data Architectures, he's now applying all his skills to IoT, defining architectures to crunch numbers, create nice user experiences and provide meaningful insights, all leveraging Microsoft Azure cloud. MVP on Data Platform since 2006 he has a very strong background development and love both the ER model and OO principles. He is also a fan of Agile Methodology and Automation, which he tries to apply everywhere he can, to make sure that "people think, machines do".

This Blog


Privacy Statement