1

I've been reading with interest about SQL Server 2016's Temporal Table capability.

It almost seems like the answer to a problem I have (and I'm sure many have had), except for one thing:

StartTime and EndTime durations are immutable.

Which is great for transactions going forward...but what if you want to model stuff that changed in the past?

Is it possible to model things like "Organization A was named X between 1970 and 1980, Y between 1980 and 1990, and Z since 1990" using temporal tables? Or are we stuck with traditional methods for this stuff?

It looks to me like temporal tables are only good with changes that happen after the temporal table is created, but I'd like someone to confirm or deny that. (Bonus points if my assumption is correct and you can point me to something that describes the ideal way to implement tables and queries to handle historical queries.)

3

Currently temporal supports system time, which means the date/time values placed in those start/end time columns are populated by the system.

In a future version, temporal will also support application time, which will provide ways to have the application dictate the time and, I expect, to update existing data with real start/end times.

The only way to override this really would be to archive data from the history table into your own table (which you may end up doing over time to keep the history table from growing forever anyway), change the times there, and use that table as part of your queries.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.