Press "Enter" to skip to content

Category: Temporal Tables

Securing Temporal Tables

Daniel Hutmacher does a little locking down:

You may have already discovered a relatively new feature in SQL Server called system-versioned temporal tables. You can have SQL Server set up a history table that keeps track of all the changes made to a table, a bit similar to what business intelligence people would call a “slowly changing dimension”.

What happens behind the scenes is that SQL Server creates a separate table that keeps track of previous versions of row changes, along with “from” and “to” timestamps. That way, you can view the contents of the table as it was at any given point in time.

But how to you version the contents of a table, while hiding things like deleted records from prying eyes?

There’s not a whole lot we can do, but Daniel shows what we are able to do.

Leave a Comment

Error 13535: Data Modification Failed with Temporal Tables

Bob Dorr troubleshoots an issue:

When 2 or more workers are modifying the same row, it is possible to encounter 13535.  The time of the begin transaction and the modification are the defining properties.  When the transaction begin time is before the latest row modification, error 13535 is encountered.

Click through for an example of how you might trigger this error. This ultimately is the optimistic concurrency problem: how do you deal with multiple writers when using snapshot-based optimistic concurrency? Silently clobber or raise an error? Looks like temporal tables, like memory-optimized tables, raise an error instead of going quietly into the night.

Comments closed

Temporal Tables and Azure DevOps Deployments

Rayis Imayev notes a problem with Azure DevOps deployments:

Here is one thing that still doesn’t work well when you try to alter an existing temporal table and run this change through the [SqlAzureDacpacDeployment@1] DevOps task, whether this change is to add a new column or modify existing attributes within the table. Your deployment will fail with the “This deployment may encounter errors during execution because changes to … are blocked by …’s dependency in the target database” error message.

Read on to see what causes this problem and what we can do to work around it.

Comments closed

Making Temporal Tables Yours

Lee Markum continues a series on temporal tables:

In the initial post, we talked about some very basic items to get you up and running with temporal tables. In part 2, I’ll cover a few more things to think about and implement when working with temporal tables.

In the first post, we looked at altering an existing table to be a temporal table. Now we will look at what is involved in making a table be a temporal table from the very beginning.

Click through for Lee’s thoughts on naming, indexing, and data retention.

Comments closed

Using Temporal Tables for Created and Updated Timestamps

Daniel Hutmacher has an interesting use case for temporal tables:

You have a table that you want to add “created” and “updated” timestamp columns to, but you can’t update the application code to update those columns. In the bad old times, you had to write a trigger to do the hard work for you. Triggers introduce additional complexity and potentially even a performance impact.

So here’s a nicer way to do it, trigger-free.

Click through for the solution, as well as a warning from Daniel.

Comments closed

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.

Comments closed

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