SQL Server 2016 Features: Temporal Tables

Temporal tables, also known as system-versioned tables, are a feature of SQL Server 2016. They provide support for obtaining information about the data stored in a table at any point in time.

Components of Temporal Tables

Temporal tables consist of two parts:

1. System-versioned table
2. History table

The system-versioned table contains all the current data while the history table contains the changes made to the data in the system-versioned table. These changes to the data are either updates, deletions, or merging where applicable. They provide a snapshot of the data at a particular point in time. The database engine (system) is responsible for determining the validity period of each row.

Applications/Uses of Temporal Table

Temporal tables are commonly used for time trend analysis, audit, slowly changing dimensions, and for repairing record-level corruptions, as outlined below.

• Trend Analysis: You can track the data changes over time in order to understand how the business is changing.
• Audit: Using temporal tables allows you to go through the entire lifetime of an entity and see what values it has had.
• Slowly Changing Dimensions: System-versioned tables operate in a similar way to a dimension with type 2 changing behavior for its columns.
• Repairing Record-level Corruptions: You can retrieve records that have been corrupted or deleted by going back to the history table, getting the record of interest, and inserting it back into the system-versioned table.

Advantages

The advantages of using temporal tables are:

1. The simplicity of coding

Temporal tables are similar in behavior to normal tables – you can sort, filter, and join them with ease as though they were permanent.

2. Speed

The execution of the temporal tables by SQL Server is fast. The temporal tables are only visible to the person creating them, and can only be used by the creator. The server will therefore have less locking and overhead locks for these temporal tables, making them faster.

3. Access Rights/Security

You needn’t worry whether you have appropriate rights to modify data in the permanent tables. You can create temporary tables and perform insertions, updates and deletions.

Disadvantages

The disadvantages of using temporal tables are:

1. Inability to use user-defined functions with insert, update, and delete statements

When using temporal tables, you will not be able to utilize your own functions with the data modification statements required. This could prove a challenge, but you can work around that by using table variables.

2. Limited speed as compared to table variables

While temporal tables are faster than the permanent ones, they are slower than table variables. This is a result of the overheads incurred when using temporal tables that are not incurred in table variables.

For DBAs: Work Within Any RDBMS

The advantage of temporal tables from the perspective of database administrators is the ability to work with data within any RDBMS. The implementation of temporal tables will involve the addition of two datetime2 columns that will define the validity period of each row, within two parts of the temporal table. These columns are the period start column, which stores the start time for the row, and the period stop column which stores the end time for the row. For more details, you can follow the tutorial on creating temporal tables, “SQL Server 2016: Temporal Tables a first class citizen.”

To learn more about how to create temporal tables or about other features of SQL Server 2016, contact Datavail today. With more than 600 database administrators worldwide, Datavail is the largest database services provider in North America. As a reliable provider of 24×7 managed services for applications, BI/Analytics, and databases, Datavail can support your organization, regardless of the build you’ve selected.

For additional resources please download our white paper, Performance Comparison of SQL Server 2016 Columnstore Indexes with Memory-Optimized Tables