Tag Archives: FOR SYSTEM_TIME CONTAINED

Temporal Tables in Sql Server 2016 Part 2: Querying System-Versioned Temporal Table

Sql Server, Sql Server 2016, , , , , , , , , , , , , , ,

This is the second article in the series of articles on the Temporal Tables, below is the complete list of articles on System-Versioned Temporal Tables. This article covers the querying of the Temporal tables by new temporal querying clause FOR SYSTEM_TIME and its five sub-clauses in the SELECT…FROM construct. This construct simplifies the querying of the History data and hides the complexity from the users. Even though the temporal table data exists in two tables one is the current table and other is the history table, this construct hides all the complexity of fetching data from these two tables.

Let us create a demo database with Temporal Table dbo.Customer with the following script:

CREATE DATABASE SqlHintsQueryingTemporalDB
GO
USE SqlHintsQueryingTemporalDB
GO
CREATE TABLE dbo.Customer 
(  
  Id INT NOT NULL PRIMARY KEY CLUSTERED,
  Name NVARCHAR(100) NOT NULL, 
  StartTime DATETIME2 GENERATED ALWAYS AS ROW START
            HIDDEN NOT NULL,
  EndTime   DATETIME2 GENERATED ALWAYS AS ROW END 
            HIDDEN NOT NULL,
  PERIOD FOR SYSTEM_TIME (StartTime, EndTime)   
) 
WITH(SYSTEM_VERSIONING= ON (HISTORY_TABLE=dbo.CustomerHistory))

The above script will create the Temporal Table dbo.Customer and the corresponding History Table dbo.CustomerHistory as shown in the following image:

SSMS View of Temporal and History Table

Let us populate the data in the temporal table by performing the following DML operations:

INSERT INTO dbo.Customer VALUES (1,'Basavaraj')
GO
WAITFOR DELAY '00:02:00'
GO
UPDATE dbo.Customer SET Name = 'Basavaraj Biradar' WHERE Id = 1
GO
WAITFOR DELAY '00:02:00'
GO
UPDATE dbo.Customer SET Name = 'Basavaraj P Biradar'
WHERE Id = 1
GO
WAITFOR DELAY '00:02:00'
GO
UPDATE dbo.Customer SET Name = 'Basavaraj Prabhu Biradar'
WHERE Id = 1
GO

Here I am keeping a delay of 2 minutes between each DML operations by using the WAITFOR DELAY statement.

Below image shows the records in the dbo.Customer and dbo.CustomerHistory tables after executing the above DML statements:

Records in the Temporal and History Table

Below is the depiction of the above DML operations on the Timeline graph:

System Versioning TimeLine Graph

Now we have the Temporal Table with the sample data, let us start understanding one-by-one the five sub-clauses of Temporal querying clause FOR SYSTEM_TIME:

AS OF <datetime> sub-clause of the temporal querying clause FOR SYSTEM_TIME

This sub-clause returns a record for each row that was valid at the specified point-in-time. In other words it will bring all the records from the Temporal Table and the history table which meet the following criteria:

StartTime<= @PointInTime AND EndTime > @PointInTime

Let us try to get the state of the data that was valid at ‘2015-12-29 07:51’. This point-in-time in the past is depicted in the below milestone graph by Yellow milestone point circle.

FOR SYSTEM_TIME AS OF TimeLine Graph

Execute the following query to get the records which were valid in the past point-in-time ‘2015-12-29 07:51’ by enabling the execution plan

SELECT Id, Name, StartTime, EndTime 
FROM dbo.Customer
FOR SYSTEM_TIME AS OF '2015-12-29 07:51'

RESULT:
FOR SYSTEM_TIME AS OF Example 1

Below is the execution plan of this query execution.

FOR SYSTEM_TIME AS OF Execution Plan

From the execution plan we can see that the FOR SYSTEM_TIME AS OF clause internally retrieving the data from both the Temporal Table and History Table and returning the UNION of the results from both the tables. One more observation from the execution plan is that on the temporal table we seeing Clustered Index scan, it is because our queries filter predicate is based on the Period columns but we don’t have index on these columns. So if we know that we are going to query by the Period columns it is better to add an index on these columns and by default Sql Server is adding an index on the period columns in the History table.

Below is an equivalent query of this query, which we would have written if this construct was not available:

DECLARE @PointInTime DATETIME = '2015-12-29 07:51'
SELECT Id, Name, StartTime, EndTime 
FROM dbo.Customer
WHERE StartTime<= @PointInTime  AND EndTime > @PointInTime 
UNION ALL
SELECT Id, Name, StartTime, EndTime 
FROM dbo.CustomerHistory
WHERE StartTime<= @PointInTime  AND EndTime > @PointInTime

RESULT
Equivalent of FOR SYSTEM_TIME AS OF

FROM <StartDateTime> TO <EndDateTime> sub-clause of the temporal querying clause FOR SYSTEM_TIME

This clause returns all the row versions which were some time active between <StartDateTime> and <EndDateTime> (i.e. all the row versions where StartTime is before the input <EndDateTime> and EndTime after the input parameter <StartDateTime>). In other words it will return all the row versions which match the below criteria:

StartTime < <EndDateTime> AND EndTime > <StartDateTime>

Below is an example of a FROM … TO … clause:

DECLARE @StartDateTime DATETIME = '2015-12-29 07:50',
	@EndDateTime DATETIME = '2015-12-29 07:54'
SELECT Id, Name, StartTime, EndTime 
FROM dbo.Customer
FOR SYSTEM_TIME FROM @StartDateTime TO @EndDateTime

RESULT
FOR SYSTEM_TIME FROM TO Example

Below is the execution plan of this query execution.

FOR SYSTEM_TIME FROM TO Execution Plan

From the execution plan we can see that the FOR SYSTEM_TIME FROM … TO … clause internally retrieving the data from both the Temporal Table and History Table which satisfies the criteria and then returning the UNION of the results from both the tables. Below is an equivalent query of this query, which we would have written if this construct was not available:

DECLARE @StartDateTime DATETIME = '2015-12-29 07:50',
	@EndDateTime DATETIME = '2015-12-29 07:54'
SELECT Id, Name, StartTime, EndTime 
FROM dbo.Customer
WHERE StartTime < @EndDateTime AND EndTime > @StartDateTime
UNION ALL
SELECT Id, Name, StartTime, EndTime 
FROM dbo.CustomerHistory
WHERE StartTime < @EndDateTime AND EndTime > @StartDateTime

RESULT
Equivalent of FOR SYSTEM_TIME FROM TO

BETWEEN <StartDateTime> AND <EndDateTime> sub-clause of the temporal querying clause FOR SYSTEM_TIME

This clause is same as the previous FROM <StartDateTime> TO <EndDateTime> clause except that it also includes the records which became active on <EndDateTime> (i.e. StartTime = <EndDateTime>). In other words it will return all the row versions which match the below criteria:

StartTime <= <EndDateTime> AND EndTime > <StartDateTime>

Let us execute the below query which is using the BETWEEN clause with the same @StartDateTime AND @EndDateTime as in the FROM clause example

DECLARE @StartDateTime DATETIME = '2015-12-29 07:50',
        @EndDateTime DATETIME = '2015-12-29 07:54'
SELECT Id, Name, StartTime, EndTime 
FROM dbo.Customer
FOR SYSTEM_TIME BETWEEN @StartDateTime AND @EndDateTime

RESULT
FOR SYSTEM_TIME BETWEEN AND Example

From the result we can observe that BETWEEN clause is returning one extra record whose StartTime = @EndDateTime compared to the FROM clause

Below is the execution plan of this query execution.
FOR SYSTEM_TIME BETWEEN AND Execution Plan

From the execution plan we can see that the FOR SYSTEM_TIME BETWEEN … TO … clause internally retrieving the data from both the Temporal Table and History Table which satisfies the criteria and then returning the UNION of the results from both the tables. Below is an equivalent query of this query, which we would have written if this construct was not available:

DECLARE @StartDateTime DATETIME = '2015-12-29 07:50',
	@EndDateTime DATETIME = '2015-12-29 07:54'
SELECT Id, Name, StartTime, EndTime 
FROM dbo.Customer
WHERE StartTime <= @EndDateTime AND EndTime > @StartDateTime
UNION ALL
SELECT Id, Name, StartTime, EndTime 
FROM dbo.CustomerHistory
WHERE StartTime <= @EndDateTime AND EndTime > @StartDateTime

CONTAINED IN ( <StartDateTime> , <EndDateTime>) sub-clause of the temporal querying clause FOR SYSTEM_TIME

This clause returns only the row versions which became active and ended in the specified date range. In other words it will return all the row versions which match the below criteria:

StartTime >= <StartDateTime> AND EndTime <= <EndDateTime>

Let us execute the below query which is using the CONTAINED clause with the same @StartDateTime AND @EndDateTime which are used in the previous BETWEEN clause example

DECLARE @StartDateTime DATETIME = '2015-12-29 07:50',
	@EndDateTime DATETIME = '2015-12-29 07:54'
SELECT Id, Name, StartTime, EndTime 
FROM dbo.Customer
FOR SYSTEM_TIME CONTAINED IN (@StartDateTime, @EndDateTime)

RESULT
FOR SYSTEM_TIME CONTAINED Example
Below is the execution plan of this query execution.
FOR SYSTEM_TIME CONTAINED Execution Plan

From the execution plan we can see that the FOR SYSTEM_TIME CONTAINED IN clause internally retrieving the data from both the Temporal Table and History Table which satisfies the criteria and then returning the UNION of the results from both the tables. Below is an equivalent query of this query, which we would have written if this construct was not available:

DECLARE @StartDateTime DATETIME = '2015-12-29 07:50',
	@EndDateTime DATETIME = '2015-12-29 07:54'
SELECT Id, Name, StartTime, EndTime 
FROM dbo.Customer
WHERE StartTime >= @StartDateTime AND EndTime <= @EndDateTime
UNION ALL
SELECT Id, Name, StartTime, EndTime 
FROM dbo.CustomerHistory
WHERE StartTime >= @StartDateTime AND EndTime <= @EndDateTime

FOR SYSTEM_TIME ALL

This clause returns all the row versions from both the Temporal and History table

Let us execute the below query which is using the ALL clause:

SELECT Id, Name, StartTime, EndTime 
FROM dbo.Customer
FOR SYSTEM_TIME ALL

RESULT
FOR SYSTEM_TIME ALL Example
Below is the execution plan of this query execution.
FOR SYSTEM_TIME ALL Execution Plan

From the execution plan we can see that the FOR SYSTEM_TIME ALL clause internally retrieving the data from both the Temporal Table and History Table and returning the UNION of the both the results. Below is an equivalent query of this query, which we would have written if this construct was not available:

SELECT Id, Name, StartTime, EndTime 
FROM dbo.Customer
UNION ALL
SELECT Id, Name, StartTime, EndTime 
FROM dbo.CustomerHistory

[ALSO READ] New features in Sql Server 2016: