hierarchyid IsDescendantOf: A Node Includes Itself

hierarchyid IsDescendantOf includes the tested node itself in its subtree. I check that self case before using the method to request strictly lower descendants.

Read the method as subtree membership

IsDescendantOf returns a bit. The tested parent is considered its own descendant. That convention describes membership in the subtree rooted there. It differs from everyday wording that can imply only lower nodes.

The child value invokes the method with the parent value as its argument. Reversing those values changes the question. The script keeps both path strings beside the result. That makes the direction of the test visible.

I’d decide whether the consumer needs the branch root included. A branch summary often does. A list of strict subordinates may not. The same method can be used with an additional exclusion when the required contract calls for strict descendants.

Compare self and neighboring branches

The first case compares a node with itself. The next case uses a child beneath that node. A sibling branch supplies a negative case. Those inputs distinguish inclusion from merely sharing the same depth.

Another case tests a child against the hierarchy root. It belongs to that subtree. Reversing the relationship tests the root against a lower node and returns false. The two directions remain separate rows rather than an unexplained Boolean result.

The sixth case has a missing tested parent. Its result is missing rather than false. The example retains that input and output. Treating an unknown branch as a known nonmember would introduce another policy.

WITH Inputs AS
(
    SELECT Id, CAST(ChildPath AS nvarchar(40)) AS ChildPath,
           CAST(ParentPath AS nvarchar(40)) AS ParentPath
    FROM (VALUES (1, N'/1/', N'/1/'), (2, N'/1/2/', N'/1/'),
                 (3, N'/2/', N'/1/'), (4, N'/1/', N'/'),
                 (5, N'/', N'/1/'), (6, N'/1/', NULL))
        AS v(Id, ChildPath, ParentPath)
)
SELECT i.Id, i.ChildPath, i.ParentPath,
       n.ChildNode.IsDescendantOf(n.ParentNode) AS InSubtree
FROM Inputs AS i
CROSS APPLY (VALUES (hierarchyid::Parse(i.ChildPath),
    hierarchyid::Parse(i.ParentPath))) AS n(ChildNode, ParentNode)
ORDER BY i.Id;

Exclude self only when the contract requires it

A strict-descendant filter can combine subtree membership with inequality to the branch root. That exclusion has a clear reason. It shouldn’t be added just because the function name sounded narrower. State the required record set first.

I can justify retaining the branch root in a report that summarizes all records under one location. Excluding it could omit work assigned directly to that node. Another report may deliberately show children only. Neither choice is a universal interpretation of the data.

The literal paths express logical locations without storing rows. A matching relationship doesn’t prove those paths belong to valid application records. It also doesn’t enforce unique paths. A real hierarchy table needs its own integrity contract.

Keep relationship and ordering separate

Membership doesn’t specify presentation order among the matched nodes. A report requiring hierarchy traversal must request the intended sort. A different stable display might use names or identifiers. The relationship test alone supplies neither ordering rule.

The complete script reads six comparisons through a CTE and parses each side explicitly. It creates no objects or session settings. ORDER BY fixes the case sequence. Both original paths remain visible with the native bit result.

Compare every ordered tuple and SQL type across the selected subtree relationships. Keep the self case, reversed root relationship and NULL parent. Read each child beside the supplied parent path. Checking only the positive child case would miss the method’s inclusive subtree contract.

Decide first whether the branch root belongs in your report.

Subtree membership is not strict descent, it is an inclusive relationship that also contains the branch root.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.