hierarchyid GetLevel: Count the Path Depth

hierarchyid GetLevel counts the depth of a logical node, with the root at zero. I distinguish that depth from the number of rows or relatives stored in a table.

Read depth as a path property

GetLevel returns a smallint. The root of the hierarchy has level zero. A child adds one level, and its child adds another. This describes the hierarchyid value itself rather than a count over existing records.

The method takes no argument after the node. It doesn’t need to read a parent table to describe that value’s depth. The demonstration therefore uses literal parsed paths. It creates no tree records or artificial relationship constraints.

I’d keep a logical depth separate from an application’s organizational labels. Level two might mean a department in one design and a folder in another. The type doesn’t assign that meaning. An application can document a meaning for each permitted level.

Compare root, siblings and nested nodes

The script supplies six valid path strings. They include the root, two children and two grandchildren. Another child has a dotted sibling label. Each string is explicitly nvarchar(40) before parsing.

The root returns zero. Both ordinary children return one, and the grandchildren return two. The dotted child also returns one. Its longer label doesn’t create another parent-child level.

The output retains the original path and a text rendering of the parsed value. GetLevel remains its native smallint output. Keeping those columns together makes the interpretation reviewable. A text length alone isn’t a substitute for the method’s depth result.

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

Do not treat label detail as another level

A hierarchyid component can contain dots to represent ordering between siblings. The slash-delimited hierarchy level is a different concept. The example includes that distinction deliberately. Counting every punctuation character would not reproduce the demonstrated depth contract.

I can justify showing the full logical path during an investigation. A user-facing label may need a separate name instead. Neither representation changes the depth. Formatting a label can’t create or remove an ancestor from the encoded location.

A deeper value also doesn’t establish that every intermediate ancestor has a stored row. The value describes a location. Table integrity needs its own application or constraint design. This read-only scalar demonstration doesn’t claim to enforce a complete tree.

Choose the question before filtering

Filtering by depth can select records at one logical level. It doesn’t choose a particular branch by itself. Two unrelated branches can contain nodes at the same depth. A branch membership test answers a separate question.

The script uses ORDER BY Id to present its six cases in the selected sequence. It doesn’t claim that this is hierarchy traversal order. Traversal would order the hierarchyid values themselves. Keep case presentation and tree ordering distinct.

The complete SELECT changes no objects or connection options. Compare every input path, rendered value and native level type. Include both grandchildren and the dotted sibling. A matching root alone doesn’t check the path-depth distinction.

Read the path itself, and the depth tells you where a node sits.

Path depth is not a count of stored relatives, it is a property of the logical node value.

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