THE SQL Server Blog Spot on the Web

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

Adam Machanic

Adam Machanic, Boston-based SQL Server developer, shares his experiences with programming, monitoring, and performance tuning SQL Server. And the occasional battle with the query optimizer.

Temporal Tables: Connect Item Round Up

I've been thinking a lot about SQL Server 2016 temporal tables of late. I think it's possibly the most compelling feature in the release, with broad applications across a number of different use cases. However, just like any v.1 feature, it's not without its faults.

I created a couple of new Connect items and decided to see what other things people had submitted. I combed the list and came up with a bunch of interesting items, all of which I think have great merit. Following is a summary of what I found. I hope you'll consider voting these items up and hopefully we can push Microsoft to improve the feature in forthcoming releases.


Better, More Automatic Logging, Especially to Support Audit Scenarios

A big theme I saw across many items, and something I've also heard from attendees of my sessions when I've presented on temporal tables, is the question of audit support. History is never just about the the data itself. Metadata around who made the change, how the change was made, and sometimes even why the change was made, can be vital for a number of scenarios. Temporal tables do not improve this experience today, but I think they very easily could.

Permit additional hidden columns in temporal tables - by DBAIntrepid -

Storing audit columns and don't want your users to see them all the time? Wouldn't it be nice if they could be hidden by default? I certainly think so...

Provide a mechanism for columns to automatically update themselves - by Adam Machanic - 

UpdatedBy, UpdatedFrom, UpdatedSessionContext, and many other versions on the same theme. Every single database I see has these columns. Why can't SQL Server help us by automatically populating them on our behalf?


Temporal Tables: Improve History Retention of Dropped Columns - by Adam Machanic - 

One of the really annoying things about temporal tables is that the idea of what is and is not history can change. If I drop a column from my base table, the history of the column disappears. That, it seems to me, is unnecessary. Why not leave it (optionally?) and make it nullable?


Temporal Tables (system versioned) Enhancment - by Guy Twena -

Not the best named item; this one is requests a column update mask, similar to that provided by CDC. I agree that this would be a very useful feature.


Easier Querying 

The first time you use SQL Server temporal tables, the query experience seems amazing. It's so much better and more integrated than any other form of "history" table we've ever had in SQL Server. But the experience very quickly breaks down as soon as you try to do something a bit more complex. The issue? The temporal predicates support only literals and variables. This means that, for example, you can't encapsulate a significantly complex temporal query in a view or inline UDF. I found lots of items around these theme but I decided to include only the following one in this post, as I thought it had the best phrasing and customer impact story.

Temporal for FOR SYSTEM_TIME AS OF cannot use datetime functions. - by Eric A. Peterson - 


Better Administrative Support 

Sometimes you need to change history. Maybe you need to trim some rows to keep table sizes in check. Maybe you need to backfill some history when merging data sets. Or maybe you need to fix an error. In any case, you can do this with temporal tables by briefly turning off the feature, making your changes, and then turning it back on. This isn't something end users should be doing, but is absolutely a valid administrative concern. Unfortunately, it's tougher than it should be to encapsulate in a stored procedure.

Cannot catalog stored procedures for updating temporal columns in advance - by Greg Low - Australia -


That's it for this round up. Any other interesting items I missed? Please leave a note in the comments section if so! 

Published Tuesday, October 4, 2016 11:45 AM by Adam Machanic

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



Greg Low said:

The #1 biggest limitation is the lack of app-temporal support. Temporal tables could be perfect for slowly-changing dimensions in data warehouses but cannot be sys-temporal (ie: based on current system time). They need to support app-temporal (or preferably bi-temporal).

October 4, 2016 6:15 PM

Adam Machanic said:


I don't really agree. While I think support for application times in SQL Server would be great, the "temporal tables" feature that we have today serves an entirely different purpose, with entirely different mechanisms. The query surface area might be able to be re-used, but beyond that I'm not sure much is the same. Application times necessitate updates to the history, don't require any kind of "trigger" mechanism to do the background work, and ideally would require some form of interval data type as well as support for temporal overlap constraints so as to avoid invalid data conditions.

So I definitely wouldn't call that a "limitation" if we're talking about temporal tables; it's an entirely different feature in my mind. A feature I'd definitely like to see, but in talking to the product team about these kinds of things I've found it's much easier to get changes pushed through by focusing on evolutionary rather than revolutionary, so I'd personally keep it well away from the wish lists in this post.


October 4, 2016 8:00 PM

Greg Low said:

I like what's there but the reason I mention DW SCDs is that Microsoft put them front and center in marketing. I've now used them for that but I end up doing it in the OLTP systems instead and then migrating changes from there to a DW.

Where I run into problems though is where I have RI between multiple temporal tables that I need to combine to feed a single dimension. I have not found an elegant solution to that.

October 5, 2016 3:40 AM

Greg Low said:

Also app time changes that are increasing do not require history changes.

October 5, 2016 3:42 AM

Alex Friedman said:


October 5, 2016 9:53 AM

Leave a Comment


About Adam Machanic

Adam Machanic is a Boston-based SQL Server developer, writer, and speaker. He focuses on large-scale data warehouse performance and development, and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including "SQL Server 2008 Internals" (Microsoft Press, 2009) and "Expert SQL Server 2005 Development" (Apress, 2007). Adam regularly speaks at conferences and training events on a variety of SQL Server topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, a Microsoft Certified IT Professional (MCITP), and an alumnus of the INETA North American Speakers Bureau.

This Blog


Privacy Statement