Press "Enter" to skip to content

Category: Temporal Tables

Notes on Temporal Tables

Hugo Kornelis wraps up a discussion of temporal tables with miscellany:

Of course, it will be a quite common requirement to query products and their suppliers. When querying the present, you can just access the Suppliers and Products table without the “FOR SYSTEM_TIME” keyword, and the execution plan will unsurprisingly show that a regular join of the two “current” tables is used, with no reference to the history table. Nothing special. We already saw in the earlier parts that querying the present simply ignores the history table; joining does not make that different.

But what if, for instance, we want to show all data as it was valid on June 3 at noon? Well, that is also simple. We already know that we can use FOR SYSTEM_TIME AS OF” to get the rows from a single temporal table as they were at a specific time. We can use that syntax for both tables, to get the data we need:

Read on as Hugo dives into some messy problems. Temporal table queries can expand out in complexity very quickly, as this post shows.

Leave a Comment

Temporal Table Performance Scenarios

Hugo Kornelis continues a series on temporal table performance:

Welcome to part eighteen of the plansplaining series. Like the previous posts, this one too focuses on temporal tables and their effect on the execution plan. After looking at data modifications in temporal tables and at querying with a most basic temporal form of temporal query, let’s look at the more advanced variations for temporal querying.

We’re still looking at getting data from a single query only in this post. We’ll look at joins in the next post.

Click through for these scenarios.

Comments closed

Altering Columns in Temporal Tables

Meagan Longoria explains the process around table alteration when it’s a temporal table:

System-versioned temporal tables were introduced in SQL Server 2016. They provide information about data stored in the table at any point in time by storing an effective dated version of each row rather than only the data that is correct at the current time

You can alter a temporal table to add or change columns, but you must first turn off system versioning. Let’s look at an example.

The example here relates to a computed column, so a bit more work has to happen due to the way you define computed columns.

Comments closed

Temporal Table Data Retrieval Execution Plans

Hugo Kornelis digs into execution plans when retrieving data from temporal tables:

The query above will simply grab the requested data from the Products table as if it were a normal table. The corresponding history table is not used at all. To understand why this happens it is important to recall that system-versioned temporal tables always store the currently valid version of the row in the table itself; all older, no longer valid versions are stored in the accompanying history table. So the query above, which does not use any specific temporal logic, means “I don’t care about the history, I want the data as it is now”. Due to how temporal tables are designed, SQL Server only has to query the actual table for this.

Since there is nothing special or interesting about this, let’s move on to queries that do use special logic in the query to retrieve older versions of the data.

Click through for quite a bit more detail on what it looks like for less-regular queries.

Comments closed

Temporal Tables and Execution Plans

Hugo Kornelis starts a new sub-series within an existing series:

Welcome to part sixteen of the plansplaining series. The first of a few posts about how temporal tables affect execution plans. In this post, I’ll build on the last four posts on data modifications, building on the more generic discussion of data modification in the previous four posts. Later posts will look at data retrieval and some specific scenarios.

Hugo hits the highlights of temporal tables and how they handle insertion, deletion, and updating scenarios.

Comments closed

Change Tracking with Temporal Tables

Erik Darling continues a line of thought:

Temporal Tables are different, because it will add new, and drop removed columns from the history table as they change in the base table. It’ll also push data type changes across. But the history table will not have the various permutations tracked. It just mirrors current state.

The one thing I do want to mention is that the column Erik calls JunkDate isn’t junk at all in its proper context—it’s the row end date for a Kimball-style dimension. I get that it isn’t useful in the “type 1” table, but it’s quite useful in the “type 2” table and the sorts of queries temporal tables were expected to solve use the row start date and the row end date together to give you an idea of whether the version of the row you’re seeing is the current version or not.

Comments closed

Computed Columns and Temporal Tables

Randolph West shows us how to include computed columns in temporal tables:

As I say in my Back to the future with Temporal Tables session, there are several limitations we should be aware of with this feature. One I had not previously considered is that computed columns are not permitted in a history table. This is a problem if we are converting an existing auditing process to make use of temporal tables, and we have computed columns in our table.

Computed columns are columns that are created from an expression based on one or more existing columns in a table. They are useful if we need to index a portion of a wider column, for example.

Hat tip to Erik Darling for coming up with the solution.

Comments closed

Time Travel in Snowflake

Koen Verbeeck shows an interesting feature in Snowflake:

Time travel in Snowflake is similar to temporal tables in SQL Server: it allows you to query the history rows of a table. If you delete or update some rows, you can retrieve the status of the table at the point in time before you executed that statement. The biggest difference is that time travel is applied by default on all tables in Snowflake, while in SQL Server you have to enable it for each table specifically. Another difference is Snowflake only keeps history for 1 day, configurable up to 90 days. In SQL Server, history is kept forever unless you specify a retention policy.

How does time travel work? Snowflake is built for the cloud and its storage is designed for working with immutable blobs. You can imagine that for every statement you execute on a table, a copy of the file is made. This means you have multiple copies of your table, for different points in time. Retrieving time travel data is then quite easy: the system has only to search for the specific file that was valid for that point in time. Let’s take a look at how it works.

It looks interesting, though the “Snowflake doesn’t have backups like you know them in SQL Server” gives pause.

Comments closed

Error Messages Related to Temporal Tables

Mala Mahadevan digs into temporal tables:

Last month I was fortunate to have my first ever article published on Simple-Talk, among the best quality website for sql server articles ever. During the process of writing this article I ran into several errors related to temporal tables that I have not seen before. Some of these are documented by Microsoft, some are fairly obvious to understand and others are not. Below I summarize the list of errors you can possibly run into if you are using this really cool feature.

Click through for the list.

Comments closed