SQL 2008 HierarchyID with Multiple Root Nodes

Question

I wanted to use the new HierarchyID type in SQL Server 2008 to handle the page relations in a small wiki application. However It would need to have multiple root nodes since every main article/page per account would be a root node.

From what I have read the HierarchyID type only allows 1 root node per column is this correct? and is there any way to enable multiple root nodes ?

Answer 1

I've been doing some testing, and it appears you do not need a record with a root hierarchyid.

For example, normally you would one root node (level 1) and multiple childen, but you can skip the root node, having no root records, just records that start at level 2:

//table schema CREATE TABLE [Entity](     [ID] [int] IDENTITY(1,1) NOT NULL,     [Name] [varchar](50) NOT NULL     [Hierarchy] [hierarchyid] NOT NULL,  CONSTRAINT [PK_Entity] PRIMARY KEY CLUSTERED  (     [ID] ASC ) ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]  //Insert first 'root', which is technicall a child without a parent INSERT INTO [Entity]            ([Name]            ,[Description]            ,[Hierarchy])      VALUES            ('Root A'            ,hierarchyid::GetRoot().GetDescendant(NULL,NULL))   //Create the second 'root' INSERT INTO [Entity]            ([Name]            ,[Hierarchy])      VALUES            ('Root B'            ,hierarchyid::GetRoot().GetDescendant((select MAX(hierarchy) from entity where hierarchy.GetAncestor(1) = hierarchyid::GetRoot()),NULL)) 

Now if you select all rows from the table, you see:

SELECT [ID]       ,[Name]       ,[Hierarchy],        [Hierarchy].ToString()   FROM [Entity] 

ID    Name      Hierarchy  (No column name)
1     Root A    0x58          /1/
2     Root B    0x68          /2/

I'm not sure if this would be recommended practice but conceptually it allows you to have multiple roots, as long as you consider the 2nd level in the tree as the root

Answer 2

What I do to make unique root nodes is just cast your table PrimaryKey as a HierarchyId on your desired anchor records, e.g.

Given a pretend table having ArticleID | ArticleID_Parent | Hierarchy, you can tweak all "roots" to become unique like this;

UPDATE [Article] SET Hierarchy=CAST('/'+CAST([ArticleID] as varchar(30))+'/' AS hierarchyid) WHERE [ArticleID_Parent]=0 

.. then to get the "branch" of a particular root;

SELECT * FROM [Article] WHERE Article.Hierarchy.IsDescendantOf((SELECT Hierarchy FROM Article WHERE ArticleID=XXXX)) = 1