add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
id
5
3
6
4
Microsoft SQL Server 2005 XML Showplan
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.539" Build="15.0.4023.6"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT&#xa; U.id&#xa;FROM &#xa;(&#xa; -- T.refbool = 1&#xa; SELECT T.id &#xa; FROM @thisTable AS T&#xa; WHERE &#xa; T.col = &apos;val&apos;&#xa; AND T.refbool = 1&#xa;&#xa; -- Or&#xa; UNION ALL&#xa;&#xa; -- T.refbool = 0 and not exists&#xa; SELECT T.id &#xa; FROM @thisTable AS T&#xa; LEFT JOIN @thisTable AS T2&#xa; ON T2.refid = T.id&#xa; WHERE &#xa; T.col = &apos;val&apos;&#xa; AND T.refbool = 0&#xa; AND T2.id IS NULL&#xa;) AS U&#xa;ORDER BY &#xa; CHECKSUM(NEWID())&#xa;OPTION (HASH JOIN, MERGE JOIN)" StatementId="1" StatementCompId="4" StatementType="SELECT" RetrievedFromCache="false" StatementSubTreeCost="0.0390601" StatementEstRows="2.56508" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x570CB75412379FAB" QueryPlanHash="0x9A4ED93706CDB757" 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="1600" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="48" CompileTime="0" CompileCPU="0" CompileMemory="296"><MemoryGrantInfo SerialRequiredMemory="1536" SerialDesiredMemory="1600" RequiredMemory="1536" DesiredMemory="1600" RequestedMemory="1600" GrantWaitTime="0" MaxQueryMemory="996760" GrantedMemory="1600" MaxUsedMemory="256"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="838808" EstimatedPagesCached="52425" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="4131112"></OptimizerHardwareDependentProperties><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="Sort" LogicalOp="Sort" EstimateRows="2.56508" EstimateIO="0.0112613" EstimateCPU="0.000105453" AvgRowSize="15" EstimatedTotalSubtreeCost="0.0390601" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Union1003"></ColumnReference><ColumnReference Column="Expr1004"></ColumnReference></OutputList><MemoryFractions Input="0.5" 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="544" OutputMemoryGrant="192" UsedMemoryGrant="16"></RunTimeCountersPerThread></RunTimeInformation><Sort Distinct="0"><OrderBy><OrderByColumn Ascending="1"><ColumnReference Column="Expr1004"></ColumnReference></OrderByColumn></OrderBy><RelOp NodeId="1" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="2.56508" EstimateIO="0" EstimateCPU="2.56508e-07" AvgRowSize="15" EstimatedTotalSubtreeCost="0.0276934" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Union1003"></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><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1004"></ColumnReference><ScalarOperator ScalarString="checksum(newid())"><Intrinsic FunctionName="checksum"><ScalarOperator><Intrinsic FunctionName="newid"></Intrinsic></ScalarOperator></Intrinsic></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="2" PhysicalOp="Concatenation" LogicalOp="Concatenation" EstimateRows="2.56508" EstimateIO="0" EstimateCPU="2.56508e-07" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0276931" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Union1003"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Concat><DefinedValues><DefinedValue><ColumnReference Column="Union1003"></ColumnReference><ColumnReference Table="@thisTable" Alias="[T]" Column="id"></ColumnReference><ColumnReference Table="@thisTable" Alias="[T]" Column="id"></ColumnReference></DefinedValue></DefinedValues><RelOp NodeId="3" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="1.56508" EstimatedRowsRead="6" EstimateIO="0.003125" EstimateCPU="0.0001636" AvgRowSize="21" EstimatedTotalSubtreeCost="0.0032886" TableCardinality="6" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="@thisTable" Alias="[T]" Column="id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="2" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="6" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Table="@thisTable" Alias="[T]" Column="id"></ColumnReference></DefinedValue></DefinedValues><Object Table="[@thisTable]" Index="[PK__#B7C8BCF__3213E83F66F15688]" Alias="[T]" TableReferenceId="1" Storage="RowStore"></Object><Predicate><ScalarOperator ScalarString="@thisTable.[refbool] as [T].[refbool]=(1) AND @thisTable.[col] as [T].[col]=&apos;val&apos;"><Logical Operation="AND"><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Table="@thisTable" Alias="[T]" Column="refbool"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Table="@thisTable" Alias="[T]" Column="col"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="&apos;val&apos;"></Const></ScalarOperator></Compare></ScalarOperator></Logical></ScalarOperator></Predicate></IndexScan></RelOp><RelOp NodeId="4" PhysicalOp="Filter" LogicalOp="Filter" EstimateRows="1" EstimateIO="0" EstimateCPU="7.51241e-07" AvgRowSize="11" EstimatedTotalSubtreeCost="0.024399" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="@thisTable" Alias="[T]" Column="id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="2" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Filter StartupExpression="0"><RelOp NodeId="5" PhysicalOp="Hash Match" LogicalOp="Left Outer Join" EstimateRows="1.56508" EstimateIO="0" EstimateCPU="0.0178127" AvgRowSize="15" EstimatedTotalSubtreeCost="0.0243982" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="@thisTable" Alias="[T]" Column="id"></ColumnReference><ColumnReference Table="@thisTable" Alias="[T2]" Column="id"></ColumnReference></OutputList><MemoryFractions Input="0.5" Output="0.5"></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" ActualEndOfScans="1" ActualExecutions="1" InputMemoryGrant="1056" OutputMemoryGrant="1056" UsedMemoryGrant="248"></RunTimeCountersPerThread></RunTimeInformation><Hash><DefinedValues></DefinedValues><HashKeysBuild><ColumnReference Table="@thisTable" Alias="[T]" Column="id"></ColumnReference></HashKeysBuild><HashKeysProbe><ColumnReference Table="@thisTable" Alias="[T2]" Column="refid"></ColumnReference></HashKeysProbe><ProbeResidual><ScalarOperator ScalarString="@thisTable.[refid] as [T2].[refid]=@thisTable.[id] as [T].[id]"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Table="@thisTable" Alias="[T2]" Column="refid"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Table="@thisTable" Alias="[T]" Column="id"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></ProbeResidual><RelOp NodeId="6" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="1.56508" EstimatedRowsRead="6" EstimateIO="0.003125" EstimateCPU="0.0001636" AvgRowSize="21" EstimatedTotalSubtreeCost="0.0032886" TableCardinality="6" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="@thisTable" Alias="[T]" Column="id"></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="6" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Table="@thisTable" Alias="[T]" Column="id"></ColumnReference></DefinedValue></DefinedValues><Object Table="[@thisTable]" Index="[PK__#B7C8BCF__3213E83F66F15688]" Alias="[T]" TableReferenceId="2" Storage="RowStore"></Object><Predicate><ScalarOperator ScalarString="@thisTable.[refbool] as [T].[refbool]=(0) AND @thisTable.[col] as [T].[col]=&apos;val&apos;"><Logical Operation="AND"><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Table="@thisTable" Alias="[T]" Column="refbool"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(0)"></Const></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Table="@thisTable" Alias="[T]" Column="col"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="&apos;val&apos;"></Const></ScalarOperator></Compare></ScalarOperator></Logical></ScalarOperator></Predicate></IndexScan></RelOp><RelOp NodeId="7" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="6" EstimatedRowsRead="6" EstimateIO="0.003125" EstimateCPU="0.0001636" AvgRowSize="15" EstimatedTotalSubtreeCost="0.0032886" TableCardinality="6" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="@thisTable" Alias="[T2]" Column="id"></ColumnReference><ColumnReference Table="@thisTable" Alias="[T2]" Column="refid"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="6" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="6" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Table="@thisTable" Alias="[T2]" Column="id"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Table="@thisTable" Alias="[T2]" Column="refid"></ColumnReference></DefinedValue></DefinedValues><Object Table="[@thisTable]" Index="[PK__#B7C8BCF__3213E83F66F15688]" Alias="[T2]" Storage="RowStore"></Object></IndexScan></RelOp></Hash></RelOp><Predicate><ScalarOperator ScalarString="@thisTable.[id] as [T2].[id] IS NULL"><Compare CompareOp="IS"><ScalarOperator><Identifier><ColumnReference Table="@thisTable" Alias="[T2]" Column="id"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Compare></ScalarOperator></Predicate></Filter></RelOp></Concat></RelOp></ComputeScalar></RelOp></Sort></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>