Retrieve data at system time for multiple Temporal Tables

If you have multiple temporal tables that join together, can you retrieve the data from the temporal table at a specific point in time? We’ll find out by running a test.

First up, let’s create 2 system versioned tables and put some data in them:

Next, we’ll update both records which will add data to the history tables automatically for us:


Finally, we can select the data back out again for a particular time that we want:

The syntax for pulling it out at a particular time is ‘FOR SYSTEM_TIME AS OF <DateTime>’. You can see in the comment section of that block that I was hoping that this was something that we could put against the whole query, but it has to go against each table you join to. This kind of makes sense as not every table has to be system versioned, and even if they were, you may want to get data from different times (e.g. the current department name with the manager at a particular time). You could make the code a bit neater by putting the DateTime you’re after into a variable.

So there you have it, yes you can bring back data for a specified time from multiple temporal tables, but you need to specify the time you want on each table.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Create a website or blog at WordPress.com

Up ↑