Marko Zivkovic
C:\Users\Marko\AppData\Local\Microsoft\Windows\INetCacheContent.Word\14.png

How to query data in a System-Versioned Temporal Tables in SQL Server

February 20, 2017 by

This article will cover the querying of temporal tables in SQL Server by using the FOR SYSTEM_TIME clause and its four sub clauses AS OF, FROM TO, BETWEEN AND, CONTAINED IN. Also, we’ll cover how to clean up the history table to keep it a manageable size.

The FOR SYSTEM_TIME clause is used to perform any type of time-based analysis combaing with four sub clauses and it can be used for each table in the query independently. Also, it can be used inside table-valued functions and stored procedures.

Before we start, let’s create a SQL Server database with a temporal table dbo.People. To play alone you can copy and execute the following code in the query window:

This will create the system-versioned table dbo.People and the corresponding history table dbo.HistoryPeople:

For more information about SQL Server temporal tables, please visit Temporal tables in SQL Server.

Let’s insert and update some data:

Both system-versioned and history table can be queried by using standard

SELECT * FROM <TableName> query statement.


Querying system-versioned dbo.People table

Code:

Result:

This will return all current (actual) data:


Querying SQL Server history dbo.HistoryPeople table

Code:

Result:

This will return all data changes (Update, Delete, Merge) that were made in the dbo.People system versioned table:


ALL sub clause

This clause will return all the rows from both the dbo.People system versioned and dbo.PeopleHistory history table.

Type the following code:

Result:

The same result can be obtained by using the following query and omit the FOR SYSTEM_TIME clause:


AS FOR sub clause

Using the AS FOR sub clause can return a state of the data for each row containing the values that were current at the specified time in the past. The AS FOR sub clause returns all the records from the SQL Server system versioned and history table that satisfied the below criteria:

StartTime <= SpecifiedTime AND EndTime > SpecifiedTime

The query below will return the records which were valid at specific time in the past (‘2017-01-26 13:52:29’) for the specific PeopleID = 2:

Result:

The same result can be achieved by typing the following code and omit FOR SYSTEM_TIME AS OF:


FROM <start_date_time> TO <end_date_time> sub clause

This temporal sub clause is useful when you need to get changes for all records that were active between <start_date_time> and <end_date_time>. FROM … TO … sub clause returning the data from both tables system versioned and history.

The following criteria must be fulfilled:

StartTime < <end_date_time> AND EndTime > <start_date_time>

Execute the following code:

Result:

The same result can be achieved by typing the following code, without using FOR SYSTEM_TIME FROM… TO…


BETWEEN <start_date_time> AND <end_date_time> sub clause

This is almost the same as the FROM … TO … sub clause, except that BETWEEN … TO … sub clause includes the records that were active on <end_date_time> (i.e. StartTime = <end_date_time>). The BETWEEN … TO … sub clause returns all the row that satisfied the following criteria:

StartTime <= <end_date_time> AND EndTime > <start_date_time>

Execute the following code:

Result:

The same result can be achieved by using this code, without using BETWEEN … TO … sub clause:


CONTAINED IN(<start_date_time>, <end_date_time>) sub clause

This temporal sub- clause will return only the records that existed within the specified period boundaries. The CONTAINED IN sub clause returns all the row that satisfied the following criteria:

StartTime >= <start_date_time> AND EndTime <= <end_date_time>

Execute the following code:

Result:

The same result can be achieved using this code:


Cleaning up the SQL Server history table

Over time the history table can grow significantly. Since inserting, updating or deleting data from the history table are not allowed, the only way to clean up the history table is first to disable system versioning:

Delete unnecessary data from the history table:

and then re-enable system versioning:

Cleaning the history table in Azure SQL Databases is a little different, since Azure SQL databases have built-in support for cleaning of the history table. First, temporal history retention cleanup need to be enable on a database level:

Then set the retention period per table.:

This will delete all data in the history table older than 90 days.

SQL Server 2016 on-premise databases do not support TEMPORAL_HISTORY_RETENTION and HISTORY_RETENTION_PERIOD and either of the above two queries are executed on the SQL Server 2016 on-premise databases the following errors will occur:

For TEMPORAL_HISTORY_RETENTION error will be:

Msg 102, Level 15, State 6, Line 34
Incorrect syntax near ‘TEMPORAL_HISTORY_RETENTION’.

For HISTORY_RETENTION_PERIOD error will be:

Msg 102, Level 15, State 1, Line 39
Incorrect syntax near ‘HISTORY_RETENTION_PERIOD’.

Further reading


Marko Zivkovic

Marko Zivkovic

Marko is a Mechanical engineer, who likes to play basketball, foosball (table-soccer) and listen to rock music. He is interested in SQL code, PHP development, HTML and CSS techniques.

Currently working for ApexSQL LLC as a Software Sales Engineer, he is helping customers with any technical issues and does quality assurance for ApexSQL Complete, ApexSQL Refactor and ApexSQL Search free add-ins.

View all posts by Marko Zivkovic
Marko Zivkovic
SQL Server maintenance

About Marko Zivkovic

Marko is a Mechanical engineer, who likes to play basketball, foosball (table-soccer) and listen to rock music. He is interested in SQL code, PHP development, HTML and CSS techniques. Currently working for ApexSQL LLC as a Software Sales Engineer, he is helping customers with any technical issues and does quality assurance for ApexSQL Complete, ApexSQL Refactor and ApexSQL Search free add-ins. View all posts by Marko Zivkovic

719 Views