By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
16 rows affected
| ID | Name | DepartmentID | Name | DepartmentID | ord_index |
|---|---|---|---|---|---|
| 1 | A | 1 | Engineering | 1 | 1 |
| 2 | D | 2 | Administration | 2 | 1 |
| 3 | C | 3 | Sales | 3 | 1 |
| 4 | E | 4 | null | null | null |
| 5 | D | 5 | null | null | null |
| 6 | A | 6 | null | null | null |
| 7 | B | 7 | null | null | null |
| Microsoft SQL Server 2005 XML Showplan |
|---|
| <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.539" Build="15.0.4053.23"><BatchSequence><Batch><Statements><StmtSimple StatementText="select * from Table1 t 
left join (
select D.Name, E.DepartmentID, 
Row_Number() over (partition by E.DepartmentID order by D.DepartmentID asc, 
case when D.Name is null then 1 
else 0 end asc, D.Name asc) as ord_index 
from Department D 
inner join Employee E on D.DepartmentID = E.DepartmentID
) x 
on x.DepartmentID = t.DepartmentID 
where x.ord_index = 1 or x.ord_index is null" StatementId="1" StatementCompId="3" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="0.0325632" StatementEstRows="5.17157" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x52193D813381ED59" QueryPlanHash="0x1D1A4BA11BD919E7" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="150"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan DegreeOfParallelism="0" MemoryGrant="1024" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="48" CompileTime="0" CompileCPU="0" CompileMemory="408"><MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="544" RequiredMemory="512" DesiredMemory="544" RequestedMemory="1024" GrantWaitTime="0" MaxQueryMemory="1006648" GrantedMemory="1024" MaxUsedMemory="16"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="838808" EstimatedPagesCached="52425" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="3826056"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2022-08-11T10:08:29.49" ModificationCount="0" SamplingPercent="100" Statistics="[PK__Departme__B2079BCD433BCDB6]" Table="[Department]" Schema="[dbo]" Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]"></StatisticsInfo><StatisticsInfo LastUpdate="2022-08-11T10:08:29.49" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000003_22AA2996]" Table="[Table1]" Schema="[dbo]" Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]"></StatisticsInfo><StatisticsInfo LastUpdate="2022-08-11T10:08:29.49" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000004_267ABA7A]" Table="[Employee]" Schema="[dbo]" Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]"></StatisticsInfo></OptimizerStatsUsage><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Filter" LogicalOp="Filter" EstimateRows="5.17157" EstimateIO="0" EstimateCPU="7.04e-06" AvgRowSize="283" EstimatedTotalSubtreeCost="0.0325632" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Table1]" Alias="[t]" Column="ID"></ColumnReference><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Table1]" Alias="[t]" Column="Name"></ColumnReference><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Table1]" Alias="[t]" Column="DepartmentID"></ColumnReference><ColumnReference Column="Expr1005"></ColumnReference><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Department]" Alias="[D]" Column="DepartmentID"></ColumnReference><ColumnReference Column="Expr1007"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="7" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Filter StartupExpression="0"><RelOp NodeId="1" PhysicalOp="Nested Loops" LogicalOp="Left Outer Join" EstimateRows="8" EstimateIO="0" EstimateCPU="0.00011704" AvgRowSize="283" EstimatedTotalSubtreeCost="0.0325562" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Table1]" Alias="[t]" Column="ID"></ColumnReference><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Table1]" Alias="[t]" Column="Name"></ColumnReference><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Table1]" Alias="[t]" Column="DepartmentID"></ColumnReference><ColumnReference Column="Expr1005"></ColumnReference><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Department]" Alias="[D]" Column="DepartmentID"></ColumnReference><ColumnReference Column="Expr1007"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="8" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><Predicate><ScalarOperator ScalarString="[fiddle_4a31eff0643c4f23875ad86e57e521a2].[dbo].[Department].[DepartmentID] as [D].[DepartmentID]=[fiddle_4a31eff0643c4f23875ad86e57e521a2].[dbo].[Table1].[DepartmentID] as [t].[DepartmentID]"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Department]" Alias="[D]" Column="DepartmentID"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Table1]" Alias="[t]" Column="DepartmentID"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Predicate><RelOp NodeId="2" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="7" EstimatedRowsRead="7" EstimateIO="0.003125" EstimateCPU="0.0001647" AvgRowSize="144" EstimatedTotalSubtreeCost="0.0032897" TableCardinality="7" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Table1]" Alias="[t]" Column="ID"></ColumnReference><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Table1]" Alias="[t]" Column="Name"></ColumnReference><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Table1]" Alias="[t]" Column="DepartmentID"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="7" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="7" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Table1]" Alias="[t]" Column="ID"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Table1]" Alias="[t]" Column="Name"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Table1]" Alias="[t]" Column="DepartmentID"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Table1]" Index="[PK__Table1__3214EC27216807C1]" Alias="[t]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp><RelOp NodeId="4" PhysicalOp="Table Spool" LogicalOp="Lazy Spool" EstimateRows="4" EstimateIO="0.01" EstimateCPU="0.00010112" AvgRowSize="152" EstimatedTotalSubtreeCost="0.029141" Parallel="0" EstimateRebinds="0" EstimateRewinds="6" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Department]" Alias="[D]" Column="DepartmentID"></ColumnReference><ColumnReference Column="Expr1005"></ColumnReference><ColumnReference Column="Expr1007"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="28" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="21" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="24" ActualRebinds="1" ActualRewinds="6" ActualEndOfScans="7" ActualExecutions="7"></RunTimeCountersPerThread></RunTimeInformation><Spool><RelOp NodeId="5" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="4" EstimateIO="0" EstimateCPU="4e-07" AvgRowSize="152" EstimatedTotalSubtreeCost="0.0184356" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Department]" Alias="[D]" Column="DepartmentID"></ColumnReference><ColumnReference Column="Expr1005"></ColumnReference><ColumnReference Column="Expr1006"></ColumnReference><ColumnReference Column="Expr1007"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1005"></ColumnReference><ScalarOperator ScalarString="[fiddle_4a31eff0643c4f23875ad86e57e521a2].[dbo].[Department].[Name] as [D].[Name]"><Identifier><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Department]" Alias="[D]" Column="Name"></ColumnReference></Identifier></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="Expr1006"></ColumnReference><ScalarOperator ScalarString="[fiddle_4a31eff0643c4f23875ad86e57e521a2].[dbo].[Department].[DepartmentID] as [D].[DepartmentID]"><Identifier><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Department]" Alias="[D]" Column="DepartmentID"></ColumnReference></Identifier></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="Expr1007"></ColumnReference><ScalarOperator ScalarString="[Expr1004]"><Identifier><ColumnReference Column="Expr1004"></ColumnReference></Identifier></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="6" PhysicalOp="Sequence Project" LogicalOp="Compute Scalar" EstimateRows="4" EstimateIO="0" EstimateCPU="3.2e-07" AvgRowSize="148" EstimatedTotalSubtreeCost="0.0184352" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Department]" Alias="[D]" Column="Name"></ColumnReference><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Department]" Alias="[D]" Column="DepartmentID"></ColumnReference><ColumnReference Column="Expr1004"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><SequenceProject><DefinedValues><DefinedValue><ColumnReference Column="Expr1004"></ColumnReference><ScalarOperator ScalarString="row_number"><Sequence FunctionName="row_number"></Sequence></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="7" PhysicalOp="Segment" LogicalOp="Segment" EstimateRows="4" EstimateIO="0" EstimateCPU="8e-08" AvgRowSize="148" EstimatedTotalSubtreeCost="0.0184349" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Department]" Alias="[D]" Column="DepartmentID"></ColumnReference><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Department]" Alias="[D]" Column="Name"></ColumnReference><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Department]" Alias="[D]" Column="DepartmentID"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference><ColumnReference Column="Segment1008"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Segment><GroupBy><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Department]" Alias="[D]" Column="DepartmentID"></ColumnReference></GroupBy><SegmentColumn><ColumnReference Column="Segment1008"></ColumnReference></SegmentColumn><RelOp NodeId="9" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="4" EstimateIO="0.0112613" EstimateCPU="0.000112628" AvgRowSize="148" EstimatedTotalSubtreeCost="0.0184348" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Department]" Alias="[D]" Column="DepartmentID"></ColumnReference><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Department]" Alias="[D]" Column="Name"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference></OutputList><MemoryFractions Input="1" Output="1"></MemoryFractions><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="1" ActualExecutions="1" InputMemoryGrant="1024" OutputMemoryGrant="640" UsedMemoryGrant="16"></RunTimeCountersPerThread></RunTimeInformation><Sort Distinct="0"><OrderBy><OrderByColumn Ascending="1"><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Department]" Alias="[D]" Column="DepartmentID"></ColumnReference></OrderByColumn><OrderByColumn Ascending="1"><ColumnReference Column="Expr1003"></ColumnReference></OrderByColumn></OrderBy><RelOp NodeId="10" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="4" EstimateIO="0" EstimateCPU="4e-07" AvgRowSize="148" EstimatedTotalSubtreeCost="0.00706092" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Department]" Alias="[D]" Column="DepartmentID"></ColumnReference><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Department]" Alias="[D]" Column="Name"></ColumnReference><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Employee]" Alias="[E]" Column="DepartmentID"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1003"></ColumnReference><ScalarOperator ScalarString="CASE WHEN [fiddle_4a31eff0643c4f23875ad86e57e521a2].[dbo].[Department].[Name] as [D].[Name] IS NULL THEN (1) ELSE (0) END"><IF><Condition><ScalarOperator><Compare CompareOp="IS"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Department]" Alias="[D]" Column="Name"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator></Then><Else><ScalarOperator><Const ConstValue="(0)"></Const></ScalarOperator></Else></IF></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="11" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="4" EstimateIO="0" EstimateCPU="1.672e-05" AvgRowSize="144" EstimatedTotalSubtreeCost="0.00706052" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Department]" Alias="[D]" Column="DepartmentID"></ColumnReference><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Department]" Alias="[D]" Column="Name"></ColumnReference><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Employee]" Alias="[E]" Column="DepartmentID"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><OuterReferences><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Employee]" Alias="[E]" Column="DepartmentID"></ColumnReference></OuterReferences><RelOp NodeId="12" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="4" EstimatedRowsRead="4" EstimateIO="0.003125" EstimateCPU="0.0001614" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0032864" TableCardinality="4" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Employee]" Alias="[E]" Column="DepartmentID"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="4" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Employee]" Alias="[E]" Column="DepartmentID"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Employee]" Index="[PK__Employee__7AD04FF1DADB766A]" Alias="[E]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp><RelOp NodeId="13" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="140" EstimatedTotalSubtreeCost="0.0037574" TableCardinality="5" Parallel="0" EstimateRebinds="2.5" EstimateRewinds="0.5" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Department]" Alias="[D]" Column="DepartmentID"></ColumnReference><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Department]" Alias="[D]" Column="Name"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="8" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="4" ActualEndOfScans="0" ActualExecutions="4"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Department]" Alias="[D]" Column="DepartmentID"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Department]" Alias="[D]" Column="Name"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Department]" Index="[PK__Departme__B2079BCD433BCDB6]" Alias="[D]" IndexKind="Clustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Department]" Alias="[D]" Column="DepartmentID"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[fiddle_4a31eff0643c4f23875ad86e57e521a2].[dbo].[Employee].[DepartmentID] as [E].[DepartmentID]"><Identifier><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Employee]" Alias="[E]" Column="DepartmentID"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></NestedLoops></RelOp></ComputeScalar></RelOp></Sort></RelOp></Segment></RelOp></SequenceProject></RelOp></ComputeScalar></RelOp></Spool></RelOp></NestedLoops></RelOp><Predicate><ScalarOperator ScalarString="[Expr1007] IS NULL OR [Expr1007]=(1)"><Logical Operation="OR"><ScalarOperator><Compare CompareOp="IS"><ScalarOperator><Identifier><ColumnReference Column="Expr1007"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Column="Expr1007"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator></Compare></ScalarOperator></Logical></ScalarOperator></Predicate></Filter></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML> |
Table 'Worktable'. Scan count 1, logical reads 21, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Department'. Scan count 0, logical reads 8, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Employee'. Scan count 1, logical reads 2, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Table1'. Scan count 1, logical reads 2, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
| ID | Name | DepartmentID | Name | ord_index |
|---|---|---|---|---|
| 1 | A | 1 | Engineering | 1 |
| 2 | D | 2 | Administration | 1 |
| 3 | C | 3 | Sales | 1 |
| 4 | E | 4 | null | null |
| 5 | D | 5 | null | null |
| 6 | A | 6 | null | null |
| 7 | B | 7 | null | null |
| Microsoft SQL Server 2005 XML Showplan |
|---|
| <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.539" Build="15.0.4053.23"><BatchSequence><Batch><Statements><StmtSimple StatementText="select * from Table1 t 
outer apply (
	select D.Name,
		Row_Number() over (partition by E.DepartmentID order by D.DepartmentID asc, 
		case when D.Name is null then 1 
		else 0 end asc, D.Name asc) as ord_index 
	from Department D 
	join Employee E on D.DepartmentID = E.DepartmentID
	where E.DepartmentID = t.DepartmentID 
) x 
where x.ord_index = 1 or x.ord_index is null" StatementId="1" StatementCompId="1" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="0.0114074" StatementEstRows="6.0335" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x9F472CAC9679F666" QueryPlanHash="0xED4BBA1F50371BDE" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="150"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="40" CompileTime="0" CompileCPU="0" CompileMemory="384"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="838808" EstimatedPagesCached="52425" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="3826056"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2022-08-11T10:08:29.49" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000003_22AA2996]" Table="[Table1]" Schema="[dbo]" Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]"></StatisticsInfo><StatisticsInfo LastUpdate="2022-08-11T10:08:29.49" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000004_267ABA7A]" Table="[Employee]" Schema="[dbo]" Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]"></StatisticsInfo></OptimizerStatsUsage><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Filter" LogicalOp="Filter" EstimateRows="6.0335" EstimateIO="0" EstimateCPU="8.21333e-06" AvgRowSize="279" EstimatedTotalSubtreeCost="0.0114074" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Table1]" Alias="[t]" Column="ID"></ColumnReference><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Table1]" Alias="[t]" Column="Name"></ColumnReference><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Table1]" Alias="[t]" Column="DepartmentID"></ColumnReference><ColumnReference Column="Expr1005"></ColumnReference><ColumnReference Column="Expr1006"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="7" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Filter StartupExpression="0"><RelOp NodeId="1" PhysicalOp="Nested Loops" LogicalOp="Left Outer Join" EstimateRows="9.33333" EstimateIO="0" EstimateCPU="3.90133e-05" AvgRowSize="279" EstimatedTotalSubtreeCost="0.0113992" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Table1]" Alias="[t]" Column="ID"></ColumnReference><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Table1]" Alias="[t]" Column="Name"></ColumnReference><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Table1]" Alias="[t]" Column="DepartmentID"></ColumnReference><ColumnReference Column="Expr1005"></ColumnReference><ColumnReference Column="Expr1006"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="8" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><OuterReferences><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Table1]" Alias="[t]" Column="DepartmentID"></ColumnReference></OuterReferences><RelOp NodeId="2" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="7" EstimatedRowsRead="7" EstimateIO="0.003125" EstimateCPU="0.0001647" AvgRowSize="144" EstimatedTotalSubtreeCost="0.0032897" TableCardinality="7" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Table1]" Alias="[t]" Column="ID"></ColumnReference><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Table1]" Alias="[t]" Column="Name"></ColumnReference><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Table1]" Alias="[t]" Column="DepartmentID"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="7" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="7" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Table1]" Alias="[t]" Column="ID"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Table1]" Alias="[t]" Column="Name"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Table1]" Alias="[t]" Column="DepartmentID"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Table1]" Index="[PK__Table1__3214EC27216807C1]" Alias="[t]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp><RelOp NodeId="3" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1.33333" EstimateIO="0" EstimateCPU="1.33333e-07" AvgRowSize="144" EstimatedTotalSubtreeCost="0.00807052" Parallel="0" EstimateRebinds="6" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1005"></ColumnReference><ColumnReference Column="Expr1006"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1005"></ColumnReference><ScalarOperator ScalarString="[fiddle_4a31eff0643c4f23875ad86e57e521a2].[dbo].[Department].[Name] as [D].[Name]"><Identifier><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Department]" Alias="[D]" Column="Name"></ColumnReference></Identifier></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="Expr1006"></ColumnReference><ScalarOperator ScalarString="[Expr1004]"><Identifier><ColumnReference Column="Expr1004"></ColumnReference></Identifier></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="4" PhysicalOp="Sequence Project" LogicalOp="Compute Scalar" EstimateRows="1.33333" EstimateIO="0" EstimateCPU="1.06667e-07" AvgRowSize="144" EstimatedTotalSubtreeCost="0.00806959" Parallel="0" EstimateRebinds="6" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Department]" Alias="[D]" Column="Name"></ColumnReference><ColumnReference Column="Expr1004"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="7" ActualExecutions="7"></RunTimeCountersPerThread></RunTimeInformation><SequenceProject><DefinedValues><DefinedValue><ColumnReference Column="Expr1004"></ColumnReference><ScalarOperator ScalarString="row_number"><Sequence FunctionName="row_number"></Sequence></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="5" PhysicalOp="Segment" LogicalOp="Segment" EstimateRows="1.33333" EstimateIO="0" EstimateCPU="2.66667e-08" AvgRowSize="144" EstimatedTotalSubtreeCost="0.00806884" Parallel="0" EstimateRebinds="6" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Department]" Alias="[D]" Column="Name"></ColumnReference><ColumnReference Column="Segment1007"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="7" ActualExecutions="7"></RunTimeCountersPerThread></RunTimeInformation><Segment><GroupBy></GroupBy><SegmentColumn><ColumnReference Column="Segment1007"></ColumnReference></SegmentColumn><RelOp NodeId="6" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="1.33333" EstimateIO="0" EstimateCPU="5.57333e-06" AvgRowSize="136" EstimatedTotalSubtreeCost="0.00806865" Parallel="0" EstimateRebinds="6" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Department]" Alias="[D]" Column="Name"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="7" ActualExecutions="7"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><RelOp NodeId="8" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="136" EstimatedTotalSubtreeCost="0.0042317" TableCardinality="5" Parallel="0" EstimateRebinds="6" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Department]" Alias="[D]" Column="Name"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="5" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="14" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="5" ActualEndOfScans="5" ActualExecutions="7"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Department]" Alias="[D]" Column="Name"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Department]" Index="[PK__Departme__B2079BCD433BCDB6]" Alias="[D]" IndexKind="Clustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Department]" Alias="[D]" Column="DepartmentID"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[fiddle_4a31eff0643c4f23875ad86e57e521a2].[dbo].[Table1].[DepartmentID] as [t].[DepartmentID]"><Identifier><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Table1]" Alias="[t]" Column="DepartmentID"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp><RelOp NodeId="9" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="1.33333" EstimatedRowsRead="4" EstimateIO="0.0032035" EstimateCPU="8.29e-05" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0037838" TableCardinality="4" Parallel="0" EstimateRebinds="0" EstimateRewinds="6" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="11" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="20" ActualEndOfScans="5" ActualExecutions="5"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues></DefinedValues><Object Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Employee]" Index="[PK__Employee__7AD04FF1DADB766A]" Alias="[E]" IndexKind="Clustered" Storage="RowStore"></Object><Predicate><ScalarOperator ScalarString="[fiddle_4a31eff0643c4f23875ad86e57e521a2].[dbo].[Employee].[DepartmentID] as [E].[DepartmentID]=[fiddle_4a31eff0643c4f23875ad86e57e521a2].[dbo].[Table1].[DepartmentID] as [t].[DepartmentID]"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Employee]" Alias="[E]" Column="DepartmentID"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_4a31eff0643c4f23875ad86e57e521a2]" Schema="[dbo]" Table="[Table1]" Alias="[t]" Column="DepartmentID"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Predicate></IndexScan></RelOp></NestedLoops></RelOp></Segment></RelOp></SequenceProject></RelOp></ComputeScalar></RelOp></NestedLoops></RelOp><Predicate><ScalarOperator ScalarString="[Expr1006] IS NULL OR [Expr1006]=(1)"><Logical Operation="OR"><ScalarOperator><Compare CompareOp="IS"><ScalarOperator><Identifier><ColumnReference Column="Expr1006"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Column="Expr1006"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator></Compare></ScalarOperator></Logical></ScalarOperator></Predicate></Filter></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML> |
Table 'Employee'. Scan count 1, logical reads 11, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Department'. Scan count 0, logical reads 14, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Table1'. Scan count 1, logical reads 2, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.