How to insert a new record into a table with hierarchyid

Question

I’m learning how to deal with hierarchical data using hierarchyid. Have read a tutorial. The demo data is composed of precalculated hierarchyids. I’m familiar with parent/child tables using IDENTITY (1, 1) in the primary key. I understand that I must provide the value of the hierarchyid. But do not know how to do that. How can I obtain the value for the hierarchyid for a new record without parent and for a new record with a parent (given that I have the hierarchyid of the parent)? Thank you very much.

Answer 1

As Microsoft states it here: "It is up to the application to generate and assign HierarchyId values in such a way that the desired relationship between rows is reflected in the values."

HierarchyId always has a parent, the top branches have a root as a parent.

Some examples with GetDescendant:

declare @Parent hierarchyid = 0x; -- root
    
print @Parent.GetDescendant('/1/', '/2/').ToString()     -- /1.1/
print @Parent.GetDescendant('/1/', '/1.1/').ToString()   -- /1.0/
print @Parent.GetDescendant('/1/', '/1.0/').ToString()   -- /1.-1/
print @Parent.GetDescendant('/1/', '/1.-1/').ToString()  -- /1.-2/
    
print @Parent.GetDescendant('/1.1/', '/2/').ToString()   -- /1.2/
print @Parent.GetDescendant('/1.2/', '/2/').ToString()   -- /1.3/
print @Parent.GetDescendant('/1.3/', '/2/').ToString()   -- /1.4/
    
print @Parent.GetDescendant('/1.3/', '/1.4/').ToString() -- /1.3.1/
    
print @Parent.GetDescendant('/1.2.3.4.5.6.7.8/', '/1.2.3.4.5.6.7.9/').ToString() -- /1.2.3.4.5.6.7.8.1/
    
    
-- by the way
    
declare @Hid hierarchyid = '/1.2.3.4.5.6.7.8.1/';
select @Hid; -- 0x63A08A49A85258
    
declare @Hid hierarchyid = '/-1.-2.-3.-4.-5.-6.-7.-8.-1234567890/';
select @Hid; -- 0x41F8F87A3C1D8E87216D9A81A73A
    
    
    -- special cases with null
    
print @Parent.GetDescendant(null, null).ToString()    -- /1/
print @Parent.GetDescendant('/1/', null).ToString()   -- /2/
print @Parent.GetDescendant(null, '/1/').ToString()   -- /0/

So, to generate a new HierarchyId for your new record, you have to take a parent (or root), use GetDescendant function and indicate after which child or between which two children you want to add it.