Prashanth Jayaram
Dropping temporal tables with SSMS

Temporal Tables in SQL Server

February 28, 2019 by

Temporal tables have been a feature of SQL Server since version 2016. SQL Server professionals have historically had several options to track data change events. The evolution of the data tracking mechanism started with Change tracking (CT), Change Data Capture (CDC) and now Temporal Tables.

Introduction

In my experience, I have seen that a few projects still use custom data tracking solutions. And in a few other enterprises, third-party solutions are in place to manage the same. In this article, we’ll discuss temporal tables in SQL Server 2016.

GDPR (General Data Protection Regulation)

As we all know, the General Data Protection Regulation (GDPR), a new data privacy regulatory act, introduces significant changes in the way organizations collect or use data. Enterprises must adhere to the data privacy and protection act.

To comply with GDPR requirements, organizations strive towards working with various tools and techniques.

SQL Server offers various native tools and techniques and is capable of fulfilling most of the security requirements for being GDPR complaint.

Getting Started

Let’s deep-dive into the concepts of temporal table and see what can be derived out of this feature.

What is Temporal Table?

Temporal tables, also known as system-versioned tables, provide us with new functionality to track data changes. It allows SQL Server to maintain and manage the history of the data in the table automatically. This feature provides a full history of every change made to the data.

It was first introduced in ANSI (American National Standards Institute) SQL 2011 standard. Now, it’s available in SQL Server 2016 and later versions.

Temporal tables are considered as one of the critical features used to audit SQL Server data.

The two period columns which are of datetime2 data type, SysStartTime and SysEndTime define the current and historical data for every row of the table. It defines the validity of the data. The DateTime range has a value that is appropriate during that timeframe. By querying the table, we can easily see the transitioning of the data to its various states within the specific date-time intervals.

Why Temporal tables?

The following are some usage scenarios of Temporal tables

  1. Auditing
  2. Rebuilding the data in case of inadvertent changes
  3. Projecting and reporting for historical trend analysis
  4. Protecting the data in case of accidental data loss

Demo

Let’s jump in to get started with the demonstration.

The following example creates an employee temporal table created with a few sample rows.

By default, the SQL Server will automatically generate a name for the history table in the following format

dbo.MSSQL_TemporalHistoryFor_<ObjectID>.

Naming the history table in temporal tables

If you browse the table, we can see the absence of “Delete” option for Temporal Tables properties.

Temporal tables have no delete option

To get the delete option, first, turn off the system_versioning setting using the alter table statement. After that, you’ll notice that the tables, temporal table and history table become a regular table.

You can also use the SSMS generate script option to drop the temporal table.


Dropping temporal tables with SSMS

Now, the tables can be dropped either by using T-SQL or SSMS.

How to Create a Custom Name for Temporal History table

In this section, we’ll see how to create a custom temporal table along with a consistency checker option. In this case, the employeeHistory is the history table and DATA_CONSISTENCY_CHECK=ON is used with the WITH clause of the create table statement.

The create table statement is discussed in detail here.

In the following output, we can see that the custom history table dbo.EmployeeHistory is created as part of the Create table statement.

Custom history table in temporal tables

Insert a few records into the employee table

Query the employee table


Our initial test table for creating a temporal table

Now, If you want to hide the SysStartTime and SysEndTime columns from viewing, add a hidden keyword in the create table statement so that the column does not show up in a select query.

Let us perform SQL Update DML operations to understand the usage of SYSTEM_TIME column.

For example, SMITH’s salary has got an increment of 2000. The update SQL is as follows.


Seeing initial data in our temporal table

After the update, we can see that the Salary column is updated. The column SysEndTime is a reference pointer to indicate the active record of the table. It’s really easy to audit the data flow by referring the SysStartTime and SysEndTime.

The SysEndTime ‘9999-12-31 23:59:59.9999999’ always refers to the active record set. The dbo.emplyeehistory table has the previous reference pointers of the modified data. In this case, the column, salary, is seen as updated on “2018-01-25 20:31:40.9852938”. The unmodified record made an entry into the history table with the updated timestamp. The updated timestamp becomes the SysStartTime column of the modified record.

The entire update process can be easily traced by referring the Sys columns that is arranged in a Zig-Zag manner.

Reviewing data in our temporal table

Now, Let us perform the SQL Delete operation to delete the employee record 7369 and see what happens

The output is an empty result set. The SysEndTime is updated with the timestamp of DELETE statement. This indicates that the record was deleted from the table.

>Viewing deleted data in our temporal table

The FOR SYSTEM_TIME clause has many variations and options. It is further classified into four temporal sub-clauses. This provides a way to query the data across current and history tables.

  1. AS OF <datetime>
  2. FROM <startdatetime> TO <enddatetime>
  3. BETWEEN <startdatetime> AND <enddatetime>
  4. CONTAINED IN (<startdatetime> , <enddatetime>)
  5. ALL

The AS OF clause is used when there is a need to rebuild the original state of the data and need to know the state it was at any specific time in the past. This is possible by specifying the date time as its input.

Here are some other sub-clause temporal options that are useful for data audit.

Summary

In this article, we see the step-by-step procedures to setup temporal tables in SQL Server. Temporal Tables are generally useful in scenarios that require tracking the history of data changes. In some cases, it can be used to reconstruct the data. Its steps are pretty simple and straightforward. It doesn’t require writing triggers or stored procedures or any application code change. Data retrieval is made very simple using the SYSTEM_TIME clause along with the associated sub-clause.

The history table can grow big. It depends on the number of transactions on the temporal table so, space is the biggest tradeoff. You can consider purging the data from the history table.

Prashanth Jayaram
Auditing, Temporal tables

About Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application. My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB. View all posts by Prashanth Jayaram

168 Views