3

I was going through Temporal tables new features in SQL 2016,one of the examples was to query tables with as of clause

which returned below query plan

Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions 1 1 select * from Employee for system_time as of '2016-02-07 15:39:02.10' 1 1 0 NULL NULL NULL NULL 2 NULL NULL NULL 0.0065729 NULL NULL SELECT 0 NULL 1 1 |--Concatenation 1 2 1 Concatenation Concatenation NULL [Union1005] = ([sql2016].[dbo].[Employee].[EmployeeID], [sql2016].[dbo].[MSSQL_TemporalHistoryFor_565577053].[EmployeeID]), [Union1006] = ([sql2016].[dbo].[Employee].[Name], [sql2016].[dbo].[MSSQL_TemporalHistoryFor_565577053].[Name]), [Union1007] = ([sql2016].[dbo].[Employee].[Position], [sql2016].[dbo].[MSSQL_TemporalHistoryFor_565577053].[Position]), [Union1008] = ([sql2016].[dbo].[Employee].[Department], [sql2016].[dbo].[MSSQL_TemporalHistoryFor_565577053].[Department]), [Union1009] = ([sql2016].[dbo].[Employee].[Address], [sql2016].[dbo].[MSSQL_TemporalHistoryFor_565577053].[Address]), [Union1010] = ([sql2016].[dbo].[Employee].[AnnualSalary], [sql2016].[dbo].[MSSQL_TemporalHistoryFor_565577053].[AnnualSalary]), [Union1011] = ([sql2016].[dbo].[Employee].[ValidFrom], [sql2016].[dbo].[MSSQL_TemporalHistoryFor_565577053].[ValidFrom]), [Union1012] = ([sql2016].[dbo].[Employee].[ValidTo], [sql2016].[dbo].[MSSQL_TemporalHistoryFor_565577053].[ValidTo]) 2 0 2E-07 1266 0.0065729 [Union1005], [Union1006], [Union1007], [Union1008], [Union1009], [Union1010], [Union1011], [Union1012] NULL PLAN_ROW 0 1 0 1 |--Clustered Index Scan(OBJECT:([sql2016].[dbo].[Employee].[PK__Employee__7AD04FF1C19A16B5]), WHERE:([sql2016].[dbo].[Employee].[ValidFrom]<='2016-02-07 15:39:02.1000000' AND [sql2016].[dbo].[Employee].[ValidTo]>'2016-02-07 15:39:02.1000000')) 1 3 2 Clustered Index Scan Clustered Index Scan OBJECT:([sql2016].[dbo].[Employee].[PK__Employee__7AD04FF1C19A16B5]), WHERE:([sql2016].[dbo].[Employee].[ValidFrom]<='2016-02-07 15:39:02.1000000' AND [sql2016].[dbo].[Employee].[ValidTo]>'2016-02-07 15:39:02.1000000') [sql2016].[dbo].[Employee].[EmployeeID], [sql2016].[dbo].[Employee].[Name], [sql2016].[dbo].[Employee].[Position], [sql2016].[dbo].[Employee].[Department], [sql2016].[dbo].[Employee].[Address], [sql2016].[dbo].[Employee].[AnnualSalary], [sql2016].[dbo].[Employee].[ValidFrom], [sql2016].[dbo].[Employee].[ValidTo] 1 0.003125 0.0001592 1266 0.0032842 [sql2016].[dbo].[Employee].[EmployeeID], [sql2016].[dbo].[Employee].[Name], [sql2016].[dbo].[Employee].[Position], [sql2016].[dbo].[Employee].[Department], [sql2016].[dbo].[Employee].[Address], [sql2016].[dbo].[Employee].[AnnualSalary], [sql2016].[dbo].[Employee].[ValidFrom], [sql2016].[dbo].[Employee].[ValidTo] NULL PLAN_ROW 0 1 1 1 |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1014], [Expr1015], [Expr1013])) 1 4 2 Nested Loops Inner Join OUTER REFERENCES:([Expr1014], [Expr1015], [Expr1013]) NULL 1 0.003125 0.0001603 1266 0.0032853 [sql2016].[dbo].[MSSQL_TemporalHistoryFor_565577053].[EmployeeID], [sql2016].[dbo].[MSSQL_TemporalHistoryFor_565577053].[Name], [sql2016].[dbo].[MSSQL_TemporalHistoryFor_565577053].[Position], [sql2016].[dbo].[MSSQL_TemporalHistoryFor_565577053].[Department], [sql2016].[dbo].[MSSQL_TemporalHistoryFor_565577053].[Address], [sql2016].[dbo].[MSSQL_TemporalHistoryFor_565577053].[AnnualSalary], [sql2016].[dbo].[MSSQL_TemporalHistoryFor_565577053].[ValidFrom], [sql2016].[dbo].[MSSQL_TemporalHistoryFor_565577053].[ValidTo] NULL PLAN_ROW 0 1 1 1 |--Compute Scalar(DEFINE:(([Expr1014],[Expr1015],[Expr1013])=GetRangeWithMismatchedTypes('2016-02-07 15:39:02.1000000',NULL,(6)))) 1 5 4 Compute Scalar Compute Scalar DEFINE:(([Expr1014],[Expr1015],[Expr1013])=GetRangeWithMismatchedTypes('2016-02-07 15:39:02.1000000',NULL,(6))) ([Expr1014],[Expr1015],[Expr1013])=GetRangeWithMismatchedTypes('2016-02-07 15:39:02.1000000',NULL,(6)) 1 0 0 23 0 [Expr1014], [Expr1015], [Expr1013] NULL PLAN_ROW 0 1 1 1 | |--Constant Scan 1 6 5 Constant Scan Constant Scan NULL NULL 1 0 0 0 0 NULL NULL PLAN_ROW 0 1 1 1 |--Clustered Index Seek(OBJECT:([sql2016].[dbo].[MSSQL_TemporalHistoryFor_565577053].[ix_MSSQL_TemporalHistoryFor_565577053]), SEEK:([sql2016].[dbo].[MSSQL_TemporalHistoryFor_565577053].[ValidTo] > [Expr1014] AND [sql2016].[dbo].[MSSQL_TemporalHistoryFor_565577053].[ValidTo] < [Expr1015]), WHERE:([sql2016].[dbo].[MSSQL_TemporalHistoryFor_565577053].[ValidFrom]<='2016-02-07 15:39:02.1000000') ORDERED FORWARD) 1 10 4 Clustered Index Seek Clustered Index Seek OBJECT:([sql2016].[dbo].[MSSQL_TemporalHistoryFor_565577053].[ix_MSSQL_TemporalHistoryFor_565577053]), SEEK:([sql2016].[dbo].[MSSQL_TemporalHistoryFor_565577053].[ValidTo] > [Expr1014] AND [sql2016].[dbo].[MSSQL_TemporalHistoryFor_565577053].[ValidTo] < [Expr1015]), WHERE:([sql2016].[dbo].[MSSQL_TemporalHistoryFor_565577053].[ValidFrom]<='2016-02-07 15:39:02.1000000') ORDERED FORWARD [sql2016].[dbo].[MSSQL_TemporalHistoryFor_565577053].[EmployeeID], [sql2016].[dbo].[MSSQL_TemporalHistoryFor_565577053].[Name], [sql2016].[dbo].[MSSQL_TemporalHistoryFor_565577053].[Position], [sql2016].[dbo].[MSSQL_TemporalHistoryFor_565577053].[Department], [sql2016].[dbo].[MSSQL_TemporalHistoryFor_565577053].[Address], [sql2016].[dbo].[MSSQL_TemporalHistoryFor_565577053].[AnnualSalary], [sql2016].[dbo].[MSSQL_TemporalHistoryFor_565577053].[ValidFrom], [sql2016].[dbo].[MSSQL_TemporalHistoryFor_565577053].[ValidTo] 1 0.003125 0.0001603 1266 0.0032853 [sql2016].[dbo].[MSSQL_TemporalHistoryFor_565577053].[EmployeeID], [sql2016].[dbo].[MSSQL_TemporalHistoryFor_565577053].[Name], [sql2016].[dbo].[MSSQL_TemporalHistoryFor_565577053].[Position], [sql2016].[dbo].[MSSQL_TemporalHistoryFor_565577053].[Department], [sql2016].[dbo].[MSSQL_TemporalHistoryFor_565577053].[Address], [sql2016].[dbo].[MSSQL_TemporalHistoryFor_565577053].[AnnualSalary], [sql2016].[dbo].[MSSQL_TemporalHistoryFor_565577053].[ValidFrom], [sql2016].[dbo].[MSSQL_TemporalHistoryFor_565577053].[ValidTo] NULL PLAN_ROW 0 1

Here is my query:

select * from Employee for system_time as of '2016-02-07 15:39:02.10'

I am not able to understand what below values contain

1 1 |--Compute Scalar(DEFINE:(([Expr1014],[Expr1015],[Expr1013])=GetRangeWithMismatchedTypes('2016-02-07 15:39:02.1000000',NULL,(6)))) 1 5 4 Compute Scalar Compute Scalar DEFINE:(([Expr1014],[Expr1015],[Expr1013])=GetRangeWithMismatchedTypes('2016-02-07 15:39:02.1000000',NULL,(6))) ([Expr1014],[Expr1015],[Expr1013])=GetRangeWithMismatchedTypes('2016-02-07 15:39:02.1000000',NULL,(6)) 1 0 0 23 0 [Expr1014], [Expr1015], [Expr1013] NULL PLAN_ROW 0 1

Can you guys please help me understand what does scalar expression contains,below is the entire xml plan FYR

 PhysicalOp="Concatenation" EstimatedTotalSubtreeCost="0.0065729"> <OutputList> <ColumnReference Column="Union1005" /> <ColumnReference Column="Union1006" /> <ColumnReference Column="Union1007" /> <ColumnReference Column="Union1008" /> <ColumnReference Column="Union1009" /> <ColumnReference Column="Union1010" /> <ColumnReference Column="Union1011" /> <ColumnReference Column="Union1012" /> </OutputList> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" /> </RunTimeInformation> <Concat> <DefinedValues> <DefinedValue> <ColumnReference Column="Union1005" /> <ColumnReference Database="[sql2016]" Schema="[dbo]" Table="[Employee]" Column="EmployeeID" /> <ColumnReference Database="[sql2016]" Schema="[dbo]" Table="[MSSQL_TemporalHistoryFor_565577053]" Column="EmployeeID" /> </DefinedValue> <DefinedValue> <ColumnReference Column="Union1006" /> <ColumnReference Database="[sql2016]" Schema="[dbo]" Table="[Employee]" Column="Name" /> <ColumnReference Database="[sql2016]" Schema="[dbo]" Table="[MSSQL_TemporalHistoryFor_565577053]" Column="Name" /> </DefinedValue> <DefinedValue> <ColumnReference Column="Union1007" /> <ColumnReference Database="[sql2016]" Schema="[dbo]" Table="[Employee]" Column="Position" /> <ColumnReference Database="[sql2016]" Schema="[dbo]" Table="[MSSQL_TemporalHistoryFor_565577053]" Column="Position" /> </DefinedValue> <DefinedValue> <ColumnReference Column="Union1008" /> <ColumnReference Database="[sql2016]" Schema="[dbo]" Table="[Employee]" Column="Department" /> <ColumnReference Database="[sql2016]" Schema="[dbo]" Table="[MSSQL_TemporalHistoryFor_565577053]" Column="Department" /> </DefinedValue> <DefinedValue> <ColumnReference Column="Union1009" /> <ColumnReference Database="[sql2016]" Schema="[dbo]" Table="[Employee]" Column="Address" /> <ColumnReference Database="[sql2016]" Schema="[dbo]" Table="[MSSQL_TemporalHistoryFor_565577053]" Column="Address" /> </DefinedValue> <DefinedValue> <ColumnReference Column="Union1010" /> <ColumnReference Database="[sql2016]" Schema="[dbo]" Table="[Employee]" Column="AnnualSalary" /> <ColumnReference Database="[sql2016]" Schema="[dbo]" Table="[MSSQL_TemporalHistoryFor_565577053]" Column="AnnualSalary" /> </DefinedValue> <DefinedValue> <ColumnReference Column="Union1011" /> <ColumnReference Database="[sql2016]" Schema="[dbo]" Table="[Employee]" Column="ValidFrom" /> <ColumnReference Database="[sql2016]" Schema="[dbo]" Table="[MSSQL_TemporalHistoryFor_565577053]" Column="ValidFrom" /> </DefinedValue> <DefinedValue> <ColumnReference Column="Union1012" /> <ColumnReference Database="[sql2016]" Schema="[dbo]" Table="[Employee]" Column="ValidTo" /> <ColumnReference Database="[sql2016]" Schema="[dbo]" Table="[MSSQL_TemporalHistoryFor_565577053]" Column="ValidTo" /> </DefinedValue> </DefinedValues> <RelOp AvgRowSize="1266" EstimateCPU="0.0001592" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Scan" NodeId="1" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0032842" TableCardinality="2"> <OutputList> <ColumnReference Database="[sql2016]" Schema="[dbo]" Table="[Employee]" Column="EmployeeID" /> <ColumnReference Database="[sql2016]" Schema="[dbo]" Table="[Employee]" Column="Name" /> <ColumnReference Database="[sql2016]" Schema="[dbo]" Table="[Employee]" Column="Position" /> <ColumnReference Database="[sql2016]" Schema="[dbo]" Table="[Employee]" Column="Department" /> <ColumnReference Database="[sql2016]" Schema="[dbo]" Table="[Employee]" Column="Address" /> <ColumnReference Database="[sql2016]" Schema="[dbo]" Table="[Employee]" Column="AnnualSalary" /> <ColumnReference Database="[sql2016]" Schema="[dbo]" Table="[Employee]" Column="ValidFrom" /> <ColumnReference Database="[sql2016]" Schema="[dbo]" Table="[Employee]" Column="ValidTo" /> </OutputList> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualRowsRead="2" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" /> </RunTimeInformation> <IndexScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore"> <DefinedValues> <DefinedValue> <ColumnReference Database="[sql2016]" Schema="[dbo]" Table="[Employee]" Column="EmployeeID" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[sql2016]" Schema="[dbo]" Table="[Employee]" Column="Name" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[sql2016]" Schema="[dbo]" Table="[Employee]" Column="Position" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[sql2016]" Schema="[dbo]" Table="[Employee]" Column="Department" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[sql2016]" Schema="[dbo]" Table="[Employee]" Column="Address" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[sql2016]" Schema="[dbo]" Table="[Employee]" Column="AnnualSalary" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[sql2016]" Schema="[dbo]" Table="[Employee]" Column="ValidFrom" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[sql2016]" Schema="[dbo]" Table="[Employee]" Column="ValidTo" /> </DefinedValue> </DefinedValues> <Object Database="[sql2016]" Schema="[dbo]" Table="[Employee]" Index="[PK__Employee__7AD04FF1C19A16B5]" IndexKind="Clustered" Storage="RowStore" /> <Predicate> <ScalarOperator ScalarString="[sql2016].[dbo].[Employee].[ValidFrom]&lt;='2016-02-07 15:39:02.1000000' AND [sql2016].[dbo].[Employee].[ValidTo]&gt;'2016-02-07 15:39:02.1000000'"> <Logical Operation="AND"> <ScalarOperator> <Compare CompareOp="LE"> <ScalarOperator> <Identifier> <ColumnReference Database="[sql2016]" Schema="[dbo]" Table="[Employee]" Column="ValidFrom" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="'2016-02-07 15:39:02.1000000'" /> </ScalarOperator> </Compare> </ScalarOperator> <ScalarOperator> <Compare CompareOp="GT"> <ScalarOperator> <Identifier> <ColumnReference Database="[sql2016]" Schema="[dbo]" Table="[Employee]" Column="ValidTo" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="'2016-02-07 15:39:02.1000000'" /> </ScalarOperator> </Compare> </ScalarOperator> </Logical> </ScalarOperator> </Predicate> </IndexScan> </RelOp> <RelOp AvgRowSize="1266" EstimateCPU="0.0001603" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Inner Join" NodeId="2" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0032853" TableCardinality="3"> <OutputList> <ColumnReference Database="[sql2016]" Schema="[dbo]" Table="[MSSQL_TemporalHistoryFor_565577053]" Column="EmployeeID" /> <ColumnReference Database="[sql2016]" Schema="[dbo]" Table="[MSSQL_TemporalHistoryFor_565577053]" Column="Name" /> <ColumnReference Database="[sql2016]" Schema="[dbo]" Table="[MSSQL_TemporalHistoryFor_565577053]" Column="Position" /> <ColumnReference Database="[sql2016]" Schema="[dbo]" Table="[MSSQL_TemporalHistoryFor_565577053]" Column="Department" /> <ColumnReference Database="[sql2016]" Schema="[dbo]" Table="[MSSQL_TemporalHistoryFor_565577053]" Column="Address" /> <ColumnReference Database="[sql2016]" Schema="[dbo]" Table="[MSSQL_TemporalHistoryFor_565577053]" Column="AnnualSalary" /> <ColumnReference Database="[sql2016]" Schema="[dbo]" Table="[MSSQL_TemporalHistoryFor_565577053]" Column="ValidFrom" /> <ColumnReference Database="[sql2016]" Schema="[dbo]" Table="[MSSQL_TemporalHistoryFor_565577053]" Column="ValidTo" /> </OutputList> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" /> </RunTimeInformation> <NestedLoops Optimized="false"> <OuterReferences> <ColumnReference Column="Expr1014" /> <ColumnReference Column="Expr1015" /> <ColumnReference Column="Expr1013" /> </OuterReferences> <RelOp AvgRowSize="23" EstimateCPU="0" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="3" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0"> <OutputList> <ColumnReference Column="Expr1014" /> <ColumnReference Column="Expr1015" /> <ColumnReference Column="Expr1013" /> </OutputList> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" /> </RunTimeInformation> <ComputeScalar> <DefinedValues> <DefinedValue> <ValueVector> <ColumnReference Column="Expr1014" /> <ColumnReference Column="Expr1015" /> <ColumnReference Column="Expr1013" /> </ValueVector> <ScalarOperator ScalarString="GetRangeWithMismatchedTypes('2016-02-07 15:39:02.1000000',NULL,(6))"> <Intrinsic FunctionName="GetRangeWithMismatchedTypes"> <ScalarOperator> <Const ConstValue="'2016-02-07 15:39:02.1000000'" /> </ScalarOperator> <ScalarOperator> <Const ConstValue="NULL" /> </ScalarOperator> <ScalarOperator> <Const ConstValue="(6)" /> </ScalarOperator> </Intrinsic> </ScalarOperator> </DefinedValue> </DefinedValues> <RelOp AvgRowSize="0" EstimateCPU="0" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Constant Scan" NodeId="4" Parallel="false" PhysicalOp="Constant Scan" EstimatedTotalSubtreeCost="0"> <OutputList /> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" /> </RunTimeInformation> <ConstantScan /> </RelOp> </ComputeScalar> </RelOp> <RelOp AvgRowSize="1266" EstimateCPU="0.0001603" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="8" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032853" TableCardinality="3"> <OutputList> <ColumnReference Database="[sql2016]" Schema="[dbo]" Table="[MSSQL_TemporalHistoryFor_565577053]" Column="EmployeeID" /> <ColumnReference Database="[sql2016]" Schema="[dbo]" Table="[MSSQL_TemporalHistoryFor_565577053]" Column="Name" /> <ColumnReference Database="[sql2016]" Schema="[dbo]" Table="[MSSQL_TemporalHistoryFor_565577053]" Column="Position" /> <ColumnReference Database="[sql2016]" Schema="[dbo]" Table="[MSSQL_TemporalHistoryFor_565577053]" Column="Department" /> <ColumnReference Database="[sql2016]" Schema="[dbo]" Table="[MSSQL_TemporalHistoryFor_565577053]" Column="Address" /> <ColumnReference Database="[sql2016]" Schema="[dbo]" Table="[MSSQL_TemporalHistoryFor_565577053]" Column="AnnualSalary" /> <ColumnReference Database="[sql2016]" Schema="[dbo]" Table="[MSSQL_TemporalHistoryFor_565577053]" Column="ValidFrom" /> <ColumnReference Database="[sql2016]" Schema="[dbo]" Table="[MSSQL_TemporalHistoryFor_565577053]" Column="ValidTo" /> </OutputList> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRows="1" ActualRowsRead="3" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" /> </RunTimeInformation> <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore"> <DefinedValues> <DefinedValue> <ColumnReference Database="[sql2016]" Schema="[dbo]" Table="[MSSQL_TemporalHistoryFor_565577053]" Column="EmployeeID" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[sql2016]" Schema="[dbo]" Table="[MSSQL_TemporalHistoryFor_565577053]" Column="Name" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[sql2016]" Schema="[dbo]" Table="[MSSQL_TemporalHistoryFor_565577053]" Column="Position" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[sql2016]" Schema="[dbo]" Table="[MSSQL_TemporalHistoryFor_565577053]" Column="Department" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[sql2016]" Schema="[dbo]" Table="[MSSQL_TemporalHistoryFor_565577053]" Column="Address" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[sql2016]" Schema="[dbo]" Table="[MSSQL_TemporalHistoryFor_565577053]" Column="AnnualSalary" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[sql2016]" Schema="[dbo]" Table="[MSSQL_TemporalHistoryFor_565577053]" Column="ValidFrom" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[sql2016]" Schema="[dbo]" Table="[MSSQL_TemporalHistoryFor_565577053]" Column="ValidTo" /> </DefinedValue> </DefinedValues> <Object Database="[sql2016]" Schema="[dbo]" Table="[MSSQL_TemporalHistoryFor_565577053]" Index="[ix_MSSQL_TemporalHistoryFor_565577053]" IndexKind="Clustered" Storage="RowStore" /> <SeekPredicates> <SeekPredicateNew> <SeekKeys> <StartRange ScanType="GT"> <RangeColumns> <ColumnReference Database="[sql2016]" Schema="[dbo]" Table="[MSSQL_TemporalHistoryFor_565577053]" Column="ValidTo" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="[Expr1014]"> <Identifier> <ColumnReference Column="Expr1014" /> </Identifier> </ScalarOperator> </RangeExpressions> </StartRange> <EndRange ScanType="LT"> <RangeColumns> <ColumnReference Database="[sql2016]" Schema="[dbo]" Table="[MSSQL_TemporalHistoryFor_565577053]" Column="ValidTo" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="[Expr1015]"> <Identifier> <ColumnReference Column="Expr1015" /> </Identifier> </ScalarOperator> </RangeExpressions> </EndRange> </SeekKeys> </SeekPredicateNew> </SeekPredicates> <Predicate> <ScalarOperator ScalarString="[sql2016].[dbo].[MSSQL_TemporalHistoryFor_565577053].[ValidFrom]&lt;='2016-02-07 15:39:02.1000000'"> <Compare CompareOp="LE"> <ScalarOperator> <Identifier> <ColumnReference Database="[sql2016]" Schema="[dbo]" Table="[MSSQL_TemporalHistoryFor_565577053]" Column="ValidFrom" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="'2016-02-07 15:39:02.1000000'" /> </ScalarOperator> </Compare> </ScalarOperator> </Predicate> </IndexScan> </RelOp> </NestedLoops> </RelOp> </Concat> </RelOp> </QueryPlan> </StmtSimple> </Statements> </Batch> </BatchSequence> </ShowPlanXML>
Improve this question
4
  • I have stripped some on top to fall with in limit, Commented Feb 7, 2016 at 18:26
  • Thanks Daniel,i have pasted here..pastebin.com/dgXpve8G Commented Feb 7, 2016 at 18:31
  • Does the plan change if you alter the query to select * from Employee for system_time as of {ts '2016-02-07 15:39:02.100'}; ? Commented Feb 7, 2016 at 18:38
  • @DanielHutmacher:plan doesn't change Commented Feb 8, 2016 at 3:56

1 Answer 1

Reset to default
3

Thanks Kin for pointing to Paul White's article. Earlier wherever I have scalar operators I used to see them assigned some value,but in this case,those values are calculated from below function.

GetRangeWithMismatchedTypes('2016-02-07 15:39:02.1000000',NULL,(6))

So scalar operator 

Expr1014 is assigned--2016-02-07 15:39:02.1000000 Expr1015 is assigned null Expr1013 is some internal value here 6

So final query turns out to be like below..

select * from table where validto>Expr1014 and validto <null

As per Article,these values are there to convert data types which don't match,but in my case those columns are created by SQL server and I don't insert any data.

References:

https://sqlkiwi.blogspot.com/2012/01/dynamic-seeks-and-hidden-implicit-conversions.html

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.