| t1_id |
t2_id |
| null |
3 |
| 1 |
null |
| 2 |
2 |
| Microsoft SQL Server 2005 XML Showplan |
| <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.564" Build="16.0.1000.6"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT
 x.t1_id, 
 x.t2_id
FROM
(
SELECT 
 t1_id = 
 t1.id,
 t2_id = 
 t2.id,
 n =
 ROW_NUMBER() OVER
 (
 ORDER BY
 t1.id,
 t2.id
 ) 
FROM dbo.t1
FULL JOIN dbo.t2 
 ON t2.id = t1.id
) AS x
ORDER BY x.n" StatementId="1" StatementCompId="2" StatementType="SELECT" StatementSqlHandle="0x090078A80C58CDAEC5FDC0A052189F382A220000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="2" ParentObjectId="0" StatementParameterizationType="0" RetrievedFromCache="true" StatementSubTreeCost="0.0189345" StatementEstRows="3" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x4B8C29F974EF8F6E" QueryPlanHash="0xC5AFF5EEF2CF85F1" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="160"><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="32" CompileTime="62" CompileCPU="62" CompileMemory="264"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="998360"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2023-03-18T19:49:08.45" ModificationCount="0" SamplingPercent="100" Statistics="[PK__t1__3213E83F2E124F5F]" Table="[t1]" Schema="[dbo]" Database="[fiddle_ef6b90b7860f4a319a88e08c1f5e05fc]"></StatisticsInfo><StatisticsInfo LastUpdate="2023-03-18T19:49:08.45" ModificationCount="0" SamplingPercent="100" Statistics="[PK__t2__3213E83F78AB689F]" Table="[t2]" Schema="[dbo]" Database="[fiddle_ef6b90b7860f4a319a88e08c1f5e05fc]"></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="Sequence Project" LogicalOp="Compute Scalar" EstimateRows="3" EstimateIO="0" EstimateCPU="2.4e-07" AvgRowSize="23" EstimatedTotalSubtreeCost="0.0189345" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_ef6b90b7860f4a319a88e08c1f5e05fc]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_ef6b90b7860f4a319a88e08c1f5e05fc]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference><ColumnReference Column="Expr1004"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><SequenceProject><DefinedValues><DefinedValue><ColumnReference Column="Expr1004"></ColumnReference><ScalarOperator ScalarString="row_number"><Sequence FunctionName="row_number"></Sequence></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="1" PhysicalOp="Segment" LogicalOp="Segment" EstimateRows="3" EstimateIO="0" EstimateCPU="6e-08" AvgRowSize="23" EstimatedTotalSubtreeCost="0.0189342" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_ef6b90b7860f4a319a88e08c1f5e05fc]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_ef6b90b7860f4a319a88e08c1f5e05fc]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference><ColumnReference Column="Segment1005"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><Segment><GroupBy></GroupBy><SegmentColumn><ColumnReference Column="Segment1005"></ColumnReference></SegmentColumn><RelOp NodeId="2" PhysicalOp="Merge Join" LogicalOp="Concatenation" EstimateRows="3" EstimateIO="0" EstimateCPU="0.0056045" AvgRowSize="15" EstimatedTotalSubtreeCost="0.0189342" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_ef6b90b7860f4a319a88e08c1f5e05fc]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_ef6b90b7860f4a319a88e08c1f5e05fc]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><Merge><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_ef6b90b7860f4a319a88e08c1f5e05fc]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_ef6b90b7860f4a319a88e08c1f5e05fc]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_ef6b90b7860f4a319a88e08c1f5e05fc]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_ef6b90b7860f4a319a88e08c1f5e05fc]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_ef6b90b7860f4a319a88e08c1f5e05fc]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_ef6b90b7860f4a319a88e08c1f5e05fc]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference></DefinedValue></DefinedValues><RelOp NodeId="3" PhysicalOp="Nested Loops" LogicalOp="Left Outer Join" EstimateRows="2" EstimateIO="0" EstimateCPU="1.672e-05" AvgRowSize="15" EstimatedTotalSubtreeCost="0.00666702" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_ef6b90b7860f4a319a88e08c1f5e05fc]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_ef6b90b7860f4a319a88e08c1f5e05fc]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="2" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><Predicate><ScalarOperator ScalarString="[fiddle_ef6b90b7860f4a319a88e08c1f5e05fc].[dbo].[t2].[id]=[fiddle_ef6b90b7860f4a319a88e08c1f5e05fc].[dbo].[t1].[id]"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_ef6b90b7860f4a319a88e08c1f5e05fc]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_ef6b90b7860f4a319a88e08c1f5e05fc]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Predicate><RelOp NodeId="4" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="2" EstimatedRowsRead="2" EstimateIO="0.003125" EstimateCPU="0.0001592" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0032842" TableCardinality="2" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_ef6b90b7860f4a319a88e08c1f5e05fc]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="2" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="2" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_ef6b90b7860f4a319a88e08c1f5e05fc]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_ef6b90b7860f4a319a88e08c1f5e05fc]" Schema="[dbo]" Table="[t1]" Index="[PK__t1__3213E83F2E124F5F]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp><RelOp NodeId="5" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="2" EstimatedRowsRead="2" EstimateIO="0.0032035" EstimateCPU="8.07e-05" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0033649" TableCardinality="2" Parallel="0" EstimateRebinds="0" EstimateRewinds="1" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_ef6b90b7860f4a319a88e08c1f5e05fc]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="5" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="4" ActualEndOfScans="2" ActualExecutions="2" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_ef6b90b7860f4a319a88e08c1f5e05fc]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_ef6b90b7860f4a319a88e08c1f5e05fc]" Schema="[dbo]" Table="[t2]" Index="[PK__t2__3213E83F78AB689F]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp></NestedLoops></RelOp><RelOp NodeId="6" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-07" AvgRowSize="15" EstimatedTotalSubtreeCost="0.00666264" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_ef6b90b7860f4a319a88e08c1f5e05fc]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_ef6b90b7860f4a319a88e08c1f5e05fc]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_ef6b90b7860f4a319a88e08c1f5e05fc]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference><ScalarOperator ScalarString="NULL"><Const ConstValue="NULL"></Const></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="7" PhysicalOp="Nested Loops" LogicalOp="Left Anti Semi Join" EstimateRows="1" EstimateIO="0" EstimateCPU="1.254e-05" AvgRowSize="11" EstimatedTotalSubtreeCost="0.00666254" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_ef6b90b7860f4a319a88e08c1f5e05fc]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><Predicate><ScalarOperator ScalarString="[fiddle_ef6b90b7860f4a319a88e08c1f5e05fc].[dbo].[t2].[id]=[fiddle_ef6b90b7860f4a319a88e08c1f5e05fc].[dbo].[t1].[id]"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_ef6b90b7860f4a319a88e08c1f5e05fc]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_ef6b90b7860f4a319a88e08c1f5e05fc]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Predicate><RelOp NodeId="8" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="2" EstimatedRowsRead="2" EstimateIO="0.003125" EstimateCPU="0.0001592" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0032842" TableCardinality="2" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_ef6b90b7860f4a319a88e08c1f5e05fc]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="2" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="2" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_ef6b90b7860f4a319a88e08c1f5e05fc]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_ef6b90b7860f4a319a88e08c1f5e05fc]" Schema="[dbo]" Table="[t2]" Index="[PK__t2__3213E83F78AB689F]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp><RelOp NodeId="9" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="2" EstimatedRowsRead="2" EstimateIO="0.0032035" EstimateCPU="8.07e-05" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0033649" TableCardinality="2" Parallel="0" EstimateRebinds="0" EstimateRewinds="1" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_ef6b90b7860f4a319a88e08c1f5e05fc]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="5" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="4" ActualEndOfScans="1" ActualExecutions="2" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_ef6b90b7860f4a319a88e08c1f5e05fc]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_ef6b90b7860f4a319a88e08c1f5e05fc]" Schema="[dbo]" Table="[t1]" Index="[PK__t1__3213E83F2E124F5F]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp></NestedLoops></RelOp></ComputeScalar></RelOp></Merge></RelOp></Segment></RelOp></SequenceProject></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML> |