SQL 2008 HierarchyID with Multiple Root Nodes

user_50970 0
Viewed 12882

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 ?

hierarchyid sql-server
6 Answers

Yes you can have multiple roots.

There is no database engine restriction on multiple roots. But of course you need a discriminator when selecting. Consider the following which uses 'Division' as the discriminator:

CREATE TABLE [EmployeeOrg](
    [OrgNode] [hierarchyid] NOT NULL,
    [OrgLevel]  AS ([OrgNode].[GetLevel]()),
    [EmployeeID] [int] NOT NULL,
    [Title] [varchar](20) NULL,
    [Division] [int] not null
) ON [PRIMARY]
GO


Insert into EmployeeOrg (OrgNode, EmployeeID, Title, Division) values ('/', 1, 'Partner A', 1 );
Insert into EmployeeOrg (OrgNode, EmployeeID, Title, Division) values ('/1/', 2, 'Part A Legal', 1 );
Insert into EmployeeOrg (OrgNode, EmployeeID, Title, Division) values ('/1/1/', 3, 'Part A Legal Asst', 1 );
Insert into EmployeeOrg (OrgNode, EmployeeID, Title, Division) values ('/', 4, 'Partner B', 2 );
Insert into EmployeeOrg (OrgNode, EmployeeID, Title, Division) values ('/1/', 5, 'Partner B Legal', 2 );
Insert into EmployeeOrg (OrgNode, EmployeeID, Title, Division) values ('/1/1/', 6, 'Partner B Legal Asst', 2 );

SELECT *  
FROM EmployeeOrg  
WHERE OrgNode.IsDescendantOf('/') = 1  and Division = 1

SELECT *  
FROM EmployeeOrg  
WHERE OrgNode.IsDescendantOf('/') = 1  and Division = 2

This returns the two different hierarchies as expected.

user_4258230
Related