Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted April 25, 2016

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

By Arshad Ali

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.

In the last article of the series, I discussed this new feature, how it works, considerations and limitations to keep in mind when using this new feature. In this article I am going to demonstrate 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.

Temporal Table – Creating a New Table with this Feature

A temporal table must have a primary key defined in order to correlate records between the current table and the history table, whereas a history table cannot have constraints like primary key, foreign key, table or column constraints, or triggers. However, the history table can contain indexes, compression, or even a columnstore index.

When you create a table with the temporal feature or enable it for an existing table, you need to add two columns for recording start and end date (to define the period of validity for each record) with data type of datetime2. These columns are referred to as SYSTEM_TIME period columns. These columns can be marked as HIDDEN to hide it from end users. Next you need to use the SYSTEM_VERSIONING keyword to enable and disable system versioning as part of the CREATE TABLE or ALTER TABLE statement.

SQL Server 2016 internally creates a history table with the same schema as your current table when you don’t specify a name for the specific history table. If you already have an existing table that you want to use it as history table, you can specify its name during table creation or modification, however you need to ensure it has the same schema as the current table and it meets other requirements as discussed earlier.

CREATE TABLE Employee
(  
  [EmployeeID] int NOT NULL PRIMARY KEY CLUSTERED 
  , [Name] nvarchar(100) NOT NULL
  , [Position] varchar(100) NOT NULL 
  , [Department] varchar(100) NOT NULL
  , [AnnualSalary] decimal (10,2) NOT NULL
  , [ValidFrom] datetime2 (2) GENERATED ALWAYS AS ROW START
  , [ValidTo] datetime2 (2) GENERATED ALWAYS AS ROW END
  , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
 )  
 WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));

As you can see in the image below, a temporal or system versioned table contains (or is linked to) a history table with the same schema:

Temporal or System-Versioned Table
Temporal or System-Versioned Table

Temporal Table – Enabling It for an Existing Table

Not only you can create a new table with the temporal feature, you can also add this feature to an existing table. To demonstrate this, l create a table based on the structure of the Person.BusinessContact table from AdventureWorks2016 sample database. Next I load data into this table to mimic a real life scenario of an existing table with the data:

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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM