The Power of the Temporal Table

One of the coolest things about the latest versions of SQL Server is the ability to have concurrent versions of the same row of data in a table, and know what version is appropriate for each connection. This results in less blocking for a very active database with many concurrent users.

Think of this kind of table as being three dimensions instead of two. In all SQL Tables you have rows and columns. Each row represents a new instance of the entity defined by the table. It will have a primary key consisting of data points that are one or more columns defined in the table, intended to be unique in some way. Other columns are also maintained in the table with properties representing the entity for that row.

In a versioned table you may have more than one version of a row. It will have the same primary key. However, the other columns may or may not contain different data. For example, if a persons first name was changed from William to Bill in a versioned Person table, one version of the row contains William, and one version contains Bill.

This is where things get really cool. Using metadata about the tables and connections, SQL Server is able to determine what version of the person record you should receive should you request it. Instead of worrying about locks and blocking, SQL Server maintains multiple versions, and assigns the appropriate instance to your request. That is just amazingly cool, and super efficient.

The ANSI SQL 2011 were enhanced to include this notion of Temporal Tables. If you want to read more about how SQL Server has implemented it you can navigate to https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables.

You can also roll your own instance of temporal tables by adding a date column to the primary key. You can read an example of this implementation for PosGres SQL at http://clarkdave.net/2015/02/historical-records-with-postgresql-and-temporal-tables-and-sql-2011/.

Oracle 12c has their own implementation for Temporal data you can see demonstrated at http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/12c/r1/ilm/temporal/temporal.html.

You can probably find some implementation, or roll you own for your favorite database engine.



  • Eilenblogger

    Well the obvious question is what version of the row SQL Server “thinks” I want?