SQL Server 2016 introduced system-versioned temporal tables to enable tracking history of changes to data. I covered this feature in First Look at System-Versioned Temporal Tables-Part 1: Creating Tables and Modifying Data and First Look at System-Versioned Temporal Tables-Part 2: Querying Data and Optimization Considerations. In this article, I provide a few tips concerning working with temporal tables, such as presenting the period column values in a desired target time zone, presenting degenerate intervals and an optimization used by the CONTAINED IN subclause.
Sample Data
Some of the examples in this article query a temporal table from the WideWorldImporters sample database. You can find the documentation about this database here. To install the sample database in your system, download the backup file WideWorldImporters-Full.bak from here. Assuming you placed the file in a folder called C:\WWI\, restore the database by running the following code (replace the source and target paths as needed):
RESTORE DATABASE WideWorldImporters
FROM DISK = N'C:\WWI\WideWorldImporters-Full.bak'
WITH
FILE = 1,
MOVE N'WWI_Primary' TO N'C:\WWI\WideWorldImporters.mdf',
MOVE N'WWI_UserData' TO N'C:\WWI\WideWorldImporters_UserData.ndf',
MOVE N'WWI_Log' TO N'C:\WWI\WideWorldImporters.ldf',
MOVE N'WWI_InMemory_Data_1' TO N'C:\WWI\WideWorldImporters_InMemory_Data_1',
REPLACE, NOUNLOAD, STATS = 5;
Presenting the Period Columns in a Desired Target Time Zone
Remember that the period columns in a temporal table hold the time in the UTC time zone as DATETIME2 values. Suppose that you need to present them as DATETIMEOFFSET values in a certain desired time zone. The recommended tool to handle such conversions is the AT TIME ZONE function, which I covered last month. Apparently, there’s a bit of trickiness around handling this need. As a reminder, to return a DATETIME2-typed value stored in a certain source time zone (UTC in our case) as a DATETIMEOFFSET-typed value with a certain target time zone, you need two AT TIME ZONE conversions—one to convert the nonoffset value to an offset one with the source time zone and another to switch the offset from the source to the target time zone. The following code demonstrates such a conversion:
DECLARE
@dt AS DATETIME2 = '20170118 12:00:00.0000000',
@sourcetimezone AS sysname = 'UTC',
@targettimezone AS sysname = 'Russian Standard Time';
SELECT
@dt AT TIME ZONE @sourcetimezone AT TIME ZONE @targettimezone AS targetvalue;
This code generates the following output:
targetvalue
----------------------------------
2017-01-18 15:00:00.0000000 +03:00
The trickiness with temporal tables has to do with the current rows which hold the maximum possible value in the type in the period end column (9999-12-31 23:59:59.9999999 when using the maximum precision). As an example, consider the following query:
USE WideWorldImporters;
SELECT StockItemID, ValidFrom, ValidTo
FROM Warehouse.StockItems
FOR SYSTEM_TIME ALL
WHERE StockItemName LIKE 'An%'
ORDER BY StockItemID, ValidFrom;
This query generates the following output:
StockItemID ValidFrom ValidTo
----------- --------------------------- ---------------------------
134 2013-01-01 00:00:00.0000000 2016-05-31 23:00:00.0000000
134 2016-05-31 23:00:00.0000000 2016-05-31 23:07:00.0000000
134 2016-05-31 23:07:00.0000000 2016-05-31 23:08:00.0000000
134 2016-05-31 23:08:00.0000000 9999-12-31 23:59:59.9999999
135 2013-01-01 00:00:00.0000000 2016-05-31 23:00:00.0000000
135 2016-05-31 23:00:00.0000000 2016-05-31 23:07:00.0000000
135 2016-05-31 23:07:00.0000000 2016-05-31 23:08:00.0000000
135 2016-05-31 23:08:00.0000000 9999-12-31 23:59:59.9999999
136 2013-01-01 00:00:00.0000000 2016-05-31 23:00:00.0000000
136 2016-05-31 23:00:00.0000000 2016-05-31 23:07:00.0000000
136 2016-05-31 23:07:00.0000000 2016-05-31 23:08:00.0000000
136 2016-05-31 23:08:00.0000000 9999-12-31 23:59:59.9999999
137 2013-01-01 00:00:00.0000000 2016-05-31 23:00:00.0000000
137 2016-05-31 23:00:00.0000000 2016-05-31 23:07:00.0000000
137 2016-05-31 23:07:00.0000000 2016-05-31 23:08:00.0000000
137 2016-05-31 23:08:00.0000000 9999-12-31 23:59:59.9999999
There are four stock items shown here, each with four versions, with the current version marked with the maximum possible value in the ValidTo column.
If you need to present the values in a target time zone that has a negative offset, e.g., Pacific Standard Time, the local time is adjusted backward. When applied to the maximum value in the type, it’s expressible in the target time zone: 9999-12-31 15:59:59.9999999 -08:00. The following code demonstrates this:
DECLARE
@targettimezone AS sysname = 'Pacific Standard Time';
SELECT StockItemID,
ValidFrom AT TIME ZONE N'UTC' AT TIME ZONE @targettimezone AS ValidFrom,
ValidTo AT TIME ZONE N'UTC' AT TIME ZONE @targettimezone AS ValidTo
FROM Warehouse.StockItems
FOR SYSTEM_TIME ALL
WHERE StockItemName LIKE 'An%'
ORDER BY StockItemID, ValidFrom;
This code generates the following output:
StockItemID ValidFrom ValidTo
----------- ---------------------------------- ----------------------------------
134 2012-12-31 16:00:00.0000000 -08:00 2016-05-31 16:00:00.0000000 -07:00
134 2016-05-31 16:00:00.0000000 -07:00 2016-05-31 16:07:00.0000000 -07:00
134 2016-05-31 16:07:00.0000000 -07:00 2016-05-31 16:08:00.0000000 -07:00
134 2016-05-31 16:08:00.0000000 -07:00 9999-12-31 15:59:59.9999999 -08:00
135 2012-12-31 16:00:00.0000000 -08:00 2016-05-31 16:00:00.0000000 -07:00
135 2016-05-31 16:00:00.0000000 -07:00 2016-05-31 16:07:00.0000000 -07:00
135 2016-05-31 16:07:00.0000000 -07:00 2016-05-31 16:08:00.0000000 -07:00
135 2016-05-31 16:08:00.0000000 -07:00 9999-12-31 15:59:59.9999999 -08:00
136 2012-12-31 16:00:00.0000000 -08:00 2016-05-31 16:00:00.0000000 -07:00
136 2016-05-31 16:00:00.0000000 -07:00 2016-05-31 16:07:00.0000000 -07:00
136 2016-05-31 16:07:00.0000000 -07:00 2016-05-31 16:08:00.0000000 -07:00
136 2016-05-31 16:08:00.0000000 -07:00 9999-12-31 15:59:59.9999999 -08:00
137 2012-12-31 16:00:00.0000000 -08:00 2016-05-31 16:00:00.0000000 -07:00
137 2016-05-31 16:00:00.0000000 -07:00 2016-05-31 16:07:00.0000000 -07:00
137 2016-05-31 16:07:00.0000000 -07:00 2016-05-31 16:08:00.0000000 -07:00
137 2016-05-31 16:08:00.0000000 -07:00 9999-12-31 15:59:59.9999999 -08:00
If the target time zone has a positive offset--e.g., Russian Standard Time--the local time of the maximum value in the type is not expressible in SQL Server since it is in the year 10000 and SQL Server can only express dates up to the year 9999. Instead of failing the attempted conversion, SQL Server returns the maximum value in the type with the offset +00:00. The following code demonstrates this:
DECLARE
@targettimezone AS sysname = 'Russian Standard Time';
SELECT StockItemID,
ValidFrom AT TIME ZONE N'UTC' AT TIME ZONE @targettimezone AS ValidFrom,
ValidTo AT TIME ZONE N'UTC' AT TIME ZONE @targettimezone AS ValidTo
FROM Warehouse.StockItems
FOR SYSTEM_TIME ALL
WHERE StockItemName LIKE 'An%'
ORDER BY StockItemID, ValidFrom;
This code generates the following output:
StockItemID ValidFrom ValidTo
----------- ---------------------------------- ----------------------------------
134 2013-01-01 04:00:00.0000000 +04:00 2016-06-01 02:00:00.0000000 +03:00
134 2016-06-01 02:00:00.0000000 +03:00 2016-06-01 02:07:00.0000000 +03:00
134 2016-06-01 02:07:00.0000000 +03:00 2016-06-01 02:08:00.0000000 +03:00
134 2016-06-01 02:08:00.0000000 +03:00 9999-12-31 23:59:59.9999999 +00:00
135 2013-01-01 04:00:00.0000000 +04:00 2016-06-01 02:00:00.0000000 +03:00
135 2016-06-01 02:00:00.0000000 +03:00 2016-06-01 02:07:00.0000000 +03:00
135 2016-06-01 02:07:00.0000000 +03:00 2016-06-01 02:08:00.0000000 +03:00
135 2016-06-01 02:08:00.0000000 +03:00 9999-12-31 23:59:59.9999999 +00:00
136 2013-01-01 04:00:00.0000000 +04:00 2016-06-01 02:00:00.0000000 +03:00
136 2016-06-01 02:00:00.0000000 +03:00 2016-06-01 02:07:00.0000000 +03:00
136 2016-06-01 02:07:00.0000000 +03:00 2016-06-01 02:08:00.0000000 +03:00
136 2016-06-01 02:08:00.0000000 +03:00 9999-12-31 23:59:59.9999999 +00:00
137 2013-01-01 04:00:00.0000000 +04:00 2016-06-01 02:00:00.0000000 +03:00
137 2016-06-01 02:00:00.0000000 +03:00 2016-06-01 02:07:00.0000000 +03:00
137 2016-06-01 02:07:00.0000000 +03:00 2016-06-01 02:08:00.0000000 +03:00
137 2016-06-01 02:08:00.0000000 +03:00 9999-12-31 23:59:59.9999999 +00:00
If you’re happy with this behavior, there’s nothing further that you need to do. However, if you want consistent behavior irrespective of whether the target time zone has a negative or positive offset, you would want to return the maximum value in the type with the offset +00:00. This can be easily achieved with a CASE expression, like so:
DECLARE
@targettimezone AS sysname = 'Russian Standard Time';
SELECT StockItemID,
ValidFrom AT TIME ZONE N'UTC' AT TIME ZONE @targettimezone AS ValidFrom,
ValidTo AT TIME ZONE
CASE WHEN ValidTo = '99991231 23:59:59.9999999'
THEN 'UTC'
ELSE @targettimezone
END AS ValidTo
FROM Warehouse.StockItems
FOR SYSTEM_TIME ALL
WHERE StockItemName LIKE 'An%'
ORDER BY StockItemID, ValidFrom;
This code generates the following output:
StockItemID ValidFrom ValidTo
----------- ---------------------------------- ----------------------------------
134 2013-01-01 04:00:00.0000000 +04:00 2016-05-31 23:00:00.0000000 +03:00
134 2016-06-01 02:00:00.0000000 +03:00 2016-05-31 23:07:00.0000000 +03:00
134 2016-06-01 02:07:00.0000000 +03:00 2016-05-31 23:08:00.0000000 +03:00
134 2016-06-01 02:08:00.0000000 +03:00 9999-12-31 23:59:59.9999999 +00:00
135 2013-01-01 04:00:00.0000000 +04:00 2016-05-31 23:00:00.0000000 +03:00
135 2016-06-01 02:00:00.0000000 +03:00 2016-05-31 23:07:00.0000000 +03:00
135 2016-06-01 02:07:00.0000000 +03:00 2016-05-31 23:08:00.0000000 +03:00
135 2016-06-01 02:08:00.0000000 +03:00 9999-12-31 23:59:59.9999999 +00:00
136 2013-01-01 04:00:00.0000000 +04:00 2016-05-31 23:00:00.0000000 +03:00
136 2016-06-01 02:00:00.0000000 +03:00 2016-05-31 23:07:00.0000000 +03:00
136 2016-06-01 02:07:00.0000000 +03:00 2016-05-31 23:08:00.0000000 +03:00
136 2016-06-01 02:08:00.0000000 +03:00 9999-12-31 23:59:59.9999999 +00:00
137 2013-01-01 04:00:00.0000000 +04:00 2016-05-31 23:00:00.0000000 +03:00
137 2016-06-01 02:00:00.0000000 +03:00 2016-05-31 23:07:00.0000000 +03:00
137 2016-06-01 02:07:00.0000000 +03:00 2016-05-31 23:08:00.0000000 +03:00
137 2016-06-01 02:08:00.0000000 +03:00 9999-12-31 23:59:59.9999999 +00:00
Next, try the code with the time zone Pacific Standard Time:
DECLARE
@targettimezone AS sysname = 'Pacific Standard Time';
SELECT StockItemID,
ValidFrom AT TIME ZONE N'UTC' AT TIME ZONE @targettimezone AS ValidFrom,
ValidTo AT TIME ZONE
CASE WHEN ValidTo = '99991231 23:59:59.9999999'
THEN 'UTC'
ELSE @targettimezone
END AS ValidTo
FROM Warehouse.StockItems
FOR SYSTEM_TIME ALL
WHERE StockItemName LIKE 'An%'
ORDER BY StockItemID, ValidFrom;
You get the following output:
StockItemID ValidFrom ValidTo
----------- ---------------------------------- ----------------------------------
134 2012-12-31 16:00:00.0000000 -08:00 2016-05-31 23:00:00.0000000 -07:00
134 2016-05-31 16:00:00.0000000 -07:00 2016-05-31 23:07:00.0000000 -07:00
134 2016-05-31 16:07:00.0000000 -07:00 2016-05-31 23:08:00.0000000 -07:00
134 2016-05-31 16:08:00.0000000 -07:00 9999-12-31 23:59:59.9999999 +00:00
135 2012-12-31 16:00:00.0000000 -08:00 2016-05-31 23:00:00.0000000 -07:00
135 2016-05-31 16:00:00.0000000 -07:00 2016-05-31 23:07:00.0000000 -07:00
135 2016-05-31 16:07:00.0000000 -07:00 2016-05-31 23:08:00.0000000 -07:00
135 2016-05-31 16:08:00.0000000 -07:00 9999-12-31 23:59:59.9999999 +00:00
136 2012-12-31 16:00:00.0000000 -08:00 2016-05-31 23:00:00.0000000 -07:00
136 2016-05-31 16:00:00.0000000 -07:00 2016-05-31 23:07:00.0000000 -07:00
136 2016-05-31 16:07:00.0000000 -07:00 2016-05-31 23:08:00.0000000 -07:00
136 2016-05-31 16:08:00.0000000 -07:00 9999-12-31 23:59:59.9999999 +00:00
137 2012-12-31 16:00:00.0000000 -08:00 2016-05-31 23:00:00.0000000 -07:00
137 2016-05-31 16:00:00.0000000 -07:00 2016-05-31 23:07:00.0000000 -07:00
137 2016-05-31 16:07:00.0000000 -07:00 2016-05-31 23:08:00.0000000 -07:00
137 2016-05-31 16:08:00.0000000 -07:00 9999-12-31 23:59:59.9999999 +00:00
Personally, this would be my preference.
Degenerate Intervals
When you modify data in a temporal table as part of an explicit transaction, the effective change time is the transaction start time for all changes. This can result in a curious situation when you apply multiple updates to the same row within the same transaction. The original and last versions of the row will have nonzero length intervals, but the in-between versions will have zero length intervals, also known as degenerate intervals. To demonstrate this, I’ll use a temporal table called Products. Run the following code to create the Products table and populate it with a few rows:
-- Create and populate Products table
USE tempdb;
IF OBJECT_ID(N'dbo.Products', N'U') IS NOT NULL
BEGIN
IF OBJECTPROPERTY(OBJECT_ID(N'dbo.Products', N'U'), N'TableTemporalType') = 2
ALTER TABLE dbo.Products SET ( SYSTEM_VERSIONING = OFF );
DROP TABLE IF EXISTS dbo.ProductsHistory, dbo.Products;
END;
GO
CREATE TABLE dbo.Products
(
productid INT NOT NULL
CONSTRAINT PK_dboProducts PRIMARY KEY(productid),
productname NVARCHAR(40) NOT NULL,
supplierid INT NOT NULL,
categoryid INT NOT NULL,
unitprice MONEY NOT NULL,
validfrom DATETIME2
GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
validto DATETIME2
GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
PERIOD FOR SYSTEM_TIME (validfrom, validto)
)
WITH ( SYSTEM_VERSIONING = ON ( HISTORY_TABLE = dbo.ProductsHistory ) );
-- Insert rows at point in time P1
INSERT INTO dbo.Products(productid, productname, supplierid, categoryid, unitprice)
VALUES(1, 'Product HHYDP', 1, 1, 18.00),
(2, 'Product RECZE', 1, 1, 19.00),
(3, 'Product IMEHJ', 1, 2, 10.00),
(4, 'Product KSBRM', 2, 2, 22.00),
(5, 'Product EPEIM', 2, 2, 21.35);
Run the following code to apply a few updates in a single transaction:
BEGIN TRAN; -- point in time P2
WAITFOR DELAY '00:00:01';
-- point in time P3
UPDATE dbo.Products
SET unitprice += 1
WHERE productid = 3;
WAITFOR DELAY '00:00:01';
-- point in time P4
UPDATE dbo.Products
SET unitprice += 1
WHERE productid = 3;
WAITFOR DELAY '00:00:01';
-- point in time P5
UPDATE dbo.Products
SET unitprice += 1
WHERE productid = 3;
COMMIT TRAN;
The oldest version of the row for product 3 should have P1 as the validfrom value and P2 as the validto value, with the price 10.00. The newest, current version should have P2 as the validfrom value and the maximum as the validto value, with the price 13.00. But two more versions were generated with both validfrom and validto values of P2, with prices 11.00 and 12.00. If you query the data with the FOR SYSTEM_TIME clause, SQL Server discards the degenerate intervals. For example, use the following code to return all versions of rows for product 3 using the ALL subclause:
SELECT productid, unitprice, validfrom, validto
FROM dbo.Products
FOR SYSTEM_TIME ALL
WHERE productid = 3
ORDER BY validfrom;
This code generates the following output, excluding the degenerate intervals:
productid unitprice validfrom validto
---------- ---------- -------------------------------- --------------------------------
3 10.00 2016-12-14 06:23:24.9343997 (P1) 2016-12-14 06:23:41.7382783 (P2)
3 13.00 2016-12-14 06:23:41.7382783 (P2) 9999-12-31 23:59:59.9999999
The execution plan for this query is shown in Figure 1.
Figure 1: Degenerate intervals discarded
Observe that the plan includes predicates that discard degenerate intervals from both tables.
To keep degenerate intervals, you need to query the current and history tables directly, and unify the results, like so:
SELECT productid, unitprice, validfrom, validto
FROM dbo.Products
WHERE productid = 3
UNION ALL
SELECT productid, unitprice, validfrom, validto
FROM dbo.ProductsHistory
WHERE productid = 3
ORDER BY validfrom;
This code generates the following output, which includes the degenerate intervals:
productid unitprice validfrom validto
---------- ---------- -------------------------------- --------------------------------
3 10.00 2016-12-14 06:23:24.9343997 (P1) 2016-12-14 06:23:41.7382783 (P2)
3 11.00 2016-12-14 06:23:41.7382783 (P2) 2016-12-14 06:23:41.7382783 (P2)
3 12.00 2016-12-14 06:23:41.7382783 (P2) 2016-12-14 06:23:41.7382783 (P2)
3 13.00 2016-12-14 06:23:41.7382783 (P2) 9999-12-31 23:59:59.9999999
The plan for this query is shown in Figure 2.
Figure 2: Degenerate intervals included
Obviously, the plan doesn’t have any predicates to discard degenerate intervals.
When using the AS OF subclause to return intervals that were valid at a given point in time, by definition all degenerate intervals are discarded. For instance, consider a query such as the following (assuming @datetime is an input parameter to a procedure or function):
SELECT productid, unitprice, validfrom, validto
FROM dbo.Products
FOR SYSTEM_TIME AS OF @datetime;
This query returns rows where @datetime is greater than or equal to validfrom, and less than validto. This means that degenerate intervals can never be considered matches. So here, there’s no point in writing queries directly against the current and history tables with the logically equivalent predicates. However, with the rest of the supported subclauses (FROM TO, BETWEEN and CONTAINED IN), a query with the subclause won’t return degenerate intervals, whereas direct queries against the current and history tables will. The following examples show for each subclause the alternative with the direct queries:
Example with FROM TO, which excludes degenerate intervals:
SELECT productid, unitprice, validfrom, validto
FROM dbo.Products
FOR SYSTEM_TIME FROM @start TO @end;
Alternative with direct queries to include degenerate intervals:
SELECT productid, unitprice, validfrom, validto
FROM dbo.Products
WHERE validfrom < @end
AND validto > @start
UNION ALL
SELECT productid, unitprice, validfrom, validto
FROM dbo.ProductsHistory
WHERE validfrom < @end
AND validto > @start;
Example with BETWEEN, which excludes degenerate intervals:
SELECT productid, unitprice, validfrom, validto
FROM dbo.Products
FOR SYSTEM_TIME BETWEEN @start AND @end;
Alternative with direct queries to include degenerate intervals:
SELECT productid, unitprice, validfrom, validto
FROM dbo.Products
WHERE validfrom <= @end
AND validto > @start
UNION ALL
SELECT productid, unitprice, validfrom, validto
FROM dbo.ProductsHistory
WHERE validfrom <= @end
AND validto > @start;
Example with CONTAINED IN, which excludes degenerate intervals:
SELECT productid, unitprice, validfrom, validto
FROM dbo.Products
FOR SYSTEM_TIME CONTAINED IN (@start, @end);
Alternative with direct queries to include degenerate intervals:
SELECT productid, unitprice, validfrom, validto
FROM dbo.Products
WHERE validfrom >= @start
AND validto <= @end
UNION ALL
SELECT productid, unitprice, validfrom, validto
FROM dbo.ProductsHistory
WHERE validfrom >= @start
AND validto <= @end;
When you’re done, run the following code for cleanup:
IF OBJECT_ID(N'dbo.Products', N'U') IS NOT NULL
BEGIN
IF OBJECTPROPERTY(OBJECT_ID(N'dbo.Products', N'U'), N'TableTemporalType') = 2
ALTER TABLE dbo.Products SET ( SYSTEM_VERSIONING = OFF );
DROP TABLE IF EXISTS dbo.ProductsHistory, dbo.Products;
END;
Optimized CONTAINED IN Subclause
The CONTAINED IN subclause returns all rows that have a validity period that is contained in the input interval--i.e., rows that have a period start that is greater than or equal to the input interval’s start and a period end that is less than or equal to the input interval’s end. SQL Server has a special optimization for the CONTAINED IN subclause where it avoids physically scanning the current table if the input interval’s end is different than the maximum possible value in the type. That’s because it knows that in such a case the rows from the current table cannot be qualifying rows. Therefore, if you need to identify row versions that are contained in an input period, better use the CONTAINED IN subclause and not the ALL subclause with explicit filter predicates. To demonstrate this, first run the following code to enable reporting I/O statistics:
SET STATISTICS IO ON;
Run the following code using the ALL subclause to identify row versions that were contained in the period 2010 through 2016:
USE WideWorldImporters;
SELECT StockItemID, ValidFrom, ValidTo
FROM Warehouse.StockItems
FOR SYSTEM_TIME ALL
WHERE StockItemName LIKE 'An%'
AND ValidFrom >= '20100101 00:00:00.0000000'
AND ValidTo <= '20161231 23:59:59.9999999';
The execution plan for this query is shown in Figure 3.
Figure 03: With ALL both tables are accessed
Notice that both the current and history tables are accessed. (Execution count is 1 for the scans of the indexes on both tables.) Also notice in the output of STATISTICS IO that there are reads reported against both tables:
Table 'StockItems_Archive'. Scan count 1, logical reads 19, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'StockItems'. Scan count 1, logical reads 16, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Next, try achieving the same task using the CONTAINED IN subclause, like so:
SELECT StockItemID, ValidFrom, ValidTo
FROM Warehouse.StockItems
FOR SYSTEM_TIME CONTAINED IN
('20100101 00:00:00.0000000', '20161231 23:59:59.9999999')
WHERE StockItemName LIKE 'An%';
The plan for this query is shown in Figure 4.
Figure 04: With CONTAINED IN only history table is accessed
Observe the Startup Expression Predicate. It checks if the input period end is equal to the maximum possible value in the type; only then it starts up the scan against the current table. In this example, since the predicate is false, the scan isn’t executed. Observe in the output of STATISTICS IO that there’s no I/O reported against the current table:
Table 'StockItems_Archive'. Scan count 1, logical reads 19, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
You may be thinking, "Why not query just the history table directly ,and this way also avoid physically touching the current table?" That’s a possibility. But, first, the point with the FOR SYSTEM_TIME clause is to simplify the logic and remove the need to think about the underlying separation between the current and history tables. Second, what if the inputs are parameters, and the executing user can sometimes request the input interval’s end to be the maximum? Instead of you needing to check the startup condition yourself with an IF statement, the optimizer does it for you. Here’s an example with variables:
DECLARE
@start AS DATETIME2 = '20100101 00:00:00.0000000',
@end AS DATETIME2 = '20161231 23:59:59.9999999';
SELECT StockItemID, ValidFrom, ValidTo
FROM Warehouse.StockItems
FOR SYSTEM_TIME CONTAINED IN (@start, @end)
WHERE StockItemName LIKE 'An%';
The plan for this query is shown in Figure 5.
Figure 05: CONTAINED IN with variables
Observe that even when using variables or parameters, the same startup logic is used. The output of STATISTICS IO also indicates that there was no I/O performed against the current table:
Table 'StockItems_Archive'. Scan count 1, logical reads 19, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
When you’re done, run the following code to turn off I/O statistics reporting:
SET STATISTICS IO OFF;
Conclusion
This article covered a few tips concerning temporal tables. It showed how to get consistent behavior when you want to present the period columns in a desired target time zone by using a CASE expression. It explained what degenerate intervals are and how to return those if you’re interested in them by querying the current and history tables directly. Finally, the article described an optimization used for the CONTAINED IN subclause of the FOR SYSTEM_TIME clause and explained why it is preferred to use this clause compared to alternatives.
