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]<='2016-02-07 15:39:02.1000000' AND [sql2016].[dbo].[Employee].[ValidTo]>'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]<='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>
Best Answer
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.
So scalar operator
So final query turns out to be like below..
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