Search hierarchyid by "right-most" node

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?

Solution

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