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'
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
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
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
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
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
- 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
Thank you,
Very well explained. Helped me a lot.