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