Select Page

How to Create Temporal Tables in SQL Server 2016

Author: Anup Gopinathan | | January 30, 2019

System-versioned temporal tables were introduced as a new feature in SQL Server 2016, allowing you to analyze the contents of the table at any given point in time. But what exactly is a temporal table, and how can you implement it in SQL Server 2016?

What are Temporal Tables in SQL Server 2016?

The concept of temporal tables was first introduced in the ANSI SQL 2011 specifications, and then implemented in SQL Server 2016. System-versioned temporal tables provide layers of information about the data stored in the table over time, instead of a single picture into the data at the current moment in time.

Some of the situations in which you might use temporal tables are:

  • Data audits: Some of your tables may store critical information for which you need to track its version history. You can determine what values a specific entity has held during its lifetime by using temporal tables.
  • Point in time analyses: Data audits focus on the changes to an individual entity, while point in time analyses focus on changes in an entire dataset over time. Temporal tables allow you to examine datasets for long-term trends and see a snapshot of the dataset at a certain point in time.
  • Anomaly detection: Your data may contain unexpected anomalies that do not align with the expected trends and patterns. You can use temporal tables to analyze your data throughout time and identify anomalies that occur periodically or irregularly.

Temporal tables work by creating a pair of tables: the main data table and a history table that is the same as the main table, but with two additional columns for specifying time periods. The first additional column stores the start time of validity for the data in the given row, while the second column stores the end time of validity. All data changes are then stored to this history table.

In order to work with this temporal data, SQL Server 2016 includes a FOR SYSTEM_TIME clause that is used to query tables according to their state during a previous time period. As a result, you can more easily perform data audits, data forensics, and point in time analyses.

Creating a Temporal Table in SQL Server 2016

SQL Server 2016 provides three different ways for users to create a temporal table, depending on how you choose to implement the history table:

  • Anonymous history table: This method requires you to specify the schema of the current table. SQL Server 2016 will create a corresponding history table and automatically generate the table’s name.
  • Default history table: This method requires you to specify the history table schema name and table name. SQL Server 2016 will create a history table with that schema.
  • User-defined history table: This method requires you to create a history table that best fits your needs beforehand. You then reference this table during the temporal table creation process in SQL Server 2016.

Temporal Tables vs. Change Data Capture

Temporal tables are often compared and contrasted with change data capture (CDC), another technique for tracking changes to tables and databases. In general, there’s no clear winner between the two options—it depends on the business case and how the functionality of each option can help.

For one, temporal tables can give us a version of the current table at any point in time. On the other hand, CDC can help determine what changes have been made, but it will not help you review or work with historical data.

Ready for an upgrade? Download our SQL Server 2017 white paper to learn how your organization can benefit from making the move to SQL Server 2017.




/************ Creating a temporal table with a user-defined history table ************/


— Create the history table

Createtable dbo.UserHistory


[ID] intnotnull

, [Name] varchar(100)notnull

, [Number] intnotnull

, [ValidFrom] datetime2 (2)notnull

, [ValidTo] datetime2 (2)notnull



— Create the user table

CREATETABLE dbo.TemporalUserHistory



, [Name] varchar(100)NOTNULL

, [Number] intNOTNULL





WITH (SYSTEM_VERSIONING=ON (HISTORY_TABLE = dbo.UserHistory)); — Here you will associate the history table to the user table.


— Inserting some sample rows

Insertinto TemporalUserHistory(Name, Number)values (‘SB’, 8989)

Insertinto TemporalUserHistory(Name, Number)values (‘JP’,898989)


Select*from dbo.TemporalUserHistory


  1. What would be the output from below query after executing the above queries?
  2. Select*from dbo.UserHistory

  3. What will happen after the following commands?
  4. Altertable dbo.TemporalUserHistory set (SYSTEM_VERSIONING=OFF);
    Droptable dbo.TemporalUserHistory

  5. Can we create index the following index?
  6. Createindex IX_DateRange on dbo.TemporalUserHistory([ValidFrom], [ValidTo])

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

The “ORA-12154: TNS Oracle error message is very common for database administrators. Learn how to diagnose & resolve this common issue here today.

Vijay Muthu | February 4, 2021

Oracle Database Extended Support Deadlines: What You Need to Know

If you’re confused about Oracle’s extended support deadlines, you are not alone. Here’s an overview of what’s in store for 11g through 19c.

Steve Thompson | January 19, 2021

Scripting Out SQL Server Logins, Server Role Assignments, and Server Permissions

This blog reviews how you can generate scripts for SQL server logins, role assignments, and server permissions for a smooth migration.

JP Chen | October 1, 2015

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.


Work for Us

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