SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE SCHEMA [hist]
CREATE TABLE dbo.People
(
	[ID]					INT	IDENTITY(1,1) PRIMARY KEY CLUSTERED,
	[Name]					VARCHAR(256),
	[VolatileData]			VARCHAR(256),
	[DateOfBirth]			DATETIME2,
	[HistoryEffectiveDate]	DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
	[HistoryExpiredDate]	DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
	PERIOD FOR SYSTEM_TIME (HistoryEffectiveDate, HistoryExpiredDate)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = hist.People))
INSERT INTO dbo.People
(
    [Name],
    [VolatileData],
    [DateOfBirth]
)
VALUES
  ('Robert', CAST(NEWID() AS VARCHAR(255)), '2000-03-11')
, ('John', CAST(NEWID() AS VARCHAR(255)), '1919-01-01')
, ('Sally', CAST(NEWID() AS VARCHAR(255)), '1951-05-02')
, ('Jennifer', CAST(NEWID() AS VARCHAR(255)), '1975-12-18')
, ('Tobias', CAST(NEWID() AS VARCHAR(255)), '1933-07-21')
, ('Dmitri', CAST(NEWID() AS VARCHAR(255)), '1984-06-02')
, ('Seline', CAST(NEWID() AS VARCHAR(255)), '1971-10-14')
, ('Prakash', CAST(NEWID() AS VARCHAR(255)), '1966-02-01')
, ('Barry', CAST(NEWID() AS VARCHAR(255)), '1951-11-11')
, ('Chip', CAST(NEWID() AS VARCHAR(255)), '1999-12-31')
10 rows affected

ALTER TABLE dbo.People SET (SYSTEM_VERSIONING = OFF)
DECLARE histCursor CURSOR
FOR
	SELECT	ID, [Name], DateOfBirth, HistoryEffectiveDate
	FROM	dbo.People

DECLARE @ID INT, @Name VARCHAR(256), @DOB DATETIME2, @HED DATETIME2

-- Inject the data into the cursor
OPEN histCursor
FETCH NEXT FROM histCursor
INTO @ID, @Name, @DOB, @HED

WHILE @@FETCH_STATUS = 0
BEGIN

	;WITH GetDates ([ID], [Date]) AS 
	(
		SELECT 1 AS ID, CAST('01-01-1919' AS DATETIME) AS [Date]
        
		UNION ALL
        
		SELECT ID + 1, DATEADD(DAY, 1, [Date])
		FROM   GetDates
		WHERE  [Date] < GETDATE()-1
	), SampleData AS
	(
		SELECT ID, [Date], CAST(NEWID() AS VARCHAR(255)) AS VolatileData
		FROM   GetDates
	)
	INSERT INTO hist.People ([ID], [Name], [VolatileData], [DateOfBirth], [HistoryEffectiveDate], [HistoryExpiredDate])
	SELECT	@ID, @Name, VolatileData, @DOB, [Date], COALESCE(LEAD([Date]) OVER(ORDER BY [ID]), @HED)
	FROM	SampleData
	WHERE	ISNUMERIC(LEFT(VolatileData, 1)) <> 0
		AND [Date] >= @DOB
	OPTION ( MAXRECURSION 0 )

	FETCH NEXT FROM histCursor
	INTO @ID, @Name, @DOB, @HED
END

CLOSE histCursor
DEALLOCATE histCursor
123154 rows affected

ALTER TABLE dbo.People SET (SYSTEM_VERSIONING = ON 
	(HISTORY_TABLE = hist.People, DATA_CONSISTENCY_CHECK = ON))
-- Create useless Index 1
CREATE INDEX [IX_Live_People__Name] ON dbo.People
(
	[Name]
)
-- Create useless Index 2
CREATE INDEX [IX_Hist_People__Name] ON hist.People
(
	[Name]
)
SET SHOWPLAN_XML ON
-- Query without Hints
SELECT	*
FROM	dbo.People FOR SYSTEM_TIME ALL
WHERE	LEFT(VolatileData, 2) = '2A'
	AND Name = 'John'


Microsoft SQL Server 2005 XML Showplan


<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.481" Build="14.0.3045.24"><BatchSequence><Batch><Statements><StmtSimple StatementText="-- Query without Hints&#xa;SELECT&#x9;*&#xa;FROM&#x9;dbo.People FOR SYSTEM_TIME ALL&#xa;WHERE&#x9;LEFT(VolatileData, 2) = &apos;2A&apos;&#xa;&#x9;AND Name = &apos;John&apos;" StatementId="1" StatementCompId="1" StatementType="SELECT" RetrievedFromCache="false" StatementSubTreeCost="2.02416" StatementEstRows="211.268" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0xDE9050A60EF4DFF8" QueryPlanHash="0xF6C5AD89C5725F67" CardinalityEstimationModelVersion="140"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="32" CompileTime="390" CompileCPU="390" CompileMemory="512"><MissingIndexes><MissingIndexGroup Impact="94.2652"><MissingIndex Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]"><ColumnGroup Usage="EQUALITY"><Column Name="[Name]" ColumnId="2"></Column></ColumnGroup><ColumnGroup Usage="INCLUDE"><Column Name="[ID]" ColumnId="1"></Column><Column Name="[VolatileData]" ColumnId="3"></Column><Column Name="[DateOfBirth]" ColumnId="4"></Column></ColumnGroup></MissingIndex></MissingIndexGroup></MissingIndexes><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="419378" EstimatedPagesCached="26211" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1201208"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2019-05-21T19:30:36.07" ModificationCount="0" SamplingPercent="46.5701" Statistics="[ix_People]" Table="[People]" Schema="[hist]" Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]"></StatisticsInfo><StatisticsInfo LastUpdate="2019-05-21T19:30:36.12" ModificationCount="0" SamplingPercent="46.5701" Statistics="[_WA_Sys_00000005_36B12243]" Table="[People]" Schema="[hist]" Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]"></StatisticsInfo><StatisticsInfo LastUpdate="2019-05-21T19:30:35.71" ModificationCount="0" SamplingPercent="100" Statistics="[IX_Hist_People__Name]" Table="[People]" Schema="[hist]" Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]"></StatisticsInfo><StatisticsInfo LastUpdate="2019-05-21T19:30:35.35" ModificationCount="0" SamplingPercent="100" Statistics="[IX_Live_People__Name]" Table="[People]" Schema="[dbo]" Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]"></StatisticsInfo><StatisticsInfo LastUpdate="2019-05-21T19:30:35.72" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000003_34C8D9D1]" Table="[People]" Schema="[dbo]" Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]"></StatisticsInfo><StatisticsInfo LastUpdate="2019-05-21T19:30:36.01" ModificationCount="0" SamplingPercent="51.3252" Statistics="[_WA_Sys_00000003_36B12243]" Table="[People]" Schema="[hist]" Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]"></StatisticsInfo></OptimizerStatsUsage><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><RelOp NodeId="0" PhysicalOp="Concatenation" LogicalOp="Concatenation" EstimateRows="211.268" EstimateIO="0" EstimateCPU="2.11268e-005" AvgRowSize="82" EstimatedTotalSubtreeCost="2.02416" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Union1005"></ColumnReference><ColumnReference Column="Union1006"></ColumnReference><ColumnReference Column="Union1007"></ColumnReference><ColumnReference Column="Union1008"></ColumnReference><ColumnReference Column="Union1009"></ColumnReference><ColumnReference Column="Union1010"></ColumnReference></OutputList><Concat><DefinedValues><DefinedValue><ColumnReference Column="Union1005"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="ID"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="ID"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Column="Union1006"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="Name"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="Name"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Column="Union1007"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="VolatileData"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="VolatileData"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Column="Union1008"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="DateOfBirth"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="DateOfBirth"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Column="Union1009"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="HistoryEffectiveDate"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="HistoryEffectiveDate"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Column="Union1010"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="HistoryExpiredDate"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="HistoryExpiredDate"></ColumnReference></DefinedValue></DefinedValues><RelOp NodeId="2" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="1" EstimatedRowsRead="10" EstimateIO="0.003125" EstimateCPU="0.000168" AvgRowSize="82" EstimatedTotalSubtreeCost="0.003293" TableCardinality="10" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="ID"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="Name"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="VolatileData"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="DateOfBirth"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="HistoryEffectiveDate"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="HistoryExpiredDate"></ColumnReference></OutputList><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="ID"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="Name"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="VolatileData"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="DateOfBirth"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="HistoryEffectiveDate"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="HistoryExpiredDate"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Index="[PK__People__3214EC276AED428F]" IndexKind="Clustered" Storage="RowStore"></Object><Predicate><ScalarOperator ScalarString="[fiddle_b4c8a28dc813426a991378d2ef707c5f].[dbo].[People].[HistoryEffectiveDate]&lt;&gt;[fiddle_b4c8a28dc813426a991378d2ef707c5f].[dbo].[People].[HistoryExpiredDate] AND [fiddle_b4c8a28dc813426a991378d2ef707c5f].[dbo].[People].[Name]=&apos;John&apos; AND substring([fiddle_b4c8a28dc813426a991378d2ef707c5f].[dbo].[People].[VolatileData],(1),(2))=&apos;2A&apos;"><Logical Operation="AND"><ScalarOperator><Compare CompareOp="NE"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="HistoryEffectiveDate"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="HistoryExpiredDate"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="Name"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="&apos;John&apos;"></Const></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Intrinsic FunctionName="substring"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="VolatileData"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator><ScalarOperator><Const ConstValue="(2)"></Const></ScalarOperator></Intrinsic></ScalarOperator><ScalarOperator><Const ConstValue="&apos;2A&apos;"></Const></ScalarOperator></Compare></ScalarOperator></Logical></ScalarOperator></Predicate></IndexScan></RelOp><RelOp NodeId="4" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="210.268" EstimatedRowsRead="123154" EstimateIO="1.72757" EstimateCPU="0.135626" AvgRowSize="82" EstimatedTotalSubtreeCost="1.8632" TableCardinality="123154" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="ID"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="Name"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="VolatileData"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="DateOfBirth"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="HistoryEffectiveDate"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="HistoryExpiredDate"></ColumnReference></OutputList><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="ID"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="Name"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="VolatileData"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="DateOfBirth"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="HistoryEffectiveDate"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="HistoryExpiredDate"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Index="[ix_People]" IndexKind="Clustered" Storage="RowStore"></Object><Predicate><ScalarOperator ScalarString="[fiddle_b4c8a28dc813426a991378d2ef707c5f].[hist].[People].[HistoryEffectiveDate]&lt;&gt;[fiddle_b4c8a28dc813426a991378d2ef707c5f].[hist].[People].[HistoryExpiredDate] AND [fiddle_b4c8a28dc813426a991378d2ef707c5f].[hist].[People].[Name]=&apos;John&apos; AND substring([fiddle_b4c8a28dc813426a991378d2ef707c5f].[hist].[People].[VolatileData],(1),(2))=&apos;2A&apos;"><Logical Operation="AND"><ScalarOperator><Compare CompareOp="NE"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="HistoryEffectiveDate"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="HistoryExpiredDate"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="Name"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="&apos;John&apos;"></Const></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Intrinsic FunctionName="substring"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="VolatileData"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator><ScalarOperator><Const ConstValue="(2)"></Const></ScalarOperator></Intrinsic></ScalarOperator><ScalarOperator><Const ConstValue="&apos;2A&apos;"></Const></ScalarOperator></Compare></ScalarOperator></Logical></ScalarOperator></Predicate></IndexScan></RelOp></Concat></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>


-- Table Hints work on the Live table
SELECT	*
FROM	dbo.People FOR SYSTEM_TIME ALL WITH (INDEX(IX_Live_People__Name))
WHERE	LEFT(VolatileData, 2) = '2A'
	AND Name = 'John'


Microsoft SQL Server 2005 XML Showplan


<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.481" Build="14.0.3045.24"><BatchSequence><Batch><Statements><StmtSimple StatementText="-- Table Hints work on the Live table&#xa;SELECT&#x9;*&#xa;FROM&#x9;dbo.People FOR SYSTEM_TIME ALL WITH (INDEX(IX_Live_People__Name))&#xa;WHERE&#x9;LEFT(VolatileData, 2) = &apos;2A&apos;&#xa;&#x9;AND Name = &apos;John&apos;" StatementId="1" StatementCompId="1" StatementType="SELECT" RetrievedFromCache="false" StatementSubTreeCost="2.02743" StatementEstRows="211.268" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0xBE7974CB4420E418" QueryPlanHash="0x8657F026A5A9F1DA" CardinalityEstimationModelVersion="140"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="40" CompileTime="0" CompileCPU="0" CompileMemory="520"><MissingIndexes><MissingIndexGroup Impact="94.1133"><MissingIndex Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]"><ColumnGroup Usage="EQUALITY"><Column Name="[Name]" ColumnId="2"></Column></ColumnGroup><ColumnGroup Usage="INCLUDE"><Column Name="[ID]" ColumnId="1"></Column><Column Name="[VolatileData]" ColumnId="3"></Column><Column Name="[DateOfBirth]" ColumnId="4"></Column></ColumnGroup></MissingIndex></MissingIndexGroup></MissingIndexes><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="419378" EstimatedPagesCached="26211" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1201208"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2019-05-21T19:30:36.07" ModificationCount="0" SamplingPercent="46.5701" Statistics="[ix_People]" Table="[People]" Schema="[hist]" Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]"></StatisticsInfo><StatisticsInfo LastUpdate="2019-05-21T19:30:36.12" ModificationCount="0" SamplingPercent="46.5701" Statistics="[_WA_Sys_00000005_36B12243]" Table="[People]" Schema="[hist]" Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]"></StatisticsInfo><StatisticsInfo LastUpdate="2019-05-21T19:30:35.71" ModificationCount="0" SamplingPercent="100" Statistics="[IX_Hist_People__Name]" Table="[People]" Schema="[hist]" Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]"></StatisticsInfo><StatisticsInfo LastUpdate="2019-05-21T19:30:35.35" ModificationCount="0" SamplingPercent="100" Statistics="[IX_Live_People__Name]" Table="[People]" Schema="[dbo]" Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]"></StatisticsInfo><StatisticsInfo LastUpdate="2019-05-21T19:30:35.72" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000003_34C8D9D1]" Table="[People]" Schema="[dbo]" Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]"></StatisticsInfo><StatisticsInfo LastUpdate="2019-05-21T19:30:36.01" ModificationCount="0" SamplingPercent="51.3252" Statistics="[_WA_Sys_00000003_36B12243]" Table="[People]" Schema="[hist]" Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]"></StatisticsInfo></OptimizerStatsUsage><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><RelOp NodeId="0" PhysicalOp="Concatenation" LogicalOp="Concatenation" EstimateRows="211.268" EstimateIO="0" EstimateCPU="2.11268e-005" AvgRowSize="82" EstimatedTotalSubtreeCost="2.02743" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Union1005"></ColumnReference><ColumnReference Column="Union1006"></ColumnReference><ColumnReference Column="Union1007"></ColumnReference><ColumnReference Column="Union1008"></ColumnReference><ColumnReference Column="Union1009"></ColumnReference><ColumnReference Column="Union1010"></ColumnReference></OutputList><Concat><DefinedValues><DefinedValue><ColumnReference Column="Union1005"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="ID"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="ID"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Column="Union1006"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="Name"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="Name"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Column="Union1007"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="VolatileData"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="VolatileData"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Column="Union1008"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="DateOfBirth"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="DateOfBirth"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Column="Union1009"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="HistoryEffectiveDate"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="HistoryEffectiveDate"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Column="Union1010"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="HistoryExpiredDate"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="HistoryExpiredDate"></ColumnReference></DefinedValue></DefinedValues><RelOp NodeId="2" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="1" EstimateIO="0" EstimateCPU="4.18e-006" AvgRowSize="82" EstimatedTotalSubtreeCost="0.00657038" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="ID"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="Name"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="VolatileData"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="DateOfBirth"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="HistoryEffectiveDate"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="HistoryExpiredDate"></ColumnReference></OutputList><NestedLoops Optimized="0"><OuterReferences><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="ID"></ColumnReference></OuterReferences><RelOp NodeId="3" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="20" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="10" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="ID"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="Name"></ColumnReference></OutputList><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="1" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="ID"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="Name"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Index="[IX_Live_People__Name]" IndexKind="NonClustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="Name"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="&apos;John&apos;"><Const ConstValue="&apos;John&apos;"></Const></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp><RelOp NodeId="5" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="163" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="10" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="VolatileData"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="DateOfBirth"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="HistoryEffectiveDate"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="HistoryExpiredDate"></ColumnReference></OutputList><IndexScan Lookup="1" Ordered="1" ScanDirection="FORWARD" ForcedIndex="1" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="VolatileData"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="DateOfBirth"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="HistoryEffectiveDate"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="HistoryExpiredDate"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Index="[PK__People__3214EC276AED428F]" TableReferenceId="-1" IndexKind="Clustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="ID"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[fiddle_b4c8a28dc813426a991378d2ef707c5f].[dbo].[People].[ID]"><Identifier><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="ID"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates><Predicate><ScalarOperator ScalarString="substring([fiddle_b4c8a28dc813426a991378d2ef707c5f].[dbo].[People].[VolatileData],(1),(2))=&apos;2A&apos; AND ([fiddle_b4c8a28dc813426a991378d2ef707c5f].[dbo].[People].[HistoryEffectiveDate]&lt;[fiddle_b4c8a28dc813426a991378d2ef707c5f].[dbo].[People].[HistoryExpiredDate] OR [fiddle_b4c8a28dc813426a991378d2ef707c5f].[dbo].[People].[HistoryEffectiveDate]&gt;[fiddle_b4c8a28dc813426a991378d2ef707c5f].[dbo].[People].[HistoryExpiredDate])"><Logical Operation="AND"><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Intrinsic FunctionName="substring"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="VolatileData"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator><ScalarOperator><Const ConstValue="(2)"></Const></ScalarOperator></Intrinsic></ScalarOperator><ScalarOperator><Const ConstValue="&apos;2A&apos;"></Const></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Logical Operation="OR"><ScalarOperator><Compare CompareOp="LT"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="HistoryEffectiveDate"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="HistoryExpiredDate"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="GT"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="HistoryEffectiveDate"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="HistoryExpiredDate"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Logical></ScalarOperator></Logical></ScalarOperator></Predicate></IndexScan></RelOp></NestedLoops></RelOp><RelOp NodeId="7" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="210.268" EstimatedRowsRead="123154" EstimateIO="1.72757" EstimateCPU="0.135626" AvgRowSize="82" EstimatedTotalSubtreeCost="1.8632" TableCardinality="123154" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="ID"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="Name"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="VolatileData"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="DateOfBirth"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="HistoryEffectiveDate"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="HistoryExpiredDate"></ColumnReference></OutputList><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="ID"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="Name"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="VolatileData"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="DateOfBirth"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="HistoryEffectiveDate"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="HistoryExpiredDate"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Index="[ix_People]" IndexKind="Clustered" Storage="RowStore"></Object><Predicate><ScalarOperator ScalarString="[fiddle_b4c8a28dc813426a991378d2ef707c5f].[hist].[People].[HistoryEffectiveDate]&lt;&gt;[fiddle_b4c8a28dc813426a991378d2ef707c5f].[hist].[People].[HistoryExpiredDate] AND [fiddle_b4c8a28dc813426a991378d2ef707c5f].[hist].[People].[Name]=&apos;John&apos; AND substring([fiddle_b4c8a28dc813426a991378d2ef707c5f].[hist].[People].[VolatileData],(1),(2))=&apos;2A&apos;"><Logical Operation="AND"><ScalarOperator><Compare CompareOp="NE"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="HistoryEffectiveDate"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="HistoryExpiredDate"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="Name"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="&apos;John&apos;"></Const></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Intrinsic FunctionName="substring"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="VolatileData"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator><ScalarOperator><Const ConstValue="(2)"></Const></ScalarOperator></Intrinsic></ScalarOperator><ScalarOperator><Const ConstValue="&apos;2A&apos;"></Const></ScalarOperator></Compare></ScalarOperator></Logical></ScalarOperator></Predicate></IndexScan></RelOp></Concat></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>


-- Query Hints work on the Live table
SELECT	*
FROM	dbo.People FOR SYSTEM_TIME ALL
WHERE	LEFT(VolatileData, 2) = '2A'
	AND Name = 'John'
OPTION(TABLE HINT (dbo.People, INDEX(IX_Live_People__Name)))


Microsoft SQL Server 2005 XML Showplan


<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.481" Build="14.0.3045.24"><BatchSequence><Batch><Statements><StmtSimple StatementText="-- Query Hints work on the Live table&#xa;SELECT&#x9;*&#xa;FROM&#x9;dbo.People FOR SYSTEM_TIME ALL&#xa;WHERE&#x9;LEFT(VolatileData, 2) = &apos;2A&apos;&#xa;&#x9;AND Name = &apos;John&apos;&#xa;OPTION(TABLE HINT (dbo.People, INDEX(IX_Live_People__Name)))" StatementId="1" StatementCompId="1" StatementType="SELECT" RetrievedFromCache="false" StatementSubTreeCost="2.02743" StatementEstRows="211.268" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x0CB2D1DE0C92D469" QueryPlanHash="0x8657F026A5A9F1DA" CardinalityEstimationModelVersion="140"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="40" CompileTime="0" CompileCPU="0" CompileMemory="520"><MissingIndexes><MissingIndexGroup Impact="94.1133"><MissingIndex Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]"><ColumnGroup Usage="EQUALITY"><Column Name="[Name]" ColumnId="2"></Column></ColumnGroup><ColumnGroup Usage="INCLUDE"><Column Name="[ID]" ColumnId="1"></Column><Column Name="[VolatileData]" ColumnId="3"></Column><Column Name="[DateOfBirth]" ColumnId="4"></Column></ColumnGroup></MissingIndex></MissingIndexGroup></MissingIndexes><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="419378" EstimatedPagesCached="26211" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1201208"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2019-05-21T19:30:36.07" ModificationCount="0" SamplingPercent="46.5701" Statistics="[ix_People]" Table="[People]" Schema="[hist]" Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]"></StatisticsInfo><StatisticsInfo LastUpdate="2019-05-21T19:30:36.12" ModificationCount="0" SamplingPercent="46.5701" Statistics="[_WA_Sys_00000005_36B12243]" Table="[People]" Schema="[hist]" Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]"></StatisticsInfo><StatisticsInfo LastUpdate="2019-05-21T19:30:35.71" ModificationCount="0" SamplingPercent="100" Statistics="[IX_Hist_People__Name]" Table="[People]" Schema="[hist]" Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]"></StatisticsInfo><StatisticsInfo LastUpdate="2019-05-21T19:30:35.35" ModificationCount="0" SamplingPercent="100" Statistics="[IX_Live_People__Name]" Table="[People]" Schema="[dbo]" Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]"></StatisticsInfo><StatisticsInfo LastUpdate="2019-05-21T19:30:35.72" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000003_34C8D9D1]" Table="[People]" Schema="[dbo]" Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]"></StatisticsInfo><StatisticsInfo LastUpdate="2019-05-21T19:30:36.01" ModificationCount="0" SamplingPercent="51.3252" Statistics="[_WA_Sys_00000003_36B12243]" Table="[People]" Schema="[hist]" Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]"></StatisticsInfo></OptimizerStatsUsage><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><RelOp NodeId="0" PhysicalOp="Concatenation" LogicalOp="Concatenation" EstimateRows="211.268" EstimateIO="0" EstimateCPU="2.11268e-005" AvgRowSize="82" EstimatedTotalSubtreeCost="2.02743" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Union1005"></ColumnReference><ColumnReference Column="Union1006"></ColumnReference><ColumnReference Column="Union1007"></ColumnReference><ColumnReference Column="Union1008"></ColumnReference><ColumnReference Column="Union1009"></ColumnReference><ColumnReference Column="Union1010"></ColumnReference></OutputList><Concat><DefinedValues><DefinedValue><ColumnReference Column="Union1005"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="ID"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="ID"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Column="Union1006"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="Name"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="Name"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Column="Union1007"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="VolatileData"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="VolatileData"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Column="Union1008"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="DateOfBirth"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="DateOfBirth"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Column="Union1009"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="HistoryEffectiveDate"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="HistoryEffectiveDate"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Column="Union1010"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="HistoryExpiredDate"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="HistoryExpiredDate"></ColumnReference></DefinedValue></DefinedValues><RelOp NodeId="2" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="1" EstimateIO="0" EstimateCPU="4.18e-006" AvgRowSize="82" EstimatedTotalSubtreeCost="0.00657038" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="ID"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="Name"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="VolatileData"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="DateOfBirth"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="HistoryEffectiveDate"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="HistoryExpiredDate"></ColumnReference></OutputList><NestedLoops Optimized="0"><OuterReferences><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="ID"></ColumnReference></OuterReferences><RelOp NodeId="3" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="20" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="10" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="ID"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="Name"></ColumnReference></OutputList><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="1" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="ID"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="Name"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Index="[IX_Live_People__Name]" IndexKind="NonClustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="Name"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="&apos;John&apos;"><Const ConstValue="&apos;John&apos;"></Const></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp><RelOp NodeId="5" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="163" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="10" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="VolatileData"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="DateOfBirth"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="HistoryEffectiveDate"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="HistoryExpiredDate"></ColumnReference></OutputList><IndexScan Lookup="1" Ordered="1" ScanDirection="FORWARD" ForcedIndex="1" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="VolatileData"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="DateOfBirth"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="HistoryEffectiveDate"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="HistoryExpiredDate"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Index="[PK__People__3214EC276AED428F]" TableReferenceId="-1" IndexKind="Clustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="ID"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[fiddle_b4c8a28dc813426a991378d2ef707c5f].[dbo].[People].[ID]"><Identifier><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="ID"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates><Predicate><ScalarOperator ScalarString="substring([fiddle_b4c8a28dc813426a991378d2ef707c5f].[dbo].[People].[VolatileData],(1),(2))=&apos;2A&apos; AND ([fiddle_b4c8a28dc813426a991378d2ef707c5f].[dbo].[People].[HistoryEffectiveDate]&lt;[fiddle_b4c8a28dc813426a991378d2ef707c5f].[dbo].[People].[HistoryExpiredDate] OR [fiddle_b4c8a28dc813426a991378d2ef707c5f].[dbo].[People].[HistoryEffectiveDate]&gt;[fiddle_b4c8a28dc813426a991378d2ef707c5f].[dbo].[People].[HistoryExpiredDate])"><Logical Operation="AND"><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Intrinsic FunctionName="substring"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="VolatileData"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator><ScalarOperator><Const ConstValue="(2)"></Const></ScalarOperator></Intrinsic></ScalarOperator><ScalarOperator><Const ConstValue="&apos;2A&apos;"></Const></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Logical Operation="OR"><ScalarOperator><Compare CompareOp="LT"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="HistoryEffectiveDate"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="HistoryExpiredDate"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="GT"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="HistoryEffectiveDate"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="HistoryExpiredDate"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Logical></ScalarOperator></Logical></ScalarOperator></Predicate></IndexScan></RelOp></NestedLoops></RelOp><RelOp NodeId="7" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="210.268" EstimatedRowsRead="123154" EstimateIO="1.72757" EstimateCPU="0.135626" AvgRowSize="82" EstimatedTotalSubtreeCost="1.8632" TableCardinality="123154" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="ID"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="Name"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="VolatileData"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="DateOfBirth"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="HistoryEffectiveDate"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="HistoryExpiredDate"></ColumnReference></OutputList><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="ID"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="Name"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="VolatileData"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="DateOfBirth"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="HistoryEffectiveDate"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="HistoryExpiredDate"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Index="[ix_People]" IndexKind="Clustered" Storage="RowStore"></Object><Predicate><ScalarOperator ScalarString="[fiddle_b4c8a28dc813426a991378d2ef707c5f].[hist].[People].[HistoryEffectiveDate]&lt;&gt;[fiddle_b4c8a28dc813426a991378d2ef707c5f].[hist].[People].[HistoryExpiredDate] AND [fiddle_b4c8a28dc813426a991378d2ef707c5f].[hist].[People].[Name]=&apos;John&apos; AND substring([fiddle_b4c8a28dc813426a991378d2ef707c5f].[hist].[People].[VolatileData],(1),(2))=&apos;2A&apos;"><Logical Operation="AND"><ScalarOperator><Compare CompareOp="NE"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="HistoryEffectiveDate"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="HistoryExpiredDate"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="Name"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="&apos;John&apos;"></Const></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Intrinsic FunctionName="substring"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="VolatileData"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator><ScalarOperator><Const ConstValue="(2)"></Const></ScalarOperator></Intrinsic></ScalarOperator><ScalarOperator><Const ConstValue="&apos;2A&apos;"></Const></ScalarOperator></Compare></ScalarOperator></Logical></ScalarOperator></Predicate></IndexScan></RelOp></Concat></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>


-- Table Hints don't work on the History Table
SELECT	*
FROM	dbo.People FOR SYSTEM_TIME ALL WITH (INDEX(IX_Hist_People__Name))
WHERE	LEFT(VolatileData, 2) = '2A'
	AND Name = 'John'
Msg 308 Level 16 State 1 Line 2
Index 'IX_Hist_People__Name' on table 'dbo.People' (specified in the FROM clause) does not exist.

-- Query Hints don't work on the History Table
SELECT	*
FROM	dbo.People FOR SYSTEM_TIME ALL
WHERE	LEFT(VolatileData, 2) = '2A'
	AND Name = 'John'
OPTION(TABLE HINT (hist.People, INDEX(IX_Hist_People__Name)))
Msg 8723 Level 16 State 1 Line 2
Cannot execute query. Object 'hist.People' is specified in the TABLE HINT clause, but is not used in the query or does not match the alias specified in the query. Table references in the TABLE HINT clause must match the WITH clause.

-- UNION ALL instead of FOR SYSTEM_TIME clause - Table Hints
SELECT	*
FROM	dbo.People WITH (INDEX(IX_Live_People__Name))
WHERE	LEFT(VolatileData, 2) = '2A'
	AND Name = 'John'

UNION ALL

SELECT	*
FROM	hist.People WITH (INDEX (IX_Hist_People__Name))
WHERE	LEFT(VolatileData, 2) = '2A'
	AND Name = 'John'


Microsoft SQL Server 2005 XML Showplan


<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.481" Build="14.0.3045.24"><BatchSequence><Batch><Statements><StmtSimple StatementText="-- UNION ALL instead of FOR SYSTEM_TIME clause - Table Hints&#xa;SELECT&#x9;*&#xa;FROM&#x9;dbo.People WITH (INDEX(IX_Live_People__Name))&#xa;WHERE&#x9;LEFT(VolatileData, 2) = &apos;2A&apos;&#xa;&#x9;AND Name = &apos;John&apos;&#xa;&#xa;UNION ALL&#xa;&#xa;SELECT&#x9;*&#xa;FROM&#x9;hist.People WITH (INDEX (IX_Hist_People__Name))&#xa;WHERE&#x9;LEFT(VolatileData, 2) = &apos;2A&apos;&#xa;&#x9;AND Name = &apos;John&apos;" StatementId="1" StatementCompId="1" StatementType="SELECT" RetrievedFromCache="false" StatementSubTreeCost="11.1209" StatementEstRows="216.881" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x6183CCB027BEF83C" QueryPlanHash="0x8F2307750B089B36" CardinalityEstimationModelVersion="140"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="56" CompileTime="15" CompileCPU="15" CompileMemory="472"><MissingIndexes><MissingIndexGroup Impact="98.9268"><MissingIndex Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]"><ColumnGroup Usage="EQUALITY"><Column Name="[Name]" ColumnId="2"></Column></ColumnGroup><ColumnGroup Usage="INCLUDE"><Column Name="[ID]" ColumnId="1"></Column><Column Name="[VolatileData]" ColumnId="3"></Column><Column Name="[DateOfBirth]" ColumnId="4"></Column></ColumnGroup></MissingIndex></MissingIndexGroup></MissingIndexes><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="419378" EstimatedPagesCached="26211" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1201208"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2019-05-21T19:30:36.07" ModificationCount="0" SamplingPercent="46.5701" Statistics="[ix_People]" Table="[People]" Schema="[hist]" Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]"></StatisticsInfo><StatisticsInfo LastUpdate="2019-05-21T19:30:36.12" ModificationCount="0" SamplingPercent="46.5701" Statistics="[_WA_Sys_00000005_36B12243]" Table="[People]" Schema="[hist]" Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]"></StatisticsInfo><StatisticsInfo LastUpdate="2019-05-21T19:30:35.71" ModificationCount="0" SamplingPercent="100" Statistics="[IX_Hist_People__Name]" Table="[People]" Schema="[hist]" Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]"></StatisticsInfo><StatisticsInfo LastUpdate="2019-05-21T19:30:35.35" ModificationCount="0" SamplingPercent="100" Statistics="[IX_Live_People__Name]" Table="[People]" Schema="[dbo]" Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]"></StatisticsInfo><StatisticsInfo LastUpdate="2019-05-21T19:30:35.72" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000003_34C8D9D1]" Table="[People]" Schema="[dbo]" Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]"></StatisticsInfo><StatisticsInfo LastUpdate="2019-05-21T19:30:36.01" ModificationCount="0" SamplingPercent="51.3252" Statistics="[_WA_Sys_00000003_36B12243]" Table="[People]" Schema="[hist]" Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]"></StatisticsInfo></OptimizerStatsUsage><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><RelOp NodeId="0" PhysicalOp="Concatenation" LogicalOp="Concatenation" EstimateRows="216.881" EstimateIO="0" EstimateCPU="2.16881e-005" AvgRowSize="82" EstimatedTotalSubtreeCost="11.1209" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Union1005"></ColumnReference><ColumnReference Column="Union1006"></ColumnReference><ColumnReference Column="Union1007"></ColumnReference><ColumnReference Column="Union1008"></ColumnReference><ColumnReference Column="Union1009"></ColumnReference><ColumnReference Column="Union1010"></ColumnReference></OutputList><Concat><DefinedValues><DefinedValue><ColumnReference Column="Union1005"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="ID"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="ID"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Column="Union1006"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="Name"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="Name"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Column="Union1007"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="VolatileData"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="VolatileData"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Column="Union1008"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="DateOfBirth"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="DateOfBirth"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Column="Union1009"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="HistoryEffectiveDate"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="HistoryEffectiveDate"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Column="Union1010"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="HistoryExpiredDate"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="HistoryExpiredDate"></ColumnReference></DefinedValue></DefinedValues><RelOp NodeId="2" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="1" EstimateIO="0" EstimateCPU="4.18e-006" AvgRowSize="82" EstimatedTotalSubtreeCost="0.00657038" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="ID"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="Name"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="VolatileData"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="DateOfBirth"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="HistoryEffectiveDate"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="HistoryExpiredDate"></ColumnReference></OutputList><NestedLoops Optimized="0"><OuterReferences><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="ID"></ColumnReference></OuterReferences><RelOp NodeId="3" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="20" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="10" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="ID"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="Name"></ColumnReference></OutputList><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="1" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="ID"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="Name"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Index="[IX_Live_People__Name]" IndexKind="NonClustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="Name"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="&apos;John&apos;"><Const ConstValue="&apos;John&apos;"></Const></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp><RelOp NodeId="5" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="163" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="10" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="VolatileData"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="DateOfBirth"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="HistoryEffectiveDate"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="HistoryExpiredDate"></ColumnReference></OutputList><IndexScan Lookup="1" Ordered="1" ScanDirection="FORWARD" ForcedIndex="1" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="VolatileData"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="DateOfBirth"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="HistoryEffectiveDate"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="HistoryExpiredDate"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Index="[PK__People__3214EC276AED428F]" TableReferenceId="-1" IndexKind="Clustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="ID"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[fiddle_b4c8a28dc813426a991378d2ef707c5f].[dbo].[People].[ID]"><Identifier><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="ID"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates><Predicate><ScalarOperator ScalarString="substring([fiddle_b4c8a28dc813426a991378d2ef707c5f].[dbo].[People].[VolatileData],(1),(2))=&apos;2A&apos;"><Compare CompareOp="EQ"><ScalarOperator><Intrinsic FunctionName="substring"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="VolatileData"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator><ScalarOperator><Const ConstValue="(2)"></Const></ScalarOperator></Intrinsic></ScalarOperator><ScalarOperator><Const ConstValue="&apos;2A&apos;"></Const></ScalarOperator></Compare></ScalarOperator></Predicate></IndexScan></RelOp></NestedLoops></RelOp><RelOp NodeId="7" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="22914" EstimateIO="0" EstimateCPU="0.0957805" AvgRowSize="82" EstimatedTotalSubtreeCost="11.0965" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="ID"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="Name"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="VolatileData"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="DateOfBirth"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="HistoryEffectiveDate"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="HistoryExpiredDate"></ColumnReference></OutputList><NestedLoops Optimized="0" WithUnorderedPrefetch="1"><OuterReferences><ColumnReference Column="Uniq1003"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="HistoryEffectiveDate"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="HistoryExpiredDate"></ColumnReference><ColumnReference Column="Expr1013"></ColumnReference></OuterReferences><RelOp NodeId="9" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="22914" EstimatedRowsRead="22914" EstimateIO="0.0748786" EstimateCPU="0.0253624" AvgRowSize="36" EstimatedTotalSubtreeCost="0.100241" TableCardinality="123154" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Uniq1003"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="Name"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="HistoryEffectiveDate"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="HistoryExpiredDate"></ColumnReference></OutputList><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="1" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Column="Uniq1003"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="Name"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="HistoryEffectiveDate"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="HistoryExpiredDate"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Index="[IX_Hist_People__Name]" IndexKind="NonClustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="Name"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="&apos;John&apos;"><Const ConstValue="&apos;John&apos;"></Const></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp><RelOp NodeId="11" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="215.881" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="151" EstimatedTotalSubtreeCost="10.9004" TableCardinality="123154" Parallel="0" EstimateRebinds="22913" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="ID"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="VolatileData"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="DateOfBirth"></ColumnReference></OutputList><IndexScan Lookup="1" Ordered="1" ScanDirection="FORWARD" ForcedIndex="1" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="ID"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="VolatileData"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="DateOfBirth"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Index="[ix_People]" TableReferenceId="-1" IndexKind="Clustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="HistoryExpiredDate"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="HistoryEffectiveDate"></ColumnReference><ColumnReference Column="Uniq1003"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[fiddle_b4c8a28dc813426a991378d2ef707c5f].[hist].[People].[HistoryExpiredDate]"><Identifier><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="HistoryExpiredDate"></ColumnReference></Identifier></ScalarOperator><ScalarOperator ScalarString="[fiddle_b4c8a28dc813426a991378d2ef707c5f].[hist].[People].[HistoryEffectiveDate]"><Identifier><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="HistoryEffectiveDate"></ColumnReference></Identifier></ScalarOperator><ScalarOperator ScalarString="[Uniq1003]"><Identifier><ColumnReference Column="Uniq1003"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates><Predicate><ScalarOperator ScalarString="substring([fiddle_b4c8a28dc813426a991378d2ef707c5f].[hist].[People].[VolatileData],(1),(2))=&apos;2A&apos;"><Compare CompareOp="EQ"><ScalarOperator><Intrinsic FunctionName="substring"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="VolatileData"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator><ScalarOperator><Const ConstValue="(2)"></Const></ScalarOperator></Intrinsic></ScalarOperator><ScalarOperator><Const ConstValue="&apos;2A&apos;"></Const></ScalarOperator></Compare></ScalarOperator></Predicate></IndexScan></RelOp></NestedLoops></RelOp></Concat></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>


-- UNION ALL instead of FOR SYSTEM_TIME clause - Query Hints
SELECT	*
FROM	dbo.People 
WHERE	LEFT(VolatileData, 2) = '2A'
	AND Name = 'John'

UNION ALL

SELECT	*
FROM	hist.People
WHERE	LEFT(VolatileData, 2) = '2A'
	AND Name = 'John'
OPTION(TABLE HINT (hist.People, INDEX(IX_Hist_People__Name)), TABLE HINT(dbo.People, INDEX(IX_Live_People__Name)))


Microsoft SQL Server 2005 XML Showplan


<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.481" Build="14.0.3045.24"><BatchSequence><Batch><Statements><StmtSimple StatementText="-- UNION ALL instead of FOR SYSTEM_TIME clause - Query Hints&#xa;SELECT&#x9;*&#xa;FROM&#x9;dbo.People &#xa;WHERE&#x9;LEFT(VolatileData, 2) = &apos;2A&apos;&#xa;&#x9;AND Name = &apos;John&apos;&#xa;&#xa;UNION ALL&#xa;&#xa;SELECT&#x9;*&#xa;FROM&#x9;hist.People&#xa;WHERE&#x9;LEFT(VolatileData, 2) = &apos;2A&apos;&#xa;&#x9;AND Name = &apos;John&apos;&#xa;OPTION(TABLE HINT (hist.People, INDEX(IX_Hist_People__Name)), TABLE HINT(dbo.People, INDEX(IX_Live_People__Name)))" StatementId="1" StatementCompId="1" StatementType="SELECT" RetrievedFromCache="false" StatementSubTreeCost="11.1209" StatementEstRows="216.881" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x1500407BA6A5D078" QueryPlanHash="0x8F2307750B089B36" CardinalityEstimationModelVersion="140"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="56" CompileTime="0" CompileCPU="0" CompileMemory="480"><MissingIndexes><MissingIndexGroup Impact="98.9268"><MissingIndex Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]"><ColumnGroup Usage="EQUALITY"><Column Name="[Name]" ColumnId="2"></Column></ColumnGroup><ColumnGroup Usage="INCLUDE"><Column Name="[ID]" ColumnId="1"></Column><Column Name="[VolatileData]" ColumnId="3"></Column><Column Name="[DateOfBirth]" ColumnId="4"></Column></ColumnGroup></MissingIndex></MissingIndexGroup></MissingIndexes><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="419378" EstimatedPagesCached="26211" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1201208"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2019-05-21T19:30:36.07" ModificationCount="0" SamplingPercent="46.5701" Statistics="[ix_People]" Table="[People]" Schema="[hist]" Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]"></StatisticsInfo><StatisticsInfo LastUpdate="2019-05-21T19:30:36.12" ModificationCount="0" SamplingPercent="46.5701" Statistics="[_WA_Sys_00000005_36B12243]" Table="[People]" Schema="[hist]" Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]"></StatisticsInfo><StatisticsInfo LastUpdate="2019-05-21T19:30:35.71" ModificationCount="0" SamplingPercent="100" Statistics="[IX_Hist_People__Name]" Table="[People]" Schema="[hist]" Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]"></StatisticsInfo><StatisticsInfo LastUpdate="2019-05-21T19:30:35.35" ModificationCount="0" SamplingPercent="100" Statistics="[IX_Live_People__Name]" Table="[People]" Schema="[dbo]" Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]"></StatisticsInfo><StatisticsInfo LastUpdate="2019-05-21T19:30:35.72" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000003_34C8D9D1]" Table="[People]" Schema="[dbo]" Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]"></StatisticsInfo><StatisticsInfo LastUpdate="2019-05-21T19:30:36.01" ModificationCount="0" SamplingPercent="51.3252" Statistics="[_WA_Sys_00000003_36B12243]" Table="[People]" Schema="[hist]" Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]"></StatisticsInfo></OptimizerStatsUsage><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><RelOp NodeId="0" PhysicalOp="Concatenation" LogicalOp="Concatenation" EstimateRows="216.881" EstimateIO="0" EstimateCPU="2.16881e-005" AvgRowSize="82" EstimatedTotalSubtreeCost="11.1209" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Union1005"></ColumnReference><ColumnReference Column="Union1006"></ColumnReference><ColumnReference Column="Union1007"></ColumnReference><ColumnReference Column="Union1008"></ColumnReference><ColumnReference Column="Union1009"></ColumnReference><ColumnReference Column="Union1010"></ColumnReference></OutputList><Concat><DefinedValues><DefinedValue><ColumnReference Column="Union1005"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="ID"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="ID"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Column="Union1006"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="Name"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="Name"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Column="Union1007"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="VolatileData"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="VolatileData"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Column="Union1008"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="DateOfBirth"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="DateOfBirth"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Column="Union1009"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="HistoryEffectiveDate"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="HistoryEffectiveDate"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Column="Union1010"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="HistoryExpiredDate"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="HistoryExpiredDate"></ColumnReference></DefinedValue></DefinedValues><RelOp NodeId="2" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="1" EstimateIO="0" EstimateCPU="4.18e-006" AvgRowSize="82" EstimatedTotalSubtreeCost="0.00657038" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="ID"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="Name"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="VolatileData"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="DateOfBirth"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="HistoryEffectiveDate"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="HistoryExpiredDate"></ColumnReference></OutputList><NestedLoops Optimized="0"><OuterReferences><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="ID"></ColumnReference></OuterReferences><RelOp NodeId="3" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="20" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="10" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="ID"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="Name"></ColumnReference></OutputList><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="1" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="ID"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="Name"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Index="[IX_Live_People__Name]" IndexKind="NonClustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="Name"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="&apos;John&apos;"><Const ConstValue="&apos;John&apos;"></Const></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp><RelOp NodeId="5" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="163" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="10" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="VolatileData"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="DateOfBirth"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="HistoryEffectiveDate"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="HistoryExpiredDate"></ColumnReference></OutputList><IndexScan Lookup="1" Ordered="1" ScanDirection="FORWARD" ForcedIndex="1" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="VolatileData"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="DateOfBirth"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="HistoryEffectiveDate"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="HistoryExpiredDate"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Index="[PK__People__3214EC276AED428F]" TableReferenceId="-1" IndexKind="Clustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="ID"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[fiddle_b4c8a28dc813426a991378d2ef707c5f].[dbo].[People].[ID]"><Identifier><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="ID"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates><Predicate><ScalarOperator ScalarString="substring([fiddle_b4c8a28dc813426a991378d2ef707c5f].[dbo].[People].[VolatileData],(1),(2))=&apos;2A&apos;"><Compare CompareOp="EQ"><ScalarOperator><Intrinsic FunctionName="substring"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[dbo]" Table="[People]" Column="VolatileData"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator><ScalarOperator><Const ConstValue="(2)"></Const></ScalarOperator></Intrinsic></ScalarOperator><ScalarOperator><Const ConstValue="&apos;2A&apos;"></Const></ScalarOperator></Compare></ScalarOperator></Predicate></IndexScan></RelOp></NestedLoops></RelOp><RelOp NodeId="7" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="22914" EstimateIO="0" EstimateCPU="0.0957805" AvgRowSize="82" EstimatedTotalSubtreeCost="11.0965" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="ID"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="Name"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="VolatileData"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="DateOfBirth"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="HistoryEffectiveDate"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="HistoryExpiredDate"></ColumnReference></OutputList><NestedLoops Optimized="0" WithUnorderedPrefetch="1"><OuterReferences><ColumnReference Column="Uniq1003"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="HistoryEffectiveDate"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="HistoryExpiredDate"></ColumnReference><ColumnReference Column="Expr1013"></ColumnReference></OuterReferences><RelOp NodeId="9" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="22914" EstimatedRowsRead="22914" EstimateIO="0.0748786" EstimateCPU="0.0253624" AvgRowSize="36" EstimatedTotalSubtreeCost="0.100241" TableCardinality="123154" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Uniq1003"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="Name"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="HistoryEffectiveDate"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="HistoryExpiredDate"></ColumnReference></OutputList><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="1" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Column="Uniq1003"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="Name"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="HistoryEffectiveDate"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="HistoryExpiredDate"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Index="[IX_Hist_People__Name]" IndexKind="NonClustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="Name"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="&apos;John&apos;"><Const ConstValue="&apos;John&apos;"></Const></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp><RelOp NodeId="11" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="215.881" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="151" EstimatedTotalSubtreeCost="10.9004" TableCardinality="123154" Parallel="0" EstimateRebinds="22913" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="ID"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="VolatileData"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="DateOfBirth"></ColumnReference></OutputList><IndexScan Lookup="1" Ordered="1" ScanDirection="FORWARD" ForcedIndex="1" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="ID"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="VolatileData"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="DateOfBirth"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Index="[ix_People]" TableReferenceId="-1" IndexKind="Clustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="HistoryExpiredDate"></ColumnReference><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="HistoryEffectiveDate"></ColumnReference><ColumnReference Column="Uniq1003"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[fiddle_b4c8a28dc813426a991378d2ef707c5f].[hist].[People].[HistoryExpiredDate]"><Identifier><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="HistoryExpiredDate"></ColumnReference></Identifier></ScalarOperator><ScalarOperator ScalarString="[fiddle_b4c8a28dc813426a991378d2ef707c5f].[hist].[People].[HistoryEffectiveDate]"><Identifier><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="HistoryEffectiveDate"></ColumnReference></Identifier></ScalarOperator><ScalarOperator ScalarString="[Uniq1003]"><Identifier><ColumnReference Column="Uniq1003"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates><Predicate><ScalarOperator ScalarString="substring([fiddle_b4c8a28dc813426a991378d2ef707c5f].[hist].[People].[VolatileData],(1),(2))=&apos;2A&apos;"><Compare CompareOp="EQ"><ScalarOperator><Intrinsic FunctionName="substring"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_b4c8a28dc813426a991378d2ef707c5f]" Schema="[hist]" Table="[People]" Column="VolatileData"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator><ScalarOperator><Const ConstValue="(2)"></Const></ScalarOperator></Intrinsic></ScalarOperator><ScalarOperator><Const ConstValue="&apos;2A&apos;"></Const></ScalarOperator></Compare></ScalarOperator></Predicate></IndexScan></RelOp></NestedLoops></RelOp></Concat></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>