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>

SELECT descendant.*
FROM HierarchyExample ancestor
    INNER JOIN HierarchyExample descendant
        ON descendant.Hieararchy.IsDescendantOf(ancestor.Hieararchy) = 1
WHERE ancestor.Id = 1
SELECT
SELECT
Cached plan size32 KB
Estimated Operator Cost0 (0%)
Estimated Subtree Cost0.0065705
Degree of Parallelism0
Estimated Number of Rows1
Statement
SELECT descendant.*
FROM HierarchyExample ancestor
    INNER JOIN HierarchyExample descendant
        ON descendant.Hieararchy.IsDescendantOf(ancestor.Hieararchy) = 1
WHERE ancestor.Id = 1
Nested Loops
(Inner Join)
Cost: 0%
Nested Loops
For each row in the top (outer) input, scan the bottom (inner) input, and output matching rows.
Physical OperationNested Loops
Logical OperationInner Join
Actual Execution ModeRow
Estimated Execution ModeRow
Actual Number of Rows10
Actual Number of Batches0
Estimated Operator Cost0.0000042 (0%)
Estimated I/O Cost0
Estimated CPU Cost0.0000042
Estimated Subtree Cost0.0065705
Estimated Number of Executions1
Number of Executions1
Estimated Number of Rows1
Estimated Row Size461 B
Actual Rebinds0
Actual Rewinds0
Node ID0
Output List
[fiddle_28d392807ecc4029bc554e3bc89db38c].[dbo].[HierarchyExample].[descendant].Id
[fiddle_28d392807ecc4029bc554e3bc89db38c].[dbo].[HierarchyExample].[descendant].Hieararchy
Outer References
[fiddle_28d392807ecc4029bc554e3bc89db38c].[dbo].[HierarchyExample].[ancestor].Hieararchy
Expr1002
Compute Scalar
Cost: 0%
Compute Scalar
Compute new values from existing values in a row.
Physical OperationCompute Scalar
Logical OperationCompute Scalar
Estimated Execution ModeRow
Estimated Operator Cost0.0000001 (0%)
Estimated I/O Cost0
Estimated CPU Cost0.0000001
Estimated Subtree Cost0.0032832
Estimated Number of Executions1
Estimated Number of Rows1
Estimated Row Size460 B
Node ID1
Output List
[fiddle_28d392807ecc4029bc554e3bc89db38c].[dbo].[HierarchyExample].[ancestor].Hieararchy
Expr1002
Clustered Index Seek (Clustered)
[HierarchyExample].[PK__Hierarch__3…
Cost: 50%
Clustered Index Seek (Clustered)
Scanning a particular range of rows from a clustered index.
Physical OperationClustered Index Seek
Logical OperationClustered Index Seek
Actual Execution ModeRow
Estimated Execution ModeRow
StorageRowStore
Number of Rows Read1
Actual Number of Rows1
Actual Number of Batches0
Estimated Operator Cost0.0032831 (50%)
Estimated I/O Cost0.003125
Estimated CPU Cost0.0001581
Estimated Subtree Cost0.0032831
Estimated Number of Executions1
Number of Executions1
Estimated Number of Rows to be Read1
Estimated Number of Rows1
Estimated Row Size12 B
Actual Rebinds0
Actual Rewinds0
OrderedTrue
Node ID2
Output List
[fiddle_28d392807ecc4029bc554e3bc89db38c].[dbo].[HierarchyExample].[ancestor].Hieararchy
Object
[fiddle_28d392807ecc4029bc554e3bc89db38c].[dbo].[HierarchyExample].[PK__Hierarch__3214EC07D0CB423B].[ancestor]
Seek Predicates
Seek Keys[1]: Prefix: [fiddle_28d392807ecc4029bc554e3bc89db38c].[dbo].[HierarchyExample].Id = Scalar Operator((1))
Index Seek (NonClustered)
[HierarchyExample].[IX_HierarchyExa…
Cost: 50%
Index Seek (NonClustered)
Scan a particular range of rows from a nonclustered index.
Physical OperationIndex Seek
Logical OperationIndex Seek
Actual Execution ModeRow
Estimated Execution ModeRow
StorageRowStore
Number of Rows Read10
Actual Number of Rows10
Actual Number of Batches0
Estimated Operator Cost0.0032831 (50%)
Estimated I/O Cost0.003125
Estimated CPU Cost0.0001581
Estimated Subtree Cost0.0032831
Estimated Number of Executions1
Number of Executions1
Estimated Number of Rows to be Read1
Estimated Number of Rows1
Estimated Row Size461 B
Actual Rebinds0
Actual Rewinds0
OrderedTrue
Node ID3
Output List
[fiddle_28d392807ecc4029bc554e3bc89db38c].[dbo].[HierarchyExample].[descendant].Id
[fiddle_28d392807ecc4029bc554e3bc89db38c].[dbo].[HierarchyExample].[descendant].Hieararchy
Object
[fiddle_28d392807ecc4029bc554e3bc89db38c].[dbo].[HierarchyExample].[IX_HierarchyExample_Hierarchy].[descendant]
Seek Predicates
Seek Keys[1]: Start: [fiddle_28d392807ecc4029bc554e3bc89db38c].[dbo].[HierarchyExample].Hieararchy >= Scalar Operator([fiddle_28d392807ecc4029bc554e3bc89db38c].[dbo].[HierarchyExample].[Hieararchy] as [ancestor].[Hieararchy]), End: [fiddle_28d392807ecc4029bc554e3bc89db38c].[dbo].[HierarchyExample].Hieararchy <= Scalar Operator([Expr1002])

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/

Cached plan size	32 KB
Estimated Operator Cost	0 (0%)
Estimated Subtree Cost	0.0065705
Degree of Parallelism	0
Estimated Number of Rows	1

Physical Operation	Nested Loops
Logical Operation	Inner Join
Actual Execution Mode	Row
Estimated Execution Mode	Row
Actual Number of Rows	10
Actual Number of Batches	0
Estimated Operator Cost	0.0000042 (0%)
Estimated I/O Cost	0
Estimated CPU Cost	0.0000042
Estimated Subtree Cost	0.0065705
Estimated Number of Executions	1
Number of Executions	1
Estimated Number of Rows	1
Estimated Row Size	461 B
Actual Rebinds	0
Actual Rewinds	0
Node ID	0

Physical Operation	Compute Scalar
Logical Operation	Compute Scalar
Estimated Execution Mode	Row
Estimated Operator Cost	0.0000001 (0%)
Estimated I/O Cost	0
Estimated CPU Cost	0.0000001
Estimated Subtree Cost	0.0032832
Estimated Number of Executions	1
Estimated Number of Rows	1
Estimated Row Size	460 B
Node ID	1

Physical Operation	Clustered Index Seek
Logical Operation	Clustered Index Seek
Actual Execution Mode	Row
Estimated Execution Mode	Row
Storage	RowStore
Number of Rows Read	1
Actual Number of Rows	1
Actual Number of Batches	0
Estimated Operator Cost	0.0032831 (50%)
Estimated I/O Cost	0.003125
Estimated CPU Cost	0.0001581
Estimated Subtree Cost	0.0032831
Estimated Number of Executions	1
Number of Executions	1
Estimated Number of Rows to be Read	1
Estimated Number of Rows	1
Estimated Row Size	12 B
Actual Rebinds	0
Actual Rewinds	0
Ordered	True
Node ID	2

Physical Operation	Index Seek
Logical Operation	Index Seek
Actual Execution Mode	Row
Estimated Execution Mode	Row
Storage	RowStore
Number of Rows Read	10
Actual Number of Rows	10
Actual Number of Batches	0
Estimated Operator Cost	0.0032831 (50%)
Estimated I/O Cost	0.003125
Estimated CPU Cost	0.0001581
Estimated Subtree Cost	0.0032831
Estimated Number of Executions	1
Number of Executions	1
Estimated Number of Rows to be Read	1
Estimated Number of Rows	1
Estimated Row Size	461 B
Actual Rebinds	0
Actual Rewinds	0
Ordered	True
Node ID	3