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 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
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
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
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
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
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
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
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
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
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
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
» 