| t1_id |
t2_id |
| 1 |
null |
| 4 |
null |
| 2 |
2 |
| null |
3 |
| 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
 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="0x0900411EEEFDF2177754495FEDB28D430E8B0000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="2" ParentObjectId="0" StatementParameterizationType="0" RetrievedFromCache="true" StatementSubTreeCost="0.0235576" StatementEstRows="4" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0xA9B0BA076320EDF4" QueryPlanHash="0x3901437B607F4D9A" 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" MemoryGrant="1024" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="24" CompileTime="0" CompileCPU="0" CompileMemory="264"><MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="544" RequiredMemory="512" DesiredMemory="544" RequestedMemory="1024" GrantWaitTime="0" MaxQueryMemory="681816" GrantedMemory="1024" MaxUsedMemory="16"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="992384"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2023-03-18T19:57:28.95" ModificationCount="0" SamplingPercent="100" Statistics="[PK__t1__3213E83F324EA957]" Table="[t1]" Schema="[dbo]" Database="[fiddle_9a9a001b06764ddba1c5f8cf92a2a79f]"></StatisticsInfo><StatisticsInfo LastUpdate="2023-03-18T19:57:28.95" ModificationCount="0" SamplingPercent="100" Statistics="[PK__t2__3213E83FC9E6D41C]" Table="[t2]" Schema="[dbo]" Database="[fiddle_9a9a001b06764ddba1c5f8cf92a2a79f]"></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="4" EstimateIO="0" EstimateCPU="3.2e-07" AvgRowSize="23" EstimatedTotalSubtreeCost="0.0235576" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9a9a001b06764ddba1c5f8cf92a2a79f]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_9a9a001b06764ddba1c5f8cf92a2a79f]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference><ColumnReference Column="Expr1004"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4" 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="4" EstimateIO="0" EstimateCPU="8e-08" AvgRowSize="23" EstimatedTotalSubtreeCost="0.0235572" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9a9a001b06764ddba1c5f8cf92a2a79f]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_9a9a001b06764ddba1c5f8cf92a2a79f]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference><ColumnReference Column="Segment1005"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4" 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="Sort" LogicalOp="Sort" EstimateRows="4" EstimateIO="0.0112613" EstimateCPU="0.000112495" AvgRowSize="15" EstimatedTotalSubtreeCost="0.0235572" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9a9a001b06764ddba1c5f8cf92a2a79f]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_9a9a001b06764ddba1c5f8cf92a2a79f]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference></OutputList><MemoryFractions Input="1" Output="1"></MemoryFractions><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4" Batches="0" 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" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><Sort Distinct="0"><OrderBy><OrderByColumn Ascending="1"><ColumnReference Database="[fiddle_9a9a001b06764ddba1c5f8cf92a2a79f]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference></OrderByColumn></OrderBy><RelOp NodeId="3" PhysicalOp="Merge Join" LogicalOp="Full Outer Join" EstimateRows="4" EstimateIO="0" EstimateCPU="0.0056109" AvgRowSize="15" EstimatedTotalSubtreeCost="0.0121834" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9a9a001b06764ddba1c5f8cf92a2a79f]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_9a9a001b06764ddba1c5f8cf92a2a79f]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4" 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 ManyToMany="0"><InnerSideJoinColumns><ColumnReference Database="[fiddle_9a9a001b06764ddba1c5f8cf92a2a79f]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference></InnerSideJoinColumns><OuterSideJoinColumns><ColumnReference Database="[fiddle_9a9a001b06764ddba1c5f8cf92a2a79f]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference></OuterSideJoinColumns><Residual><ScalarOperator ScalarString="[fiddle_9a9a001b06764ddba1c5f8cf92a2a79f].[dbo].[t2].[id]=[fiddle_9a9a001b06764ddba1c5f8cf92a2a79f].[dbo].[t1].[id]"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_9a9a001b06764ddba1c5f8cf92a2a79f]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_9a9a001b06764ddba1c5f8cf92a2a79f]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Residual><RelOp NodeId="4" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="3" EstimatedRowsRead="3" EstimateIO="0.003125" EstimateCPU="0.0001603" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0032853" TableCardinality="3" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9a9a001b06764ddba1c5f8cf92a2a79f]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="3" 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_9a9a001b06764ddba1c5f8cf92a2a79f]" Schema="[dbo]" Table="[t1]" Column="id"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_9a9a001b06764ddba1c5f8cf92a2a79f]" Schema="[dbo]" Table="[t1]" Index="[PK__t1__3213E83F324EA957]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp><RelOp NodeId="5" 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_9a9a001b06764ddba1c5f8cf92a2a79f]" 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_9a9a001b06764ddba1c5f8cf92a2a79f]" Schema="[dbo]" Table="[t2]" Column="id"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_9a9a001b06764ddba1c5f8cf92a2a79f]" Schema="[dbo]" Table="[t2]" Index="[PK__t2__3213E83FC9E6D41C]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp></Merge></RelOp></Sort></RelOp></Segment></RelOp></SequenceProject></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML> |