Home Uncategorized Temporal Tables: Connect Item Round Up

    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 – https://connect.microsoft.com/SQLServer/Feedback/Details/1707429

    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 – https://connect.microsoft.com/sql/Feedback/Details/3105516 

    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 – https://connect.microsoft.com/sql/Feedback/Details/3105517 

    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 – https://connect.microsoft.com/SQLServer/Feedback/Details/1691517

    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 – https://connect.microsoft.com/SQLServer/Feedback/Details/2582201

    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 – https://connect.microsoft.com/SQLServer/Feedback/Details/2500716

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

    Previous articlesp_whoisactive for Azure SQL Database – Attempt #2
    Next articleT-SQL Tuesday Rules of Engagement
    Adam Machanic helps companies get the most out of their SQL Server databases. He creates solid architectural foundations for high performance databases and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has contributed to numerous books on SQL Server development. A long-time Microsoft MVP for SQL Server, he speaks and trains at IT conferences across North America and Europe.


    1. 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).

    2. @Greg
      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.

    3. 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.

    Comments are closed.