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.
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…
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?
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!
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).
@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.
–Adam
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.
Also app time changes that are increasing do not require history changes.
Voted!