Select Page

How to Create Temporal Tables in SQL Server 2016

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.

 

Script

 

/************ 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

(

[ID] intidentity(1,1)NOTNULLPRIMARYKEYCLUSTERED

, [Name] varchar(100)NOTNULL

, [Number] intNOTNULL

, [ValidFrom] datetime2 (2)GENERATEDALWAYSASROWSTART

, [ValidTo] datetime2 (2)GENERATEDALWAYSASROWEND

,PERIODFORSYSTEM_TIME (ValidFrom, ValidTo)

)

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

Questions

  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])
     

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.

12c Upgrade Bug with SQL Tuning Advisor

This blog post outlines steps to take on Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017
Oracle DBA Skills

8 Things Every Beginner Oracle DBA Should Know

A checklist of eight critical skills and areas an entry-level Oracle DBA should be familiar with to succeed on the job.

Patrick Gates | November 30, 2016

Best RAID For SQL Server | RAID 0, RAID 1, RAID 5, RAID 10

Which RAID should you use with SQL Server? Learn the differences between RAID 0, RAID 1, RAID 5, and RAID 10, along with best practices.

Eric Russo | June 8, 2015

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