Problem
In a previous tip, we discussed how SQL Server temporal tables work and also how to query them, but we would like to understand more about how to use the new clause FOR SYSTEM_TIME and how the new subclauses work in SELECT … FROM type queries to get varying versions of the rows or a complete picture of the data at a particular point in time.
Solution
In SQL Server 2016, we have a new clause FOR SYSTEM_TIME in the SELECT … FROM statement, which has five new subclauses to query temporal table data:
- AS OF <datetime>
- ALL
- FROM <start_datetime> TO <end_datetime>
- BETWEEN <start_datetime> AND <end_datetime>
- CONTAINED IN (start_datetime, end_datetime)
Let’s dive into an example to understand how these subclauses work and what type of data they return and why. We will assume a real-life scenario where there are already manual solutions in place to keep track of history in some fashion. We have a Volunteers table which keeps track of each volunteer’s current duty assignment. We also have a VolunteersHistory table which keeps track of history changes for the Volunteers table. We are going to bring both tables together into a temporal solution which automatically keeps track of history. Please read this for complete requirements to add an existing history table to a temporal table.
Create Temporal Table Example
The following code will create a database, some tables and insert some sample data. We will also enable the temporal features on the tables.
-- Create a database TemporalDB for our example
USE master
GO
IF DB_ID('TemporalDB') IS NOT NULL DROP DATABASE TemporalDB
CREATE DATABASE TemporalDB
GO
USE TemporalDB;
GO
-- Volunteers table
CREATE TABLE dbo.Volunteers
(
id INT NOT NULL CONSTRAINT PK_Volunteers PRIMARY KEY NONCLUSTERED,
Serving_Area VARCHAR(20) NULL,
Volunteer_name VARCHAR(25) NOT NULL,
sysstart DATETIME2(0) NOT NULL,
sysend DATETIME2(0) NOT NULL
);
CREATE UNIQUE CLUSTERED INDEX ix_Volunteers ON dbo.Volunteers (id, sysstart, sysend);
-- Insert data into Volunteers table
INSERT INTO dbo.Volunteers (id, Serving_Area, Volunteer_name, sysstart, sysend)
VALUES
(1 , NULL, 'David', '2018-01-31 17:44:04', '9999-12-31 23:59:59'),
(2 , 'Nursing Home', 'Eliza', '2018-01-31 17:44:04', '9999-12-31 23:59:59'),
(3 , 'Nursing Home', 'Inara', '2018-01-31 17:44:04', '9999-12-31 23:59:59'),
(4 , 'Shelter', 'Sam', '2018-01-31 17:44:04', '9999-12-31 23:59:59'),
(5 , 'Shelter', 'Leo', '2018-02-01 19:54:20', '9999-12-31 23:59:59'),
(6 , 'Baby Sitting', 'Steve', '2018-03-29 18:44:04', '9999-12-31 23:59:59'),
(7 , 'Soup Kitchen', 'Aaron', '2018-03-01 17:44:04', '9999-12-31 23:59:59'),
(8 , 'School', 'Laila', '2018-03-01 17:44:04', '9999-12-31 23:59:59'),
(9 , 'Soup Kitchen', 'Eva', '2018-03-01 17:44:04', '9999-12-31 23:59:59'),
(10, 'School', 'Sean', '2018-03-29 17:44:04', '9999-12-31 23:59:59'),
(11, 'Library', 'Uriel', '2018-03-29 18:44:04', '9999-12-31 23:59:59');
-- VolunteersHistory table
CREATE TABLE dbo.VolunteersHistory
( id INT NOT NULL,
Serving_Area VARCHAR(20) NULL,
Volunteer_name VARCHAR(25) NOT NULL,
sysstart DATETIME2(0) NOT NULL,
sysend DATETIME2(0) NOT NULL );
CREATE CLUSTERED INDEX ix_VolunteersHistory
ON dbo.VolunteersHistory(id, sysstart, sysend) WITH (DATA_COMPRESSION = PAGE);
-- Insert some historical data into VolunteersHistory table
INSERT INTO dbo.VolunteersHistory (id, Serving_Area, Volunteer_name, sysstart, sysend)
VALUES
(6 , 'Shelter', 'Steve', '2018-01-31 17:44:04', '2018-03-29 18:44:04'),
(7 , 'Baby Sitting', 'Aaron', '2018-01-31 17:44:04', '2018-03-01 17:44:04'),
(9 , 'Lost Found', 'Eva', '2018-01-31 17:44:04', '2018-01-31 18:44:04'),
(9 , 'Baby Sitting', 'Eva', '2018-01-31 18:45:04', '2018-03-01 17:44:04'),
(11, 'Lost Found', 'Uriel', '2018-01-31 17:44:04', '2018-01-31 18:44:04'),
(11, 'Baby Sitting', 'Uriel', '2018-01-31 18:44:04', '2018-03-29 18:44:04'),
(12, 'Traffic', 'Emily', '2018-01-31 17:44:04', '2018-03-29 19:01:41'),
(13, 'Traffic', 'Michael', '2018-01-31 17:44:04', '2018-01-31 18:44:04'),
(14, 'Traffic', 'Tom', '2018-01-31 17:44:04', '2018-01-31 18:44:04');
-- Enable system versioning
ALTER TABLE dbo.Volunteers ADD
PERIOD FOR SYSTEM_TIME (sysstart, sysend);
ALTER TABLE dbo.Volunteers
SET ( SYSTEM_VERSIONING = ON ( HISTORY_TABLE = dbo.VolunteersHistory ) );
-- Now query tables to look at the data
SELECT * FROM dbo.Volunteers;
SELECT * FROM dbo.VolunteersHistory;
Let’s say we want to query the above data using the new subclauses that came out with temporal tables in SQL Server 2016. We mentioned in the beginning that there are 5 subclauses of FOR SYSTEM_TIME clause. Now we are going to look at them one by one and see the difference in their results. This analysis will give you a good idea where you will use each subclause based on your business requirement.
Querying Temporal Table Data Using SYSTEM_TIME AS OF and ALL
SYSTEM_TIME AS OF
The AS OF subclause returns rows from the temporal and history table that are valid up to the time you specify. It gives you the complete snapshot of the current values until the specified time.
DECLARE @datetime AS DATETIME2(0) = '2018-03-01 17:44:04';
SELECT * FROM dbo.Volunteers FOR SYSTEM_TIME AS OF @datetime ORDER BY sysend DESC;
Results:
Notice the result of AS OF query above:
- The first 5 rows are the same as they are in the Volunteers table because they did not have any changes.
- The rest of the 6 rows were changed (updated or deleted) and it shows their current values up until the specified time in the query.
- The last 3 rows are records that are no longer active as evident from the column sysend. Also, id (12) does not exist anymore in the Volunteers table, but only in the VolunteersHistory table.
SYSTEM_TIME ALL
On the other hand, ALL gives you everything from the current and history table. For our example, the Volunteers table has 11 rows and VolunteersHistory table has 9 rows, so ALL will give you all 20 rows.
DECLARE @datetime AS DATETIME2(0) = '2018-03-01 17:44:04';
SELECT * FROM dbo.Volunteers FOR SYSTEM_TIME ALL ORDER BY sysend DESC;-- ALL
GO
The above query is equivalent to the following UNION ALL query, but look at how much simpler and cleaner the temporal query is.
DECLARE @datetime AS DATETIME2(0) = '2018-03-01 17:44:04';
SELECT * FROM dbo.Volunteers WHERE sysstart <= @datetime AND sysend > @datetime
UNION ALL
SELECT * FROM dbo.VolunteersHistory WHERE sysstart <= @datetime AND sysend > @datetime;
GO
Results:
What if you just want to see the data for one particular volunteer?
DECLARE @datetime AS DATETIME2(0) = '2018-03-01 17:44:04', @id AS INT = 9;
SELECT * FROM dbo.Volunteers FOR SYSTEM_TIME AS OF @datetime WHERE id = @id
Here Eva’s current assignment is Soup Kitchen. Now try with time 1 second before the above one. We will see that Eva’s volunteering assignment at that time was Baby Sitting.
DECLARE @datetime AS DATETIME2(0) = '2018-03-01 17:44:03', @id AS INT = 9;
SELECT * FROM dbo.Volunteers FOR SYSTEM_TIME AS OF @datetime WHERE id = @id;
The value of Baby Sitting was only valid during the time shown above in the red circle. Here sysstart time is before the specified time in the query.
Querying Temporal Table Data Using SYSTEM_TIME Using FROM and TO clause
The subclause FROM-TO returns results by combing the temporal and history table, but we don’t see the current assignment of Eva because this clause excludes the upper boundary of the end time.
DECLARE
@start AS DATETIME2(0)= '2018-01-31 17:44:04', -- time of Eva's first assignment
@end AS DATETIME2(0)= '2018-03-01 17:44:04', -- time of Eva's current assignment
@id AS INT = 9;
SELECT * FROM dbo.Volunteers FOR SYSTEM_TIME FROM @start TO @end WHERE id = @id;
-- Equivalent Union Query
SELECT * FROM dbo.Volunteers WHERE id = @id AND sysstart < @end AND sysend > @start
UNION ALL
SELECT * FROM dbo.VolunteersHistory WHERE id = @id AND sysstart < @end AND sysend > @start;
GO
Results:
Querying Temporal Table Data Using SYSTEM_TIME Using BETWEEN AND pair
The subclause BETWEEN-AND returns combined results from both the temporal and history tables and we can see the current assignment of Eva. This is because this subclause includes the upper boundary of the end time.
DECLARE
@start AS DATETIME2(0)= '2018-01-31 17:44:04', -- time of Eva's first assignment
@end AS DATETIME2(0)= '2018-03-01 17:44:04', -- time of Eva's current assignment
@id AS INT = 9;
SELECT * FROM dbo.Volunteers FOR SYSTEM_TIME BETWEEN @start AND @end WHERE id = @id;
-- UNION ALL equivalent
SELECT * FROM dbo.Volunteers WHERE id = @id AND sysstart < = @end AND sysend > @start
UNION ALL
SELECT * FROM dbo.VolunteersHistory WHERE id = @id AND sysstart < = @end AND sysend > @start;
GO
Results:
Querying Temporal Table Data Using SYSTEM_TIME Using Contained IN
This subclause gives all rows from the history table included in the range specified. As you will observe in the result, Eva’s current assignment is missing. This is because CONTAINED IN only returns data from the history table.
DECLARE
@start AS DATETIME2(0)= '2018-01-31 17:44:04', -- time of Eva's first assignment
@end AS DATETIME2(0)= '2018-03-01 17:44:04', -- time of Eva's current assignment
@id AS INT = 9;
SELECT * FROM dbo.Volunteers FOR SYSTEM_TIME CONTAINED IN ( @start , @end ) WHERE id = @id;
-- Equivalent UNION ALL query
SELECT * FROM dbo.Volunteers WHERE id = @id AND sysstart > = @start AND sysend < = @end
UNION ALL
SELECT * FROM dbo.VolunteersHistory WHERE id = @id AND sysstart > = @start AND sysend < = @end
GO
Results:
Comparing Temporal Table SYSTEM _TIME Query Results
Now we will run 3 of the subclause queries together and will see their results in one screenshot which will allow us to compare them easily. Discussion follows the screenshots.
Combined Result
'2018-01-31 17:44:04' is the time of Eva's first assignment and '2018-03-01 17:44:04' is the time of Eva's current (latest) assignment. Pay attention to the 3 @end values in the queries above.
The subclause FROM-TO returns a result combing the temporal and history table, but we don’t see the current assignment of Eva because this clause excludes the upper boundary of the end time.
The subclause BETWEEN-AND returns a combined result from both the temporal and history tables and we can see the current assignment of Eva. This is because this subclause includes the upper boundary of the end time.
The CONTAINED-IN clause returns results only from the history table and includes both the upper and lower boundaries of times.
Now let us look at the same queries, but instead of using the exact time at which Eva’s current assignment was given to her, we will add 1 second of time, so the end time in this case will be ‘2018-03-01 17:44:05’.
The subclause FROM-TO query now returns 3 rows because now the time of the current assignment is under the upper boundary of the end time.
The subclause BETWEEN-AND query returns the same 3 rows as before because during this additional one second, Eva’s assignment was not changed.
The CONTAINED-IN query returns the same results as before because it only returns data from the history table.
Summary
In this article I tried to cover the FOR SYSTEM_TIME clause with examples and discussions. We are now familiar with the 5 subclauses and how they behave in a query. I tried to show them one by one and also combined together so that you can see the differences and similarities in their results. I have also shown equivalent UNION ALL queries to prove the beauty of Temporal tables. Your code will be much simpler and cleaner when you use these subclauses that came with temporal tables in SQL Server 2016. This system time-based analysis will give you a good idea where you will use each subclause based on your business requirements.
Next Steps
