hierarchyid GetAncestor: Stop at the Root

hierarchyid GetAncestor moves up a specified number of logical levels. I distinguish the root from a request beyond it, which returns NULL rather than another parent.

Supply a distance rather than a target depth

The argument is the number of levels to move upward. It isn’t the absolute level of the desired result. Starting from level three and supplying one therefore reaches level two. Those two numbers answer different questions.

The method returns hierarchyid. Converting that result to text makes its logical path readable. The script explicitly narrows the displayed text to nvarchar(40), which fits these selected paths. It doesn’t change the calculated ancestor.

I’d name the argument LevelsUp rather than Level when storing a requested distance. That reduces ambiguity for a caller. A target-depth input would need its own conversion to a distance. The method doesn’t infer which interpretation an application intended.

Start with one node and five distances

The demonstration parses the node /2/3/4/. It has three logical levels below the root. The distance list contains zero through four. Every case starts from the same node so that only the requested upward distance changes.

A distance of zero returns the original node. One returns /2/3/, and two returns /2/. Three reaches the root /. Four exceeds the available depth and returns NULL.

The output also records each returned node’s level. The root remains a real level-zero value. The beyond-root result has no level because it is NULL. That distinction prevents an absent ancestor from being mistaken for the root.

WITH NodeInput AS
(
    SELECT hierarchyid::Parse(N'/2/3/4/') AS Node
), Distances AS
(
    SELECT Id, LevelsUp
    FROM (VALUES (1, 0), (2, 1), (3, 2), (4, 3), (5, 4))
        AS v(Id, LevelsUp)
)
SELECT d.Id, CAST(n.Node.ToString() AS nvarchar(40)) AS OriginalPath,
       d.LevelsUp, CAST(a.Ancestor.ToString() AS nvarchar(40)) AS AncestorPath,
       a.Ancestor.GetLevel() AS AncestorLevel
FROM NodeInput AS n
CROSS JOIN Distances AS d
CROSS APPLY (VALUES (n.Node.GetAncestor(d.LevelsUp))) AS a(Ancestor)
ORDER BY d.Id;

Keep invalid distance separate from absence

A negative distance raises an exception. The script doesn’t execute that deliberate error alongside its useful results. Its inputs are nonnegative and bounded. A caller still needs to validate the permitted request range.

I can justify clamping an excessive request to the root when an application explicitly requires that behavior. That would change the beyond-root outcome. The transformation should be visible in a wrapper. This demonstration keeps the method’s NULL result.

Likewise, a missing ancestor path doesn’t establish that a table lookup failed. These values are computed without querying a hierarchy table. They describe logical locations. A separate join is required when the consumer needs stored record details.

Separate a path from a record

A valid returned path can exist as a value even if no stored ancestor row exists. GetAncestor calculates the location rather than guaranteeing a row. Application integrity rules may require those rows. The method alone doesn’t enforce them.

The script uses a read-only node CTE and a literal distance list. It creates no objects or session changes. ORDER BY fixes the five-case sequence. Original path, distance, ancestor text and native smallint level remain visible together.

Compare complete path, distance and result tuples with their SQL types. Include the zero-distance, exact-root and beyond-root cases before reuse. Keep the missing ancestor distinct from the root path. A correct immediate parent alone doesn’t establish the whole upward-distance contract.

Keep the missing ancestor apart from the root, and the path tells the truth.

An ancestor path is not a stored record, it is a logical location reached by distance.

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