How to Create Temporal Tables 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?

Select*from dbo.UserHistory
 

2. What will happen after the following commands?

Altertable dbo.TemporalUserHistory set (SYSTEM_VERSIONING=OFF);
Droptable dbo.TemporalUserHistory
 

3. Can we create index the following index?

Createindex IX_DateRange on dbo.TemporalUserHistory([ValidFrom], [ValidTo])