SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

DROP TABLE IF EXISTS #edges;
CREATE TABLE #edges(tail int, head int);
INSERT INTO #edges(tail,head) VALUES
    (0,1), (5, 6),  (10,11), (15,16), --(20,21), (25,26), (30,31), (35,36),
    (0,2), (5, 7),  (10,12), (15,17), --(20,22), (25,27), (30,32), (35,37),
    (1,2), (6, 7),  (11,12), (16,17), --(21,22), (26,27), (31,32), (36,37),
    (1,3), (7, 8),  (11,13), (17,18), --(21,23), (27,28), (31,33), (37,38),
    (2,3), (7, 9),  (12,13), (17,19), --(22,23), (27,29), (32,33), (37,39),
    (2,4), (8, 9),  (12,14), (18,19), --(22,24), (28,29), (32,34), (38,39),
    (3,4), (8,10),  (13,14), (18,20), --(23,24), (28,30), (33,34), (38,40),
    (3,5), (9,10),  (13,15), (19,20), --(23,25), (29,30), (33,35), (39,40),
    (4,5), (9,11),  (14,15), (19,21), --(24,25), (29,31), (34,35), (39,41),
    (4,6),          (14,16);          --(24,26),          (34,36);
set statistics xml on;
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
WITH descendents(node)
AS(
    SELECT 0 as node
    UNION ALL
    SELECT head as node FROM descendents as prior
    JOIN #edges ON prior.node = tail
)
SELECT DISTINCT
    node
FROM descendents
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
set statistics xml off;

node
0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

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="WITH descendents(node) AS( SELECT 0 as node UNION ALL SELECT head as node FROM descendents as prior JOIN #edges ON prior.node = tail ) SELECT DISTINCT node FROM descendents" StatementId="1" StatementCompId="7" StatementType="SELECT" StatementSqlHandle="0x0900F32E78D758F9FF7283761825028274DA0000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="0" BatchSqlHandle="0x020000005CD63B210C7C4421DABC1892794029F7CA0E18900000000000000000000000000000000000000000" StatementParameterizationType="0" RetrievedFromCache="true" StatementSubTreeCost="0.0156617" StatementEstRows="2.58844" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x6645CF0060D310B3" QueryPlanHash="0x17945AB491D2E30C" 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="32" CompileTime="0" CompileCPU="0" CompileMemory="216"><MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="544" RequiredMemory="512" DesiredMemory="544" RequestedMemory="1024" GrantWaitTime="0" MaxQueryMemory="791280" GrantedMemory="1024" MaxUsedMemory="1024"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1290704"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2023-05-20T21:59:21.02" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000001_BB8C006A]" Table="[#edges______________________________________________________________________________________________________________00000000048B]" Schema="[dbo]" Database="[tempdb]"></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><WaitStats><Wait WaitType="PAGELATCH_EX" WaitTimeMs="62" WaitCount="9344"></Wait><Wait WaitType="PAGELATCH_SH" WaitTimeMs="62" WaitCount="10512"></Wait></WaitStats><QueryTimeStats ElapsedTime="578" CpuTime="484"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Sort" LogicalOp="Distinct Sort" EstimateRows="2.58844" EstimateIO="0.0112613" EstimateCPU="0.000128693" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0156617" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Recr1005"></ColumnReference></OutputList><Warnings><SpillToTempDb SpillLevel="1" SpilledThreadCount="1"></SpillToTempDb><SortSpillDetails GrantedMemoryKb="1024" UsedMemoryKb="1024" WritesToTempDb="2" ReadsFromTempDb="2"></SortSpillDetails></Warnings><MemoryFractions Input="1" Output="1"></MemoryFractions><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="22" Batches="0" ActualElapsedms="578" ActualCPUms="484" 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="1024" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><Sort Distinct="1"><OrderBy><OrderByColumn Ascending="1"><ColumnReference Column="Recr1005"></ColumnReference></OrderByColumn></OrderBy><RelOp NodeId="1" PhysicalOp="Index Spool" LogicalOp="Lazy Spool" EstimateRows="6.7" EstimateIO="0" EstimateCPU="1.45e-08" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0042717" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1009"></ColumnReference><ColumnReference Column="Recr1005"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="19857" Batches="0" ActualElapsedms="578" ActualCPUms="484" ActualScans="1" ActualLogicalReads="39715" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><Spool Stack="1"><RelOp NodeId="2" PhysicalOp="Concatenation" LogicalOp="Concatenation" EstimateRows="6.7" EstimateIO="0" EstimateCPU="2.9e-09" AvgRowSize="11" EstimatedTotalSubtreeCost="0.00413315" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1009"></ColumnReference><ColumnReference Column="Recr1005"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="19857" Batches="0" ActualElapsedms="328" ActualCPUms="328" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><Concat><DefinedValues><DefinedValue><ColumnReference Column="Expr1009"></ColumnReference><ColumnReference Column="Expr1006"></ColumnReference><ColumnReference Column="Expr1008"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Column="Recr1005"></ColumnReference><ColumnReference Column="Expr1000"></ColumnReference><ColumnReference Table="[#edges]" Column="head"></ColumnReference></DefinedValue></DefinedValues><RelOp NodeId="3" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="2.9e-08" AvgRowSize="11" EstimatedTotalSubtreeCost="2.9e-08" Parallel="0" EstimateRebinds="6.7" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1006"></ColumnReference><ColumnReference Column="Expr1000"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1006"></ColumnReference><ScalarOperator ScalarString="(0)"><Const ConstValue="(0)"></Const></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="4" PhysicalOp="Constant Scan" LogicalOp="Constant Scan" EstimateRows="1" EstimateIO="0" EstimateCPU="1.157e-06" AvgRowSize="11" EstimatedTotalSubtreeCost="1.157e-06" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1000"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><ConstantScan><Values><Row><ScalarOperator ScalarString="(0)"><Const ConstValue="(0)"></Const></ScalarOperator></Row></Values></ConstantScan></RelOp></ComputeScalar></RelOp><RelOp NodeId="5" PhysicalOp="Assert" LogicalOp="Assert" EstimateRows="6.7" EstimateIO="0" EstimateCPU="2.436e-07" AvgRowSize="11" EstimatedTotalSubtreeCost="0.00413199" Parallel="0" EstimateRebinds="6.7" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1008"></ColumnReference><ColumnReference Table="[#edges]" Column="head"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="19856" Batches="0" ActualElapsedms="328" ActualCPUms="328" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><Assert StartupExpression="0"><RelOp NodeId="6" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="6.7" EstimateIO="0" EstimateCPU="2.436e-07" AvgRowSize="11" EstimatedTotalSubtreeCost="0.00413199" Parallel="0" EstimateRebinds="6.7" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1008"></ColumnReference><ColumnReference Table="[#edges]" Column="head"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="19856" Batches="0" ActualElapsedms="281" ActualCPUms="281" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><OuterReferences><ColumnReference Column="Expr1008"></ColumnReference><ColumnReference Column="Recr1001"></ColumnReference></OuterReferences><RelOp NodeId="7" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="2.9e-08" AvgRowSize="11" EstimatedTotalSubtreeCost="2.9e-08" Parallel="0" EstimateRebinds="6.7" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1008"></ColumnReference><ColumnReference Column="Recr1001"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1008"></ColumnReference><ScalarOperator ScalarString="[Expr1007]+(1)"><Arithmetic Operation="ADD"><ScalarOperator><Identifier><ColumnReference Column="Expr1007"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator></Arithmetic></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="8" PhysicalOp="Table Spool" LogicalOp="Lazy Spool" EstimateRows="1" EstimateIO="0" EstimateCPU="2.9e-08" AvgRowSize="11" EstimatedTotalSubtreeCost="2.9e-08" Parallel="0" EstimateRebinds="6.7" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1007"></ColumnReference><ColumnReference Column="Recr1001"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="19857" Batches="0" ActualElapsedms="93" ActualCPUms="93" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><Spool PrimaryNodeId="1" Stack="1"></Spool></RelOp></ComputeScalar></RelOp><RelOp NodeId="9" PhysicalOp="Table Scan" LogicalOp="Table Scan" EstimateRows="1.9" EstimatedRowsRead="38" EstimateIO="0.0032035" EstimateCPU="0.0001203" AvgRowSize="15" EstimatedTotalSubtreeCost="0.00400951" TableCardinality="38" Parallel="0" EstimateRebinds="0" EstimateRewinds="5.7" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="[#edges]" Column="head"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="19856" Batches="0" ActualElapsedms="187" ActualCPUms="187" ActualScans="1" ActualLogicalReads="19857" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="754566" ActualEndOfScans="19857" ActualExecutions="19857" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><TableScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Table="[#edges]" Column="head"></ColumnReference></DefinedValue></DefinedValues><Object Database="[tempdb]" Schema="[dbo]" Table="[#edges]" Storage="RowStore"></Object><Predicate><ScalarOperator ScalarString="[#edges].[tail]=[Recr1001]"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Table="[#edges]" Column="tail"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Recr1001"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Predicate></TableScan></RelOp></NestedLoops></RelOp><Predicate><ScalarOperator ScalarString="CASE WHEN [Expr1008]>(100) THEN (0) ELSE NULL END"><IF><Condition><ScalarOperator><Compare CompareOp="GT"><ScalarOperator><Identifier><ColumnReference Column="Expr1008"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(100)"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Const ConstValue="(0)"></Const></ScalarOperator></Then><Else><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Else></IF></ScalarOperator></Predicate></Assert></RelOp></Concat></RelOp></Spool></RelOp></Sort></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>

Microsoft SQL Server 2005 XML Showplan

Table 'Worktable'. Scan count 2, logical reads 119143, physical reads 0, page server reads 0, read-ahead reads 2, 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 '#edges______________________________________________________________________________________________________________00000000048B'. Scan count 1, logical reads 19857, 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.