SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE dbo.HierarchyExample (
    Id INT PRIMARY KEY,
    Hieararchy HIERARCHYID NOT NULL
);

CREATE INDEX IX_HierarchyExample_Hierarchy 
ON dbo.HierarchyExample (Hieararchy);
INSERT INTO dbo.HierarchyExample(Id, Hieararchy)
VALUES
(1,  hierarchyid::Parse('/1/')),
(2,  hierarchyid::Parse('/1/1/')),
(3,  hierarchyid::Parse('/1/2/')),
(4,  hierarchyid::Parse('/1/3/')),
(5,  hierarchyid::Parse('/1/3/1/')),
(6,  hierarchyid::Parse('/1/3/2/')),
(7,  hierarchyid::Parse('/1/3/3/')),
(8,  hierarchyid::Parse('/1/4/')),
(9,  hierarchyid::Parse('/1/4/1/')),
(10, hierarchyid::Parse('/1/4/2/'));
10 rows affected

SET STATISTICS XML ON;

SELECT descendant.*
FROM HierarchyExample ancestor
    INNER JOIN HierarchyExample descendant
        ON descendant.Hieararchy.IsDescendantOf(ancestor.Hieararchy) = 1
WHERE ancestor.Id = 1


Id
Hieararchy


1
/1/

2
/1/1/

3
/1/2/

4
/1/3/

5
/1/3/1/

6
/1/3/2/

7
/1/3/3/

8
/1/4/

9
/1/4/1/

10
/1/4/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 descendant.*&#xa;FROM HierarchyExample ancestor&#xa;    INNER JOIN HierarchyExample descendant&#xa;        ON descendant.Hieararchy.IsDescendantOf(ancestor.Hieararchy) = 1&#xa;WHERE ancestor.Id = 1" StatementId="1" StatementCompId="2" StatementType="SELECT" StatementSqlHandle="0x0900D2DAB642012E1445199C1AB3AEC656620000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="2" ParentObjectId="0" StatementParameterizationType="0" RetrievedFromCache="true" StatementSubTreeCost="0.00657048" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0xA4CF338D2B398916" QueryPlanHash="0x90282902B79B31B5" 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="15" CompileCPU="15" CompileMemory="280"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1209296"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2023-12-20T14:53:21.74" ModificationCount="0" SamplingPercent="100" Statistics="[IX_HierarchyExample_Hierarchy]" Table="[HierarchyExample]" Schema="[dbo]" Database="[fiddle_28d392807ecc4029bc554e3bc89db38c]"></StatisticsInfo><StatisticsInfo LastUpdate="2023-12-20T14:53:21.72" ModificationCount="0" SamplingPercent="100" Statistics="[PK__Hierarch__3214EC07D0CB423B]" Table="[HierarchyExample]" Schema="[dbo]" Database="[fiddle_28d392807ecc4029bc554e3bc89db38c]"></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="Nested Loops" LogicalOp="Inner Join" EstimateRows="1" EstimateIO="0" EstimateCPU="4.18e-06" AvgRowSize="461" EstimatedTotalSubtreeCost="0.00657048" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_28d392807ecc4029bc554e3bc89db38c]" Schema="[dbo]" Table="[HierarchyExample]" Alias="[descendant]" Column="Id"></ColumnReference><ColumnReference Database="[fiddle_28d392807ecc4029bc554e3bc89db38c]" Schema="[dbo]" Table="[HierarchyExample]" Alias="[descendant]" Column="Hieararchy"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="10" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><OuterReferences><ColumnReference Database="[fiddle_28d392807ecc4029bc554e3bc89db38c]" Schema="[dbo]" Table="[HierarchyExample]" Alias="[ancestor]" Column="Hieararchy"></ColumnReference><ColumnReference Column="Expr1002"></ColumnReference></OuterReferences><RelOp NodeId="1" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-07" AvgRowSize="460" EstimatedTotalSubtreeCost="0.0032832" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_28d392807ecc4029bc554e3bc89db38c]" Schema="[dbo]" Table="[HierarchyExample]" Alias="[ancestor]" Column="Hieararchy"></ColumnReference><ColumnReference Column="Expr1002"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1002"></ColumnReference><ScalarOperator ScalarString="[fiddle_28d392807ecc4029bc554e3bc89db38c].[dbo].[HierarchyExample].[Hieararchy] as [ancestor].[Hieararchy].DescendantLimit()"><UDTMethod><CLRFunction Assembly="Microsoft.SqlServer.Types" Class="Microsoft.SqlServer.Types.SqlHierarchyId" Method="DescendantLimit"></CLRFunction><ScalarOperator><Identifier><ColumnReference Database="[fiddle_28d392807ecc4029bc554e3bc89db38c]" Schema="[dbo]" Table="[HierarchyExample]" Alias="[ancestor]" Column="Hieararchy"></ColumnReference></Identifier></ScalarOperator></UDTMethod></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="2" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="12" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="10" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_28d392807ecc4029bc554e3bc89db38c]" Schema="[dbo]" Table="[HierarchyExample]" Alias="[ancestor]" Column="Hieararchy"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="1" 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_28d392807ecc4029bc554e3bc89db38c]" Schema="[dbo]" Table="[HierarchyExample]" Alias="[ancestor]" Column="Hieararchy"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_28d392807ecc4029bc554e3bc89db38c]" Schema="[dbo]" Table="[HierarchyExample]" Index="[PK__Hierarch__3214EC07D0CB423B]" Alias="[ancestor]" IndexKind="Clustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_28d392807ecc4029bc554e3bc89db38c]" Schema="[dbo]" Table="[HierarchyExample]" Alias="[ancestor]" Column="Id"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="(1)"><Const ConstValue="(1)"></Const></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></ComputeScalar></RelOp><RelOp NodeId="3" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="461" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="10" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_28d392807ecc4029bc554e3bc89db38c]" Schema="[dbo]" Table="[HierarchyExample]" Alias="[descendant]" Column="Id"></ColumnReference><ColumnReference Database="[fiddle_28d392807ecc4029bc554e3bc89db38c]" Schema="[dbo]" Table="[HierarchyExample]" Alias="[descendant]" Column="Hieararchy"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="10" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="10" 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_28d392807ecc4029bc554e3bc89db38c]" Schema="[dbo]" Table="[HierarchyExample]" Alias="[descendant]" Column="Id"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_28d392807ecc4029bc554e3bc89db38c]" Schema="[dbo]" Table="[HierarchyExample]" Alias="[descendant]" Column="Hieararchy"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_28d392807ecc4029bc554e3bc89db38c]" Schema="[dbo]" Table="[HierarchyExample]" Index="[IX_HierarchyExample_Hierarchy]" Alias="[descendant]" IndexKind="NonClustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><StartRange ScanType="GE"><RangeColumns><ColumnReference Database="[fiddle_28d392807ecc4029bc554e3bc89db38c]" Schema="[dbo]" Table="[HierarchyExample]" Alias="[descendant]" Column="Hieararchy"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[fiddle_28d392807ecc4029bc554e3bc89db38c].[dbo].[HierarchyExample].[Hieararchy] as [ancestor].[Hieararchy]"><Identifier><ColumnReference Database="[fiddle_28d392807ecc4029bc554e3bc89db38c]" Schema="[dbo]" Table="[HierarchyExample]" Alias="[ancestor]" Column="Hieararchy"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></StartRange><EndRange ScanType="LE"><RangeColumns><ColumnReference Database="[fiddle_28d392807ecc4029bc554e3bc89db38c]" Schema="[dbo]" Table="[HierarchyExample]" Alias="[descendant]" Column="Hieararchy"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[Expr1002]"><Identifier><ColumnReference Column="Expr1002"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></EndRange></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></NestedLoops></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>
Id	Hieararchy
1	/1/
2	/1/1/
3	/1/2/
4	/1/3/
5	/1/3/1/
6	/1/3/2/
7	/1/3/3/
8	/1/4/
9	/1/4/1/
10	/1/4/2/