hierarchyid Ordering: Paths Follow Depth-First Order

hierarchyid ordering follows the logical tree value rather than its rendered path text. I sort the typed node when the result needs depth-first traversal.

Choose traversal or text presentation

hierarchyid values compare in depth-first order. Nodes within a branch appear together when those values are ordered. The logical path text is another representation. Sorting that text uses string comparison rules instead.

A path component can contain more than one digit. In a binary text comparison, the characters beginning with one can precede a component beginning with two. The typed hierarchy value understands their logical ordering. That distinction can rearrange a report without changing any stored node.

I’d decide whether a consumer needs traversal or an alphabetic display. Neither is universally appropriate. The ORDER BY expression should state that choice. A path that merely looks sorted on a small input isn’t proof of the intended comparison.

Compare the same nodes twice

The literal paths include root, /1/, its child /1/2/, and siblings /2/ and /10/. The supplied case identifiers are deliberately out of traversal order. Both queries use the same values and return each identifier beside the path.

The first query orders by hierarchyid. Its expected sequence is root, /1/, /1/2/, /2/ and /10/. The first branch stays together before the next sibling. The original identifiers let readers track the reordered records.

The second query orders the path text using an explicit BIN2 collation. It places /10/ before /2/. That is a different requested comparison, rather than a broken hierarchyid result. The script retains both full ordered sets.

WITH Inputs AS
(
    SELECT Id, CAST(PathText AS nvarchar(40)) AS PathText
    FROM (VALUES (1, N'/10/'), (2, N'/2/'), (3, N'/1/2/'),
                 (4, N'/1/'), (5, N'/')) AS v(Id, PathText)
)
SELECT i.Id, CAST(n.Node.ToString() AS nvarchar(40)) AS NodePath
FROM Inputs AS i
CROSS APPLY (VALUES (hierarchyid::Parse(i.PathText))) AS n(Node)
ORDER BY n.Node;

WITH Inputs AS
(
    SELECT Id, CAST(PathText AS nvarchar(40)) AS PathText
    FROM (VALUES (1, N'/10/'), (2, N'/2/'), (3, N'/1/2/'),
                 (4, N'/1/'), (5, N'/')) AS v(Id, PathText)
)
SELECT Id, PathText AS NodePath
FROM Inputs
ORDER BY PathText COLLATE Latin1_General_100_BIN2;

Keep rendering separate from placement

ToString provides the logical representation of a hierarchyid value. It returns nvarchar(4000) natively. The script narrows these short outputs explicitly to nvarchar(40). That display conversion isn’t the first query’s ordering key.

I can justify storing a separate descriptive label for a node. A report sorted by that name might intentionally differ from hierarchy traversal. Keep the label and hierarchy value separate. Sorting one should not silently imply sorting the other.

Depth-first ordering also doesn’t enforce an application’s stored tree integrity. A path value can be present without every logical ancestor row. The example creates no tables and makes no constraint claim. It demonstrates comparison of valid literal values.

Verify complete order rather than membership

Both queries return the same five nodes. Their row counts and unordered memberships therefore match. Those checks can’t prove their ordering contracts. Compare the full ordered tuples, including the identifiers, to distinguish the requested sequences.

The text query’s explicit binary collation makes its comparator visible. A different collation can require another text-order specification. This example doesn’t claim universal lexical ordering for all environments. The typed hierarchy query uses the hierarchyid value comparison.

The complete script reads two CTEs and SELECTs without objects or setting changes. Compare both complete ordered sets with their SQL types. Keep the path identifiers beside each sequence. A cropped result missing /10/ would conceal the key difference.

Sort the node itself and the report follows the tree.

Rendered path order is not hierarchy traversal, it is a comparison of text.

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