SQL Server Temporal Tables: How-To Recipes

Comments 0

Share to social media

Temporal, or system-versioned, tables were introduced as a database feature in SQL Server 2016. This gives us a type of table that can provide information about the data that was stored at any specified time rather than just the data that is current. ANSI SQL 2011 first specified a temporal table as a database feature and this is now supported in SQL Server.

The most common business uses for temporal tables are:

  • Slowly changing dimensions. The temporal tables provide a simpler way to querying data that is current for a specified period of time, such as time slicing data, that well-known problem on Data Warehousing databases.
  • Data Auditing. The temporal tables provide an audit trail to determine when data was modified in the “parent” table. This helps to meet the requirements of regulatory compliance and to do data forensics when needed by tracking and auditing data changes over time.
  • Repairing or recovering record level corruptions. Establishing a way of ‘undoing’ a data change on a table’s row without downtime in case a record is accidentally deleted or updated. Therefore, the previous version of the data can be retrieved from the history table and inserted back into the ‘parent’ table. – This helps when someone (or because of some application errors) accidentally deletes data and you want to revert to it or recover it.
  • Reproducing financial reports, invoices and statements with the correct data for the date of issue of the document. Temporal tables allow you to query data as it was at a particular point in time to examine the state of the data as it was then.
  • Analyzing trends by understanding how the data changes over time with the ongoing business activity, and to calculate trends in the way that data changes over time.

In the dark days before SQL Server 2016 was introduced, the data-logging mechanism had to be established explicitly in a trigger. To give a simple example, we would need to automate the maintenance of the history for the Department table with following structure, starting with the Department table itself:

The next step is to create a Department_Log table with two additional columns that provide the history of changes

  • LogDate
  • LogAction

When the logging ‘history’ table is ready, we can create the trigger to log the changes for the UPDATE and DELETE actions:

To demonstrate how the Department_Log table works with the trigger, I updated three times the row where DeptID = 1, then deleted this row and finally, last update set DeptName column the to its original value.

The result from Department_Log table shown in the next figure:

The temporal tables feature of SQL Server 2016 can dramatically simplify the logging mechanism. This article provides step-by-step instructions on how to accomplish system-versioned tables.

To migrate a table into the temporal table, a temporal table option can be set on an existing table. To create a new temporal table, you just need to set the temporal table option to ON (for example, SYSTEM_VERSIONING = ON). When the temporal table option is enabled, SQL Server 2016 generates the “historical” table automatically, and internally maintains both parent and historical tables, one for storing the actual data and the other for the historical data. The temporal table’s SYSTEM_TIME period columns (for example SysStartTime and SysEndTime) enables the mechanism to query data for a different time slice more efficiently. The updated or deleted data moves into the “historical” table, whilst the “parent” table keeps the latest row version for updated records.

What is the catch?

The most important considerations, restrictions and limitations of Temporal Tables are:

  • In order to relate records between the Temporal Table and the history table, you must have a primary key in the Temporal Table. However, the history table cannot have a primary key.
  • The datetime2 datatype must be set for the SYSTEM_TIME period columns (for example SysStartTime and SysEndTime).
  • When you create a history table, you must always specify both the schema and table name of the temporal table in the history table.
  • The PAGE compression is the default setting for the history table.
  • The Temporal Tables support blob data types, (nvarchar(max), varchar(max), varbinary(max), ntext, text, and image), that could affect the storage costs and have performance issues.
  • Both temporal and history tables must be created in the same database. You cannot use a Linked Server to provide the Temporal Tables.
  • You cannot use constraints, primary key, foreign keys or column constraints for the history tables.
  • You cannot reference Temporal Tables in indexed views that have queries that use the FOR SYSTEM_TIME clause
  • The SYSTEM_TIME period columns cannot be directly referenced in INSERT and UPDATE statements.
  • You cannot use TRUNCATE TABLE while SYSTEM_VERSIONING is ON.
  • You are not allowed to directly modify the data in a history table.

Please reference to Books Online for complete considerations and limitations list:

Creating a Temporal Table

I have shown how to create temporal and history tables in one DDL script in Listing 1. As I mentioned earlier, the columns SysStartTime and SysEndTime with data type datetime2 for both column is required for a temporal table. The column SysStartTime has to be GENERATED ALWAYS AS ROW START NOT NULL specification, and SysEndTime has to be GENERATED ALWAYS AS ROW END NOT NULL. You’re not obliged to provide a default for those columns, but I would recommend it. Both SysStartTime and SysEndTime columns have to be specified in the PERIOD FOR SYSTEM_TIME column (as MSDN defined PERIOD, in other publications PERIOD calls clause).

Note: The system-versioned columns do not necessarily have to be named as SysStartTime and SysEndTime, but the column names should be selected to reflect the function of time-capturing. The options GENERATED ALWAYS AS ROW START/END and PERIOD FOR SYSTEM_TIME (nameFrom, nameTo) enables the temporal table feature.

Listing 1: Creating the temporal and history tables

After the temporal table is created, the underlined history table is created automatically, (Figure 1) as well as a CLUSTERED INDEX with both SysStartTime and SysEndTime (or the name that has been chosen to define system-versioning) column will be created for history table, Listing 2.

Listing 2: Creating the clustered index

If a new column must be added to the temporal table, then ALTER TABLE … ADD column DDL needs to be allowed, and the new column will be mirrored automatically on the history table.

C:\Users\AGRINB~1\AppData\Local\Temp\SNAGHTML8c54d308.PNG

Figure 1: Showing newly created temporal and history tables in Object Explorer.

However, it’s not possible to use DROP TABLE DDL for a temporal table. First, SYSTEM_VERSIONING has to be turned OFF.

Listing 3: Disabling SYSTEM_VERSIONING on Department table.

When SYSTEM_VERSIONING is set to OFF, both temporal and history tables become regular tables. The DROP TABLE command can then be used for those tables.

Setting existing table to system-versioned table

SQL Server allows you to convert an existing table into a temporal table. In order to perform this task, you need to make sure that a primary key exists on the table, and if it does not already exist, then you need to create one. Then the table has to be altered with two datetime2 data type columns and also the option GENERATED ALWAYS AS ROW START/END has to be applied with …
PERIOD FOR SYSTEM_TIME (nameFrom, nameTo).
Both options have to be completed with ALTER command. The second ALTER command enables the SYSTEM_VERSIONING property, and optionally (I recommend providing it explicitly), provide a name for the HISTORY_TABLE property, Listing 4.

For example, let’s set the existing table Department_Exist to a temporal table. Run Listing 4, then run Listing 5. Refresh the Table icon to see the result, as in Figure 2.

Listing 4: Creating table Department_Exist.

Listing 5: Adding the system-versioned columns, and enable the system-versioned on the Department_Exist table.

Existing table

Converted to temporal table

Figure 2: Comparing side-by-side Department_Exist after converted to a Temporal table.

Check the metadata of the temporal tables:

Converting an In-Memory Optimized OLTP Table to a System-Versioned Table

Although the process of converting an In-Memory Optimized OLTP table to a system-versioned table is similar, there are some differences that we need to cover and demonstrate in this section.

You need to be aware of some specific details when converting the in-memory optimized table to the system-versioned table:

  • The In-memory optimized tables must be durable (DURABILITY = SCHEMA_AND_DATA).
  • In-Memory optimized history table is created as disk-based.
  • Queries that affect only the parent table can be used in natively-compiled T-SQL modules. You cannot use Temporal queries using the FOR SYSTEM TIME clause in natively compiled modules, but it is possible to use the FOR SYSTEM TIME clause with in-memory optimized tables in ad hoc queries and non-native modules.
  • An internal In-memory optimized staging table is automatically created to accept the most recent changes (INSERT, DELETE) on changes to the in-memory optimized parent table when SYSTEM_VERSIONING = ON.
  • Data from the internal In-memory optimized staging table is regularly moved to the disk-based history table by the asynchronous data flush task. This data flush mechanism has a goal of keeping the internal memory buffers at less than 10% of the memory consumption of their parent objects. DMV sys.dm_db_xtp_memory_consumers will help to track the total memory consumption.
  • A data flush can enforce a by calling sys.sp_xtp_flush_temporal_history @schema_name, @object_name stored procedure.
  • When SYSTEM_VERSIONING = OFF, or when the schema of the system-versioned table is modified by adding, dropping or altering columns, the entire contents of the internal staging buffer is moved into the disk-based history table.
  • Querying of historical data is, effectively, done under SNAPSHOT isolation level and always returns a union between in-memory staging buffer and the disk-based table without duplicates.
  • ALTER TABLE operations that change the table schema internally must perform a data flush, which may slow down the operation.

Creating new In-Memory Optimized OLTP with System-Versioned Table option enabled

The DDL for creating a new in-memory optimized table with the temporal table options is very close in its syntax to a traditional disk-based table. The in-memory optimized table syntax has the WITH block to set MEMORY_OPTIMIZED and DURABILITY properties initially. Therefore, the SYSTEM_VERSIONING property needs to be added separated by a comma, as in Listing 7.

Listing 7: Creating new In-Memory Optimized OLTP with System-Versioned Table option enabled

Adding the System-Versioned Table option to an existing In-Memory Optimized OLTP Table.

It is more difficult to convert an existing in-memory optimized OLTP table to the system-versioned table, as we show in Listing 8.

To demonstrate this mechanism let’s create the table:

Listing 8: Creating new In-Memory Optimized OLTP table

When the table has been created, we need to add the temporal table options before any data is added to the table, as in Listing 9:

Listing 9: Adding temporal table options

If the table already contains data, then the process of converting the table to the system-versioned table is more complicated.

If InMemoryExist was created with the system-versioned option, then we need to drop the InMemoryExist and InMemoryExist_History tables:

We recreate the table (use the code sample, Listing 8, provided above in this section). Then we insert data into the InMemoryExist table:

When you run the code to add temporal table options, listing 9, then the following errors will be thrown:

Cannot set SYSTEM_VERSIONING to ON when SYSTEM_TIME period is not defined.

To avoid these errors, we need to add system-versioning options in more detailed steps:

Now the InMemoryExist table is enabled for system-versioning processes.

Specifying DATA_CONSISTENCY_CHECK property

You ought to set SYSTEM_VERSIONING with DATA_CONSISTENCY_CHECK = ON to enforce data consistency checks on existing data. However, the DATA_CONSISTENCY_CHECK property currently has a memory leak profile when it used . If you decided to enable DATA_CONSISTENCY_CHECK for the temporal tables, then make sure that your instance has Cumulative Update 1 for SQL Server 2016.

Here is an example of enabling the DATA_CONSISTENCY_CHECK property on existing tables:

Conclusion

The Temporal Table is a very useful SQL Server 2016 feature with which to automate row-versioned processes. It simplifies the task of archiving data and can also be a real fix to utilize a slowly-changing dimension for the data warehouse databases. Because it is so easy to set up new as well as existing tables, the Temporal Table feature is a good choice to implement with SQL Server databases.

Load comments

About the author

Alex Grinberg

See Profile

Alex Grinberg is a senior SQL Server DBA with more than 20 years of IT experience. He has been working on Microsoft SQL Server products since version 6.5. Alex is currently working in the Pennsylvania branch of Cox Automotive, headquartered in Atlanta, GA. His primary focus is to provide architecture, tuning, optimization, analysis, operational and development services, create new applications, convert legacy technologies (SQL Server, VB.NET and C# ) and provide on-site training with the latest Microsoft technologies including .NET (VB and C#), SSRS, SSIS. Alex is a frequent speaker at professional IT events, including SQLSaturdays, Code Camps, SQL Server User Groups, and other industry seminars, where he shares his cumulative knowledge. He is the guest author for SQLServerCentral.com and is also the co-founder of HexaArt inc., an IT consulting services company for small and mid-size corporations.