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>
  • I have stripped some on top to fall with in limit, – TheGameiswar Feb 7 '16 at 18:26
  • Thanks Daniel,i have pasted here..pastebin.com/dgXpve8G – TheGameiswar Feb 7 '16 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'}; ? – Daniel Hutmacher Feb 7 '16 at 18:38
  • @DanielHutmacher:plan doesn't change – TheGameiswar Feb 8 '16 at 3:56
2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.