Dec
10
2013 Posted by Mark Gillis

Time Travel Gotchas – Part 3

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.

Time Travel Gotchas

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

Time Travel Gotchas

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

« | »
Have a Question?

Get in touch with our expert team and see how we can help with your IT project, call us on +44(0) 870 2411 550 or use our contact form…