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 OF sub clause

Using the AS OF 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 OF 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’.

Marko Zivkovic
Maintenance, Temporal tables

About Marko Zivkovic

Marko aka “Zivko” is a senior software analyst from Nis, Serbia focusing on SQL Server and MySQL as well as client technologies like SSMS, Visual Studio, and VSCode. He has extensive experience with quality assurance, issue escalation/resolution, and product evangelism. He is a prolific author of authoritative content related to SQL Server including a number of “platinum” articles (top 1% in terms of popularity and engagement). His writing covers a range of topics on MySQL and SQL Server including remote/linked servers, import/export, LocalDB, SSMS, and more. In his part-time, Zivko likes basketball, foosball (table-soccer), and rock music. See more about Marko at LinkedIn View all posts by Marko Zivkovic

168 Views