Views
One of the features that I really like about Time Travel Query is an issue that’s related to the access path discussion from Time Travel Gotchas – Part 2 (see https://www.triton.co.uk/time-travel-gotchas-part-2/). The optimizer, being the canny chunk of software that it is, will take your simple SYSTEM_TIME query and convert it into the necessary sub-queries to make sure all required historical data is accessed to.
What it will also do is accept a query against a view that contains System temporal table(s) in its definition. So I have taken the query used in the previous section, removed all SYSTEM_TIME clauses and used it to create a view:
With the tables as they were at the end of my previous example, this query works fine
You avoid the need to put a FOR SYSTEM_TIME AS OF against each table, but you lose the ability to specify a different time against each table. Now I‘ve yet to find a situation where I’d want to do that but I guess it’s possible. What might be more likely is that you would want to specify a SYSTEM TIME for just one of the tables, or a sub-set of the tables within the view.
That’s the only gotcha there; your specified AS OF date gets applied to each and every System temporal table within the view. I still think it’s a neat bit of short-hand.
So there are a couple of things that have caused me some headaches. It’s not an exhaustive list and it’s not got a cast-iron set of solutions but forewarned is forearmed and I hope you’ll get to spot some of the pitfalls before they become a problem.
Click here for Time Travel Part 1 and Time Travel Part 2