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.

• Part 1: Introduction to Temporal Tables and DML operations on the Temporal Table
• Part 2: Querying System-Versioned Temporal Table
• Part 3: Enabling System Versioning for an Existing Regular Table
• Part 4: DDL operations on the System-Versioned Temporal Table

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:

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:

Below is the depiction of the above DML operations on the 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.

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:

Below is the execution plan of this query execution.

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

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

Below is the execution plan of this query execution.

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

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

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.

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

Below is the execution plan of this query execution.

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

Below is the execution plan of this query execution.

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:

• Native JSON Support in Sql Server 2016
  • FOR JSON Clause in Sql Server 2016
  • OPENJSON Function in Sql Server 2016
  • ISJSON Function in Sql Server 2016
  • JSON_VALUE Function in Sql Server 2016
  • JSON_QUERY Function in Sql Server 2016
  • lax and strict JSON Path modes in Sql Server 2016
  • Indexing Strategy for JSON Value in Sql Server 2016
• DROP IF EXISTS Statement in Sql Server 2016
• Compare Execution Plans in Sql Server 2016
• Live Query Statistics in Sql Server 2016
• DATEDIFF_BIG Function in Sql Server 2016
• Difference between DATEDIFF and DATEDIFF_BIG functions in Sql Server
• SESSION_CONTEXT in Sql Server 2016