Why we need to optimize CONTAINED IN clause of FOR_SYSTEM TIME using check constraint?
Why in the documentation is said a optimization is working for CONTAINED IN clause, but actually you need to create such check constraint to use it?


The optimization is really occurring - even though the main table index scan appears in the execution plan, it is never executed because it's behind a filter with a startup expression predicate.

See below for a more detailed explanation.


  All clauses can return rows from both tables and one of them. Why this optimization is working only for the CONTAINED IN clause?


I don't think anyone but the SQL Server product team could answer that one, unfortunately.



The execution plan is a bit misleading here.  If I create a temporal table:

CREATE TABLE dbo.ThisIsJustATest
(    
  [Id] int NOT NULL PRIMARY KEY CLUSTERED IDENTITY(1,1)
  , [Junk] nvarchar(2048) NOT NULL 
  , [ValidFrom] datetime2 (2) GENERATED ALWAYS AS ROW START  
  , [ValidTo] datetime2 (2) GENERATED ALWAYS AS ROW END  
  , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)  
 )    
 WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ThisIsJustATestHistory));


And then load it with data:

INSERT INTO dbo.ThisIsJustATest (Junk)
SELECT m.[text] FROM sys.messages m
OPTION (MAXDOP 1);


And then update some rows (so that I have history rows to query):

UPDATE dbo.ThisIsJustATest
SET Junk = 'Busted'
WHERE Id % 100 = 0;


And run this query (that only returns history rows) with stats I/O on:

SET STATISTICS IO ON;

SELECT * 
FROM dbo.ThisIsJustATest FOR SYSTEM_TIME CONTAINED IN ('2018-09-11 14:17:00.96', '2018-09-11 14:18:51.35');


Here's what I get:

Table 'ThisIsJustATest'. 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.
Table 'ThisIsJustATestHistory'. Scan count 5, logical reads 154, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


So according to stats I/O, it really did zero reads from the base table - it only read from the history table.  Here's the execution plan.  

Notice that all the predicates on the base table's index scan operator are residual predicates.  So in theory, the whole table should get read (even though there are no matching rows).  So there is some trickery going on on.

It doesn't read anything from the base table because the predicate in the filter operator must always evaluate to false:

'2018-09-11 14:18:51.35'=sysmaxdatetime((2))


Note that the predicate is a Startup Expression, so the Clustered Index Scan below it does not get started when the expression evaluates to false. sysmaxdatetime is an intrinsic (internal) function that presumably returns the maximum date/time value at the given precision.

The "Number of Executions" on the Clustered Index Scan is 4, but this is simply an internal quirk related to the way parallel plans work - a thread does call Open() on the subtree to ensure all necessary threads get created and initialized, but no real work is done. In a serial version of the same plan, the number of executions would indeed be zero.

What about the CHECK constraint?

For what's worth, it appears that adding the check constraint is still useful as a performance tuning option, as it reduces logical reads on the history table by quite a bit.

Before the constraint:

Table 'ThisIsJustATest'. Scan count 0, logical reads 0
Table 'ThisIsJustATestHistory'. Scan count 5, logical reads 160

 SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 197 ms.


After the constraint:

Table 'ThisIsJustATestHistory'. Scan count 1, logical reads 53

 SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 197 ms.