hierarchyid GetDescendant: Insert Between Siblings

hierarchyid GetDescendant calculates a child location relative to sibling bounds. I keep that value calculation separate from inserting a record or coordinating competing writers.

A generated location is a scalar result

GetDescendant returns a hierarchyid child of its invoking parent. Its two arguments supply lower and upper sibling bounds. NULL leaves a bound open. The method calculates a value without inserting any row.

That distinction matters when the method appears in an allocation workflow. A value calculation alone doesn’t enforce uniqueness. It also doesn’t reserve the value for one caller. Two callers supplying the same inputs can calculate the same deterministic output.

I’d review transaction and uniqueness design separately before using the method during a write. This article performs only read-only calculations over literal paths. It makes no concurrency claim. Its small examples are not a complete insertion protocol.

Keep the parent and bounds visible

All three cases use parent /3/1/. The first supplies two NULL bounds. The second supplies the first child as its lower bound and leaves the upper open. The third supplies two existing sibling bounds.

The expected generated paths are /3/1/1/, /3/1/2/ and /3/1/1.1/. The dotted path falls between its supplied siblings. It stays at the same child depth.

The output retains the original parent and both bounds. It also returns the generated path, level and subtree membership. Those columns expose whether the value remains beneath the stated parent. Text length isn’t used as a substitute for hierarchy relationships.

WITH Inputs AS
(
    SELECT Id, CAST(ParentPath AS nvarchar(40)) AS ParentPath,
           CAST(LowerPath AS nvarchar(40)) AS LowerPath,
           CAST(UpperPath AS nvarchar(40)) AS UpperPath
    FROM (VALUES (1, N'/3/1/', NULL, NULL),
                 (2, N'/3/1/', N'/3/1/1/', NULL),
                 (3, N'/3/1/', N'/3/1/1/', N'/3/1/2/'))
        AS v(Id, ParentPath, LowerPath, UpperPath)
)
SELECT i.Id, i.ParentPath, i.LowerPath, i.UpperPath,
       CAST(g.ChildNode.ToString() AS nvarchar(40)) AS GeneratedPath,
       g.ChildNode.GetLevel() AS ChildLevel,
       g.ChildNode.IsDescendantOf(n.ParentNode) AS InParentSubtree
FROM Inputs AS i
CROSS APPLY (VALUES (hierarchyid::Parse(i.ParentPath),
    hierarchyid::Parse(i.LowerPath), hierarchyid::Parse(i.UpperPath)))
    AS n(ParentNode, LowerNode, UpperNode)
CROSS APPLY (VALUES (n.ParentNode.GetDescendant(n.LowerNode, n.UpperNode)))
    AS g(ChildNode)
ORDER BY i.Id;

Give valid sibling bounds

Each non-NULL bound must be a child of the invoking parent. A lower bound must also precede the upper bound. Invalid relationships can raise an exception. The script supplies only valid pairs and doesn’t execute deliberately failing calls.

I can justify leaving both bounds NULL when choosing an initial child location. A subsequent call should use the intended neighboring bounds. Repeating the empty-bound call doesn’t mean next available child. It repeats the same deterministic value calculation.

The generated identity depends on the supplied relationship. A dotted component isn’t a newly added hierarchy level. It describes a position between siblings. GetLevel in the result keeps that distinction visible beside the text path.

Separate logical order from stored identity

An application can use a separate surrogate key for record identity while hierarchyid describes placement. Moving or placing a node is then a different concern from naming the record. This article doesn’t dictate a particular schema. It simply avoids conflating value generation with record creation.

The full script parses three literal parent-and-bound tuples. It calls the method in a SELECT and creates no objects or settings. ORDER BY fixes the case sequence. No generated value is installed into a table.

Compare all three complete tuples with their SQL types. Keep parent, bounds, generated path and depth together. Read the dotted sibling beside the interval that produced it. A generated-looking string alone doesn’t prove it falls within the required sibling interval.

Calculate the value first, then design the insert around it.

A generated child value is not an allocated record, it is a location calculated from the supplied bounds.

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