Getting Started with Temporal Table in SQL Server 2016 – Part 2

Introduction

SQL Server 2016 introduces the temporal table (also known as ‘system-versioned’ temporal table) feature to store history of data changes in a table so that you can travel back in time and get the data that represents a past state in time.

Temporal or System-Versioned Table

CREATE TABLE [Person].[BusinessEntityContact1](
       [BusinessEntityID] [int] NOT NULL,
       [PersonID] [int] NOT NULL,
       [ContactTypeID] [int] NOT NULL,
       [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
       [ModifiedDate] [datetime] NOT NULL,
        CONSTRAINT [PK_BusinessEntityContact1_BusinessEntityID_PersonID_ContactTypeID] PRIMARY KEY CLUSTERED 
       (
              [BusinessEntityID] ASC,
              [PersonID] ASC,
              [ContactTypeID] ASC
       ) 
)
GO 
INSERT INTO [Person].[BusinessEntityContact1]
SELECT * FROM Person.BusinessEntityContact
GO 
SELECT * FROM [Person].[BusinessEntityContact1]
GO 

Load Data into the Table
Load Data into the Table

As discussed earlier, we need to add two columns (start and end date), which will be used as SYSTEM_TIME for system versioning. Once these columns are added, I alter the table and enabl system versioning. If the table specified with the HISTORY_TABLE clause does not exist, SQL Server creates it behind the scene or if it exists, it needs to meet the requirement as outlined earlier.

ALTER TABLE Person.BusinessEntityContact1
ADD SysStartTime datetime2 GENERATED ALWAYS AS ROW START  
       CONSTRAINT P_ValidFromConstraint DEFAULT SYSUTCDATETIME() NOT NULL,
       SysEndTime datetime2 GENERATED ALWAYS AS ROW END 
       CONSTRAINT P_ValidToConstraint DEFAULT CONVERT (DATETIME2, '9999-12-31 23:59:59.9999999') NOT NULL,
       PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
GO
 
ALTER TABLE Person.BusinessEntityContact1
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = Person.BusinessEntityContact1History))
GO

Alter Table
Alter Table

Temporal Table – Querying Data

SQL Server 2016 also introduces a couple of query constructs to query current or historical data from a temporal or system-versioned table. Before we get into details, lets query the tables we used earlier. If you notice, in the image below only the current table has rows and the history table is empty at this point of time. This is because we loaded data into the current table before enabling the temporal feature and after enabling it we have not done any data modification.

SELECT * FROM Person.BusinessEntityContact1
GO
SELECT * FROM Person.BusinessEntityContact1History
GO

Query Data
Query Data

Now let’s update a few rows in the current table and run the same queries as above. As you can see in the image below, the current state of the data in the current table and history table contain all the earlier state of the data:

UPDATE Person.BusinessEntityContact1
SET ContactTypeID = 15
WHERE ContactTypeID = 11
GO
SELECT * FROM Person.BusinessEntityContact1
GO
SELECT * FROM Person.BusinessEntityContact1History
GO

Update Data
Update Data

When I ran the below query with ContactTypeID = 11, it didn’t return any rows. This is because, we have updated ContactTypeID = 11 to ContactTypeID = 15 and the current table now does not contain rows with ContactTypeID = 11. At this time, we should have  data in the history table.

SELECT * FROM Person.BusinessEntityContact1
WHERE   ContactTypeID = 11
GO

Query Data
Query Data

A standard SELECT statement will query data from the current table only; in order to query data from the history table, we need to use newly introduced query constructs. The first of them is FOR SYSTEM_TIME AS OF, which returns a table with a single record for each row containing the values that were actual (current) at the specified point in time in the past. If you can notice, the time I have specified here is the start time in the history table for these rows.

SELECT * FROM Person.BusinessEntityContact1
FOR SYSTEM_TIME AS OF '2016-03-14   09:49:41.3797317'
WHERE   ContactTypeID = 11
GO

Standard Select Statement
Standard Select Statement

The other query construct is to use FOR SYSTEM_TIME CONTAINED IN, which returns a table with the values for all record versions that were opened and closed within the specified time range defined by the two date-time values as shown below.

DECLARE   @Start datetime2 =   '2016-03-14 09:49:41.3797317'
DECLARE   @End datetime2 =   '2016-03-14 10:13:41.8144946'
SELECT * FROM Person.BusinessEntityContact1
FOR SYSTEM_TIME CONTAINED   IN(@Start,   @End)
WHERE   ContactTypeID = 11
GO

Query using FOR SYSTEM_TIME CONTAINED IN
Query using FOR SYSTEM_TIME CONTAINED IN

You can also use FOR SYSTEM FROM…TO or use FOR SYSTEM_TIME BETWEEN, which returns a table with the values for all record versions that were active within the specified time range, regardless of whether they started being active before the <start_date_time> or ceased being active after the <end_date_time>.

The basic difference between FOR SYSTEM FROM…TO and FOR SYSTEM_TIME BETWEEN is, the later one is inclusive and includes records that became active exactly on the lower boundary or ceased being active exactly on the upper boundary.

The basic difference between FOR SYSTEM_TIME CONTAINED and FOR SYSTEM_TIME BETWEEN is the first one includes record versions that were opened and closed within the specified time and the latter one includes any record versions that were active within the specified time range, regardless of whether they started being active before the <start_date_time> or ceased being active after the <end_date_time>. To demonstrate it, if you can reduce the time for end time as demonstrate earlier, the FOR SYSTEM_TIME CONTAINED will not return any rows whereas FOR SYSTEM_TIME BETWEEN will:

DECLARE   @Start datetime2 =   '2016-03-14 09:49:41.3797317'
DECLARE   @End datetime2 =   '2016-03-14 10:13:40.8144946'
SELECT * FROM Person.BusinessEntityContact1
FOR SYSTEM_TIME BETWEEN   @Start AND @End
WHERE   ContactTypeID = 11
GO

Query Using FOR SYSTEM_TIME BETWEEN
Query Using FOR SYSTEM_TIME BETWEEN

In case you need data from both the tables (current or history), you can use the ALL clause, which returns the union of rows that belong to the current and the history table as part of a single query.

Temporal Table – Schema Modification

There are few restrictions on schema modification for the table enabled for temporal or system versioning. These are a few schema modification commands that are allowed:

  • ALTER TABLE … REBUILD
  • CREATE INDEX
  • CREATE STATISTICS 

In case you want to perform any other schema modification, like dropping a table, you will encounter an error message like this:

 

Msg 13552, Level 16, State 1, Line 15
Drop table operation failed on table 'LearnTemporal.dbo.Employee' because it is not supported operation on system-versioned temporal tables.

In order to drop a table, you need to first disable the temporal feature of that given table. Once you have disabled it, you will see two tables (current and history) as regular tables and then you can delete them based on your need. To disable the temporal feature or system versioning you need to use the below command:

ALTER TABLE <Table_Name> SET (SYSTEM_VERSIONING = OFF);

Temporal Table – Viewing Meta-data

As the temporal feature is a natively supported feature, system catalog views and functions have been accordingly updated and added to provide you information about it. For example, with the script provided below, you can find out all the tables that have system versioning enabled and its corresponding history tables:

SELECT   T1.name as   TemporalTableName, SCHEMA_NAME(T1.schema_id) AS TemporalTableSchema,
       T2.name as   HistoryTableName, SCHEMA_NAME(T2.schema_id) AS HistoryTableSchema,
       T1.temporal_type_desc
FROM sys.tables T1
LEFT JOIN sys.tables T2 ON T1.history_table_id   = T2.object_id
WHERE   T1.temporal_type <>   0
ORDER BY T1.history_table_id   DESC
GO
 

Find the Tables that Have System Versioning Enabled
Find the Tables that Have System Versioning Enabled

Apart from the addition to existing catalog views (sys.tables and sys.columns), a new catalog view (sys.periods) has been added to provide periods defined for each of the tables enabled for system versioning. For example, the query below gives you period information (start and end date columns) for each of the system versioned tables in the database:

SELECT P.name as PeriodName, T.name as TemporalTableName, c1.name as StartPeriodColumnName, c2.name as EndPeriodColumnName
FROM sys.periods P
INNER JOIN sys.tables T ON P.object_id = T.object_id
INNER JOIN sys.columns c1 ON T.object_id = c1.object_id AND p.start_column_id = c1.column_id
INNER JOIN sys.columns c2 ON T.object_id = c2.object_id AND p.end_column_id = c2.column_id
  GO

Period Information for Each of the System Versioned Tables
Period Information for Each of the System Versioned Tables

Note: The feature mentioned and demonstrated in this article is based on SQL Server 2016 Release Candidate and might change when RTM is available or in future releases.

Conclusion

SQL Server 2016 introduces the Temporal table (also known as ‘system-versioned’ temporal table) feature to store history of data changes so that you can travel back in time and get the data that represents a past state in time rather than the data that is correct at the current moment in time.

In this series, I discussed this new feature, how it works, considerations and limitations to keep in mind when using this new feature. I also demonstrated creating a new table with this feature or modifing an existing table to enable this feature, querying data from temporal table or system-versioned table with newly introduced query constructs, schema modification consideration, meta-data queries, etc.

Resources

Getting Started with Temporal Table in SQL Server 2016 – Part 1

Getting Started with System-Versioned Temporal Tables

Querying Data in a System-Versioned Temporal Table

Manage Retention of Historical Data in System-Versioned Temporal Tables

See all articles by Arshad Ali

Arshad Ali
Arshad Ali
Arshad Ali works with Microsoft India R&D Pvt Ltd. He has 8+ years of experience, mostly on Microsoft Technologies. Most recently, as a SQL Developer and BI Developer he has been working on a Data Warehousing project. Arshad is an MCSD, MCITP: Business Intelligence, MCITP: Database Developer 2008 and MCITP: Database Administrator 2008 certified and has presented at several technical events including SQL-School. On an educational front, he has an MCA (Master in Computer Applications) and MBA-IT. Disclaimer : I work for Microsoft and help people and businesses make better use of technology to realize their full potential. The opinions mentioned herein are solely mine and do not reflect those of my current employer or previous employers.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles