Tips Concerning Temporal Tables

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:WWIWideWorldImporters-Full.bak'  WITH    FILE = 1,    MOVE N'WWI_Primary'     TO N'C:WWIWideWorldImporters.mdf',  MOVE N'WWI_UserData'    TO N'C:WWIWideWorldImporters_UserData.ndf',  MOVE N'WWI_Log'         TO N'C:WWIWideWorldImporters.ldf',  MOVE N'WWI_InMemory_Data_1' TO N'C:WWIWideWorldImporters_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, ValidToFROM Warehouse.StockItems  FOR SYSTEM_TIME ALLWHERE 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.0000000134     2016-05-31 23:00:00.0000000 2016-05-31 23:07:00.0000000134     2016-05-31 23:07:00.0000000 2016-05-31 23:08:00.0000000134     2016-05-31 23:08:00.0000000 9999-12-31 23:59:59.9999999135     2013-01-01 00:00:00.0000000 2016-05-31 23:00:00.0000000135     2016-05-31 23:00:00.0000000 2016-05-31 23:07:00.0000000135     2016-05-31 23:07:00.0000000 2016-05-31 23:08:00.0000000135     2016-05-31 23:08:00.0000000 9999-12-31 23:59:59.9999999136     2013-01-01 00:00:00.0000000 2016-05-31 23:00:00.0000000136     2016-05-31 23:00:00.0000000 2016-05-31 23:07:00.0000000136     2016-05-31 23:07:00.0000000 2016-05-31 23:08:00.0000000136     2016-05-31 23:08:00.0000000 9999-12-31 23:59:59.9999999137     2013-01-01 00:00:00.0000000 2016-05-31 23:00:00.0000000137     2016-05-31 23:00:00.0000000 2016-05-31 23:07:00.0000000137     2016-05-31 23:07:00.0000000 2016-05-31 23:08:00.0000000137     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 ValidToFROM Warehouse.StockItems  FOR SYSTEM_TIME ALLWHERE 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:00134     2016-05-31 16:00:00.0000000 -07:00 2016-05-31 16:07:00.0000000 -07:00134     2016-05-31 16:07:00.0000000 -07:00 2016-05-31 16:08:00.0000000 -07:00134     2016-05-31 16:08:00.0000000 -07:00 9999-12-31 15:59:59.9999999 -08:00135     2012-12-31 16:00:00.0000000 -08:00 2016-05-31 16:00:00.0000000 -07:00135     2016-05-31 16:00:00.0000000 -07:00 2016-05-31 16:07:00.0000000 -07:00135     2016-05-31 16:07:00.0000000 -07:00 2016-05-31 16:08:00.0000000 -07:00135     2016-05-31 16:08:00.0000000 -07:00 9999-12-31 15:59:59.9999999 -08:00136     2012-12-31 16:00:00.0000000 -08:00 2016-05-31 16:00:00.0000000 -07:00136     2016-05-31 16:00:00.0000000 -07:00 2016-05-31 16:07:00.0000000 -07:00136     2016-05-31 16:07:00.0000000 -07:00 2016-05-31 16:08:00.0000000 -07:00136     2016-05-31 16:08:00.0000000 -07:00 9999-12-31 15:59:59.9999999 -08:00137     2012-12-31 16:00:00.0000000 -08:00 2016-05-31 16:00:00.0000000 -07:00137     2016-05-31 16:00:00.0000000 -07:00 2016-05-31 16:07:00.0000000 -07:00137     2016-05-31 16:07:00.0000000 -07:00 2016-05-31 16:08:00.0000000 -07:00137     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 ValidToFROM Warehouse.StockItems  FOR SYSTEM_TIME ALLWHERE 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:00134     2016-06-01 02:00:00.0000000 +03:00 2016-06-01 02:07:00.0000000 +03:00134     2016-06-01 02:07:00.0000000 +03:00 2016-06-01 02:08:00.0000000 +03:00134     2016-06-01 02:08:00.0000000 +03:00 9999-12-31 23:59:59.9999999 +00:00135     2013-01-01 04:00:00.0000000 +04:00 2016-06-01 02:00:00.0000000 +03:00135     2016-06-01 02:00:00.0000000 +03:00 2016-06-01 02:07:00.0000000 +03:00135     2016-06-01 02:07:00.0000000 +03:00 2016-06-01 02:08:00.0000000 +03:00135     2016-06-01 02:08:00.0000000 +03:00 9999-12-31 23:59:59.9999999 +00:00136     2013-01-01 04:00:00.0000000 +04:00 2016-06-01 02:00:00.0000000 +03:00136     2016-06-01 02:00:00.0000000 +03:00 2016-06-01 02:07:00.0000000 +03:00136     2016-06-01 02:07:00.0000000 +03:00 2016-06-01 02:08:00.0000000 +03:00136     2016-06-01 02:08:00.0000000 +03:00 9999-12-31 23:59:59.9999999 +00:00137     2013-01-01 04:00:00.0000000 +04:00 2016-06-01 02:00:00.0000000 +03:00137     2016-06-01 02:00:00.0000000 +03:00 2016-06-01 02:07:00.0000000 +03:00137     2016-06-01 02:07:00.0000000 +03:00 2016-06-01 02:08:00.0000000 +03:00137     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 ValidToFROM Warehouse.StockItems  FOR SYSTEM_TIME ALLWHERE 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:00134     2016-06-01 02:00:00.0000000 +03:00 2016-05-31 23:07:00.0000000 +03:00134     2016-06-01 02:07:00.0000000 +03:00 2016-05-31 23:08:00.0000000 +03:00134     2016-06-01 02:08:00.0000000 +03:00 9999-12-31 23:59:59.9999999 +00:00135     2013-01-01 04:00:00.0000000 +04:00 2016-05-31 23:00:00.0000000 +03:00135     2016-06-01 02:00:00.0000000 +03:00 2016-05-31 23:07:00.0000000 +03:00135     2016-06-01 02:07:00.0000000 +03:00 2016-05-31 23:08:00.0000000 +03:00135     2016-06-01 02:08:00.0000000 +03:00 9999-12-31 23:59:59.9999999 +00:00136     2013-01-01 04:00:00.0000000 +04:00 2016-05-31 23:00:00.0000000 +03:00136     2016-06-01 02:00:00.0000000 +03:00 2016-05-31 23:07:00.0000000 +03:00136     2016-06-01 02:07:00.0000000 +03:00 2016-05-31 23:08:00.0000000 +03:00136     2016-06-01 02:08:00.0000000 +03:00 9999-12-31 23:59:59.9999999 +00:00137     2013-01-01 04:00:00.0000000 +04:00 2016-05-31 23:00:00.0000000 +03:00137     2016-06-01 02:00:00.0000000 +03:00 2016-05-31 23:07:00.0000000 +03:00137     2016-06-01 02:07:00.0000000 +03:00 2016-05-31 23:08:00.0000000 +03:00137     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 ValidToFROM Warehouse.StockItems  FOR SYSTEM_TIME ALLWHERE 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:00134     2016-05-31 16:00:00.0000000 -07:00 2016-05-31 23:07:00.0000000 -07:00134     2016-05-31 16:07:00.0000000 -07:00 2016-05-31 23:08:00.0000000 -07:00134     2016-05-31 16:08:00.0000000 -07:00 9999-12-31 23:59:59.9999999 +00:00135     2012-12-31 16:00:00.0000000 -08:00 2016-05-31 23:00:00.0000000 -07:00135     2016-05-31 16:00:00.0000000 -07:00 2016-05-31 23:07:00.0000000 -07:00135     2016-05-31 16:07:00.0000000 -07:00 2016-05-31 23:08:00.0000000 -07:00135     2016-05-31 16:08:00.0000000 -07:00 9999-12-31 23:59:59.9999999 +00:00136     2012-12-31 16:00:00.0000000 -08:00 2016-05-31 23:00:00.0000000 -07:00136     2016-05-31 16:00:00.0000000 -07:00 2016-05-31 23:07:00.0000000 -07:00136     2016-05-31 16:07:00.0000000 -07:00 2016-05-31 23:08:00.0000000 -07:00136     2016-05-31 16:08:00.0000000 -07:00 9999-12-31 23:59:59.9999999 +00:00137     2012-12-31 16:00:00.0000000 -08:00 2016-05-31 23:00:00.0000000 -07:00137     2016-05-31 16:00:00.0000000 -07:00 2016-05-31 23:07:00.0000000 -07:00137     2016-05-31 16:07:00.0000000 -07:00 2016-05-31 23:08:00.0000000 -07:00137     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 tableUSE tempdb;IF OBJECT_ID(N'dbo.Products', N'U') IS NOT NULLBEGIN  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;GOCREATE 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 P1INSERT 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 P2WAITFOR DELAY '00:00:01';-- point in time P3UPDATE dbo.Products  SET unitprice += 1WHERE productid = 3;WAITFOR DELAY '00:00:01';-- point in time P4UPDATE dbo.Products  SET unitprice += 1WHERE productid = 3;WAITFOR DELAY '00:00:01';-- point in time P5UPDATE dbo.Products  SET unitprice += 1WHERE 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, validtoFROM dbo.Products  FOR SYSTEM_TIME ALLWHERE productid = 3ORDER 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, validtoFROM dbo.ProductsWHERE productid = 3UNION ALLSELECT productid, unitprice, validfrom, validtoFROM dbo.ProductsHistoryWHERE productid = 3ORDER 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, validtoFROM 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, validtoFROM dbo.Products  FOR SYSTEM_TIME FROM @start TO @end;

Alternative with direct queries to include degenerate intervals:

SELECT productid, unitprice, validfrom, validtoFROM dbo.ProductsWHERE validfrom < @end  AND validto > @start  UNION ALLSELECT productid, unitprice, validfrom, validtoFROM dbo.ProductsHistoryWHERE validfrom < @end  AND validto > @start;

Example with BETWEEN, which excludes degenerate intervals:

SELECT productid, unitprice, validfrom, validtoFROM dbo.Products  FOR SYSTEM_TIME BETWEEN @start AND @end;

Alternative with direct queries to include degenerate intervals:

SELECT productid, unitprice, validfrom, validtoFROM dbo.ProductsWHERE validfrom <= @end  AND validto > @start  UNION ALLSELECT productid, unitprice, validfrom, validtoFROM dbo.ProductsHistoryWHERE validfrom <= @end  AND validto > @start;

Example with CONTAINED IN, which excludes degenerate intervals:

SELECT productid, unitprice, validfrom, validtoFROM dbo.Products  FOR SYSTEM_TIME CONTAINED IN (@start, @end);

Alternative with direct queries to include degenerate intervals:

SELECT productid, unitprice, validfrom, validtoFROM dbo.ProductsWHERE validfrom >= @start  AND validto <= @end  UNION ALLSELECT productid, unitprice, validfrom, validtoFROM dbo.ProductsHistoryWHERE 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 NULLBEGIN  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, ValidToFROM Warehouse.StockItems  FOR SYSTEM_TIME ALLWHERE 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, ValidToFROM 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, ValidToFROM 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.