Mustafa EL-Masry

Concept and basics of Temporal tables in SQL Server 2016

November 24, 2016 by

In this article I’ll cover all aspects of a new SQL Server 2016 feature, Temporal Tables (System-Versioned), including:

  • Introduction
  • What is a temporal table?
  • Why Temporal table?
  • How does temporal table work?
  • Consideration and limitation
  • Temporal tables vs CDC
  • Creation and configuration
  • Clean up and removal
  • References

Introduction

AS we know, Microsoft released SQL Server 2016 RTM version (13.00.1601.5) and in November of 2016 updated it by the latest CU (Security Bulletin MS16-136 (CU) KB #3194717) (13.0.2186.0) you can check this update from here.

What are temporal tables?

Temporal (system-versioned) tables store and save data changes in a table so that you can return back to it in any time. This feature is very helpful, in day to day work, for developers because many times they asked about old backups to check on some data issues or changes. With this feature there is no need now since everything will be hosted in this system-version tables themselves. With temporal table the value of each record in any time can be determined easily rather than just the current value of each record.

Why Temporal tables?

  • Data auditing – to check the values of a specific column
  • Track slowing changing dimensions –to be able to track the slowing changing values
  • Repair data corruption errors –we can retrieve the data from the historical data and by this we can fix any corrupted data
  • Real data sources are dynamic
  • No change in programming model or effect on the application

And one of the biggest advantages is that we don’t need to do workarounds to retrieve historical data and we don’t need to create custom data tracking. We just need to focus on the business logic of the solution and use the newly introduced query construct to query the data.

How does temporal table work

After enabling the temporal table feature, in any table, SQL Server 2016 will manage the two tables; the current table and the historical table (System-Versioned). The current table (physical table) will contain the last data, after the modification, and the historical table will contain the pervious values for each row after the modification.

SQL server 2016 introduced query constructs query data from these tables in a single query by using one of the following operations.

  • Point in time: AS OF <date_time>
  • Exclusive bounds: FROM <start_date_time> TO <end_date_time>
  • Inclusive lower bound, exclusive upper bound: BETWEEN <start_date_time> AND <end_date_time>
  • Inclusive bounds: CONTAINED IN (<start_date_time> , <end_date_time>)

Considerations and limitations:

There are some requirements and limitations for temporal tables we should be aware of including

  1. A Primary Key is required in the current table (System-versioned)
  2. A History table must be created in the same database as the current table
  3. Linked servers are not supported
  4. INSERT and UPDATE statements cannot reference SYSTEM_TIME period columns
  5. The Truncate table operation is not supported on temporal tables
  6. Always On supported
  7. A System-Versioned table does not allowed any constraints
  8. You can’t modify the data in history table “System-Versioned”
  9. Durable memory-optimized tables can be system-versioned
  10. Temporal table support Partitioning and column store index

For more information check this Microsoft Reference.

Temporal tables vs CDC

It depends on your business case as each feature has different functionality and usage

  • Temporal Tables can give us a version of the current table at any point in time
  • Change data capture change tracking (aka CDC) can help us for determining changes but it will not help us for reviewing (or working with) historical data

Creation and configuration

  1. Create a new table with the enabled system-versioned table feature

To do it we should consider some important things in our implementation for this table

  • System-Versioned table should have primary key to manage the relation between current table and historical table
  • The Table should have two columns with data type DATETIME2 to use for the start and end of the row period
  • Two columns used in the row period with data type (DATETIME2) should be NOT NULL

These two-period columns we can mark as HIDDEN columns, and they will not appear in the results:

Now, after we executed the above script, if we opened SQL Server management studio we will find the new table as system-versioned, and inside this table you will find the corresponding historical table for it.

DML operations on Temporal Tables:

Insert: we will push some data in the table “Countries” by using simple insert statement:

I haven’t mentioned the two DATETIME2 columns (Starttime , Endtime) because they are implemented as hidden columns and won’t impact anything in the application

After we executed the above Select statement we found the data inserted in the source table but there was not any record in the historical table. By default, when selecting using ( * ) the hidden period columns don’t not appear. To query these columns you must reference them explicitly, like this:

Update:

Let’s EXEC this update statement to update the CountryEnName and the check the execution plan to see which tables are affected by this statement:

As we see in the execution plan, this update statement inserted data in the historical table. Now let us check the historical table:

We will discover that the data that existed before the record was updated was inserted into the historical table and we can found the StartTime and EndTime for this operation in the historical table:

Cleaning up temporal tables

Delete

After the execution of the delete statement you will find that is affected on the both tables {Current table, historical table}

Truncate

Now if we need to truncate the source table “temporal table” it will not work

Note: We can’t also do any DML operation on the history table “system-versioned”

Drop

To drop the temporal table, we should do follow the below sequence to be able to drop the table

First if you executed the simple drop statement like this it will not work.

So we need to follow the below sequence for cleaning the Temporal table:

References

See more

To investigate more, you can download ApexSQL Complete, a free SSMS and Visual Studio add-in, which supports all temporal table options.


Mustafa EL-Masry

Mustafa EL-Masry

Mustafa EL-Masry is a Senior database consultant and one of the experts in Database performance tuning in the Middle East.

Currently, he is working as a Senior consultant production DBA and Development DBA in many projects in multiple government sectors. He is a Top SQL Server blogger in the Middle East, founder of the community mostafaelmasry.com, and is the second Arabic author on Microsoft MSDN in SQL Server.

Based on his current position, he solved fairly interesting problems on fairly large databases and highly sensitive performance cases.

View all posts by Mustafa EL-Masry
Mustafa EL-Masry
SQL Server 2016

About Mustafa EL-Masry

Mustafa EL-Masry is a Senior database consultant and one of the experts in Database performance tuning in the Middle East. Currently, he is working as a Senior consultant production DBA and Development DBA in many projects in multiple government sectors. He is a Top SQL Server blogger in the Middle East, founder of the community mostafaelmasry.com, and is the second Arabic author on Microsoft MSDN in SQL Server. Based on his current position, he solved fairly interesting problems on fairly large databases and highly sensitive performance cases. View all posts by Mustafa EL-Masry

1,101 Views