Select Page

SQL Server 2016 Features: Temporal Tables

Author: Mehul Joshi | | February 14, 2017

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

How to Solve the Oracle Error ORA-12154: TNS:could not resolve the connect identifier specified

The “ORA-12154: TNS:could not resolve the connect identifier specified” Oracle error is a commonly seen message for database administrators.

Vijay Muthu | February 4, 2021

Using Nulls in DB2

If a column “value” can be null, it can mean one of two things: the attribute is not applicable for certain occurrences of the entity, or the attribute applies to all entity occurrences, but the information may not always be known.

Craig Mullins | April 6, 2015

MongoDB Best Practices: Design, Deployment & More

This post provides a rundown of best practices to use when running MongoDB.

Esayas Aloto | February 28, 2017

Subscribe to Our Blog

Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS