Início / dba / Perguntas / 128526
Accepted
TheGameiswar
Asked: 2016-02-08 09:51:15 +0800 CST

Tendo dificuldade em entender as expressões no plano de consulta abaixo

  • 772

Eu estava passando por novos recursos de tabelas temporais no SQL 2016, um dos exemplos foi consultar tabelas com a cláusula as of

que retornou abaixo do plano de consulta

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

Aqui está minha consulta:

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

Não consigo entender o que os valores abaixo contêm

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

Vocês podem me ajudar a entender o que contém a expressão escalar, abaixo está todo o plano xml 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>
sql-server execution-plan

1 respostas

  1. Best Answer

    Obrigado Kin por apontar para o artigo de Paul White. Anteriormente, onde quer que eu tivesse operadores escalares, costumava vê-los atribuídos a algum valor, mas, neste caso, esses valores são calculados a partir da função abaixo.

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

    operador escalar

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

    Portanto, a consulta final acaba sendo como abaixo ..

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

    De acordo com o artigo, esses valores existem para converter tipos de dados que não correspondem, mas no meu caso essas colunas são criadas pelo servidor SQL e não insiro nenhum dado.

    Referências:

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

    • 3

relate perguntas