Search hierarchyid by "right-most" node

Question

I'm somewhat new to the hierarchyid datatype. I'm trying to represent entities in a given hierarchy. For the sake of argument, let's say they're people in the classic "boss of" hierarchy, but it could be anything.

Originally, I came up with a structure that has a column for the entity's id and where it lies in the hierarchy. So the Id column is essentially the "right-most" node in the hierarchy.

create table #WithId
(
    Id int primary key clustered,
    hPath hierarchyid,
    sPath as hPath.ToString()
)

insert into #WithId (Id, hPath)
values
    (1, '/1/'), (2, '/2/'), (3, '/1/3/'), (4, '/1/4/'), 
    (5, '/2/5/'), (6, '/1/6/'), (7, '/2/7/'), (8, '/2/7/8/')

It occurs to me though, that as long as the value in the Id columns is the same as the "right-most" node in the hierarchy, the Id column is technically redundant.

create table #WithoutId
(
    hPath hierarchyid primary key clustered,
    sPath as hPath.ToString()
)

insert into #WithoutId (hPath)
select hPath
from #WithId

However I still need a way to quickly look up an entity and find its upstream hierarchy. With a dedicated id column, It's trivial to just search on that column

declare @SomeId int = 8

-- Easy
select hPath, sPath
from #WithId
where Id = @SomeId

But i can't figure out a good way to do that in the case where I don't have a dedicated Id column, and I need to find the row by the right-most node.

declare @AnotherId int = 8

-- This is totally hack, but functionally what I'm looking for
select hPath, sPath
from #WithoutId
where sPath like concat('%/', @AnotherId, '/')

Anyone know a good way to do this?

Technically, I don't think it's redundant, since there's no way you could break up a string like you want that would be quicker than just using an additional int column (both in terms of sargability and in terms of the cost of performing some sort of function or functions on the string). So if you need to search for a given ID, it's probably best having the additional column over using `LIKE` or some other similar solution, unless there's some reason you really can't. — ZLK, May 02 '18 at 22:18
@ZLK no big reason I can't, it just struck me as an interesting problem. Clearly doing it with string manipulation is ridiculous. Sargability however is a very good point, and probably not one easily obviated. Either way, it seems intuitively like I should be able to pull the n-th element from a hierarchyid path of arbitrary depth. — Xedni, May 02 '18 at 23:04

Vadim Loboda · Accepted Answer · 2018-05-03T17:36:22.337

You can create a calculated and persisted field for your Id:

create table #WithId
(
    Hid     hierarchyid,
    HidPath as Hid.ToString(),
    Id      as cast(replace(replace(Hid.ToString(), Hid.GetAncestor(1).ToString(), ''), '/', '') as int) persisted,

    primary key (Id)
)

Thus you normalize your data structure and will still have a good speed of search.

But as for me the hierarchyid type is useful not only to show the place in hierarchy, it also can define the order of an item within its parent children. And use the hierarchyid type just for the identity is a waste of valuable resource, I guess :)

Please read my article about this:

https://www.codeproject.com/Articles/1192607/Combination-of-Id-ParentId-and-HierarchyId

Search hierarchyid by "right-most" node

1 Answers1