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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE DATABASE test; GO USE test; GO CREATE TABLE People( PeopleID int PRIMARY KEY NOT NULL, Name varchar(50) Null, LastName varchar(100) NULL, NickName varchar(25) NULL, StartTime datetime2 (0) GENERATED ALWAYS AS ROW START NOT NULL DEFAULT GETUTCDATE(), EndTime datetime2(0) GENERATED ALWAYS AS ROW END NOT NULL DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'), PERIOD FOR SYSTEM_TIME (StartTime,EndTime) ) WITH (SYSTEM_VERSIONING = ON(HISTORY_TABLE = dbo.PeopleHistory)); |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
INSERT INTO dbo.People VALUES(2,'James','Smith', 'Jam',DEFAULT, DEFAULT) WAITFOR DELAY '00:01:00' UPDATE dbo.People SET dbo.People.Name = 'Thomas' WHERE dbo.People.PeopleID=2 WAITFOR DELAY '00:02:00' INSERT INTO dbo.People VALUES(3,'Joan','Johnson','Jon',DEFAULT, DEFAULT) WAITFOR DELAY '00:01:00' UPDATE dbo.People SET dbo.People.Name = 'Paul' WHERE dbo.People.PeopleID=3 WAITFOR DELAY '00:02:00' INSERT INTO dbo.People VALUES (4,'Robert','Davis', 'Rob',DEFAULT, DEFAULT) WAITFOR DELAY '00:01:00' UPDATE dbo.People SET dbo.People.Name = 'Nik' WHERE dbo.People.PeopleID=4 WAITFOR DELAY '00:02:00' UPDATE dbo.People SET dbo.People.Name = 'Brian' WHERE dbo.People.PeopleID=2 WAITFOR DELAY '00:01:00' UPDATE dbo.People SET dbo.People.Name = 'Mark' WHERE dbo.People.PeopleID=2 |
Both system-versioned and history table can be queried by using standard
SELECT * FROM <TableName> query statement.
Querying system-versioned dbo.People table
Code:
|
1 2 3 |
SELECT * FROM dbo.People |
Result:
This will return all current (actual) data:
Querying SQL Server history dbo.HistoryPeople table
Code:
|
1 2 3 |
SELECT * FROM dbo.PeopleHistory |
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:
|
1 2 3 4 |
SELECT * FROM dbo.People FOR SYSTEM_TIME ALL; |
Result:
The same result can be obtained by using the following query and omit the FOR SYSTEM_TIME clause:
|
1 2 3 4 5 6 7 |
SELECT * FROM dbo.People p UNION ALL SELECT * FROM dbo.PeopleHistory ph; |
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:
|
1 2 3 4 5 |
SELECT * FROM dbo.People FOR SYSTEM_TIME AS OF '2017-01-26 13:52:29' WHERE PeopleID=2 |
Result:
The same result can be achieved by typing the following code and omit FOR SYSTEM_TIME AS OF:
|
1 2 3 4 5 6 7 8 9 |
SELECT* FROM dbo.People p WHERE p.StartTime <= '2017-01-26 13:52:29' AND p.EndTime > '2017-01-26 13:52:29' UNION ALL SELECT ph.* FROM dbo.PeopleHistory ph WHERE ph.StartTime <='2017-01-26 13:52:29' AND ph.EndTime > '2017-01-26 13:52:29' |
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:
|
1 2 3 4 5 6 7 8 9 |
DECLARE @StartDate DATETIME2 = '2017-01-26 13:52:29' DECLARE @EndDate DATETIME2 = '2017-01-26 14:01:29' SELECT * FROM dbo.People FOR SYSTEM_TIME FROM @StartDate TO @EndDate WHERE dbo.People.PeopleID=2 |
Result:
The same result can be achieved by typing the following code, without using FOR SYSTEM_TIME FROM… TO…
|
1 2 3 4 5 6 7 8 9 10 |
DECLARE @StartDate DATETIME2 = '2017-01-26 13:52:29' DECLARE @EndDate DATETIME2 = '2017-01-26 14:01:29' SELECT * FROM dbo.People p WHERE p.StartTime < @EndDate AND p.EndTime > @StartDate AND p.PeopleID=2 UNION ALL SELECT * FROM dbo.PeopleHistory ph WHERE ph.StartTime < @EndDate AND ph.EndTime > @StartDate AND ph.PeopleID=2 |
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:
|
1 2 3 4 5 6 7 8 |
DECLARE @StartDate DATETIME2 = '2017-01-26 13:52:29' DECLARE @EndDate DATETIME2 = '2017-01-26 14:01:29' SELECT * FROM dbo.People FOR SYSTEM_TIME BETWEEN @StartDate AND @EndDate WHERE dbo.People.PeopleID=2 |
Result:
The same result can be achieved by using this code, without using BETWEEN … TO … sub clause:
|
1 2 3 4 5 6 7 8 9 10 11 |
DECLARE @StartDate DATETIME2 = '2017-01-26 13:52:29' DECLARE @EndDate DATETIME2 = '2017-01-26 14:01:29' SELECT * FROM dbo.People p WHERE p.StartTime <= @EndDate AND p.EndTime > @StartDate AND p.PeopleID=2 UNION ALL SELECT * FROM dbo.PeopleHistory ph WHERE ph.StartTime <= @EndDate AND ph.EndTime > @StartDate AND ph.PeopleID=2 |
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:
|
1 2 3 4 5 6 |
DECLARE @StartDate DATETIME2 = '2017-01-26 13:52:29' DECLARE @EndDate DATETIME2 = '2017-01-26 14:01:29' SELECT * FROM dbo.People FOR SYSTEM_TIME CONTAINED IN (@StartDate,@EndDate) WHERE dbo.People.PeopleID=2 |
Result:
The same result can be achieved using this code:
|
1 2 3 4 5 6 7 |
SELECT * FROM dbo.People p WHERE p.StartTime >= @StartDate AND p.EndTime <= @EndDate AND p.PeopleID=2 UNION ALL SELECT * FROM dbo.PeopleHistory ph WHERE ph.StartTime >= @StartDate AND ph.EndTime <= @EndDate AND ph.PeopleID=2 |
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:
|
1 2 3 4 5 |
ALTER TABLE dbo.People SET (SYSTEM_VERSIONING = OFF); GO |
Delete unnecessary data from the history table:
|
1 2 3 4 |
DELETE FROM dbo.PeopleHistory WHERE EndTime <= '2017-01-26 14:00:29'; |
and then re-enable system versioning:
|
1 2 3 4 |
ALTER TABLE dbo.People SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[PeopleHistory], DATA_CONSISTENCY_CHECK = ON)); |
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:
|
1 2 3 4 5 |
ALTER DATABASE CURRENT SET TEMPORAL_HISTORY_RETENTION ON GO |
Then set the retention period per table.:
|
1 2 3 4 |
ALTER TABLE dbo.People SET (SYSTEM_VERSIONING = ON (HISTORY_RETENTION_PERIOD = 90 DAYS)); |
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’.
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
- How to connect to a remote MySQL server using SSL on Ubuntu - April 28, 2020
- How to install MySQL on Ubuntu - March 10, 2020
- Using SSH keys to connect to a remote MySQL Server - November 28, 2019