SQL 2008 HierarchyID with Multiple Root Nodes
Asked Answered
P

7

31

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 ?

Phenacaine answered 30/8, 2009 at 1:8 Comment(0)
D
9

Yes, you are reading right - using the HierarchyID allows only one single root node. That's the way it is and there's no way around it, as far as I know, short of introducing an artificial new "über-root" which serves no other purpose than to allow you to have several first-level "sub-root"....

Marc

Update: as Greg (@Greg0) has pointed out - this answer is actually not correct - see his answer for more details.

Diatomaceous answered 30/8, 2009 at 8:36 Comment(6)
This is not correct. See my answer for details.Holleyholli
@ScottMunro: See my comment to your answer. I believe Marc is correct. However, you are free to use root's children as root-nodesPeatroy
@Phil, You are not actually required to have an "uber-root" node. You can start a hierarchy with a node such as '/1/' and have a second such as '/2/' without having to have '/' present above them.Holleyholli
@ScottMunro: I know and thanks for bringing this up. Yes, there is no requirement to have a root node in a database when using hierarchyId type. However, all hierarchyIds reference it. For example, "/1/" is {root, 1}. Root need not exist as hierarchyId does not enforce presence of referenced node.Peatroy
I'm currently trying this approach of ignoring the root node and effectively treating the 1st level items as root nodes. I know they are technically children of the root (which, as Phil pointed out, need not exist), but as far as my application is concerned they are their own roots.Pedigo
@marc_s, with all due respect, this answer is not correct - see my example below.Snotty
S
34

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

Stelu answered 3/9, 2009 at 6:19 Comment(2)
This is great, thanks. It seems like whether you create the root / or not is not really important, as long as you disregard it, or consider it 'all the hierarchies'. I'm hoping that this also allows the same children to appear in multiple hierarchies, although I suspect that will involve duplicating the children.Umbrage
You do not appear to have any constraints here to enforce the tree - ensure that you don't have any orphan records pointing to parents that do not exist. See my answer for more details.Holleyholli
T
11

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 
Tallboy answered 7/9, 2010 at 20:9 Comment(0)
D
9

Yes, you are reading right - using the HierarchyID allows only one single root node. That's the way it is and there's no way around it, as far as I know, short of introducing an artificial new "über-root" which serves no other purpose than to allow you to have several first-level "sub-root"....

Marc

Update: as Greg (@Greg0) has pointed out - this answer is actually not correct - see his answer for more details.

Diatomaceous answered 30/8, 2009 at 8:36 Comment(6)
This is not correct. See my answer for details.Holleyholli
@ScottMunro: See my comment to your answer. I believe Marc is correct. However, you are free to use root's children as root-nodesPeatroy
@Phil, You are not actually required to have an "uber-root" node. You can start a hierarchy with a node such as '/1/' and have a second such as '/2/' without having to have '/' present above them.Holleyholli
@ScottMunro: I know and thanks for bringing this up. Yes, there is no requirement to have a root node in a database when using hierarchyId type. However, all hierarchyIds reference it. For example, "/1/" is {root, 1}. Root need not exist as hierarchyId does not enforce presence of referenced node.Peatroy
I'm currently trying this approach of ignoring the root node and effectively treating the 1st level items as root nodes. I know they are technically children of the root (which, as Phil pointed out, need not exist), but as far as my application is concerned they are their own roots.Pedigo
@marc_s, with all due respect, this answer is not correct - see my example below.Snotty
S
8

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.

Snotty answered 18/9, 2018 at 20:7 Comment(0)
H
7

The hierarchyid data type that can be used to represent a position in a hierarchy. It does not inherently enforce the hierarchy though. This is an extract from the MSDN documentation for hierarchyid.

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.

This example shows how a combination of a computed column and a foreign key can be used to enforce the tree.

CREATE TABLE Org_T3
(
   EmployeeId hierarchyid PRIMARY KEY,
   ParentId AS EmployeeId.GetAncestor(1) PERSISTED  
      REFERENCES Org_T3(EmployeeId),
   LastChild hierarchyid, 
   EmployeeName nvarchar(50)
)
GO

In your case, you would modify the computed column formula so that for root records either Null (foreign keys are not enforced for Null values in SQL Server) or perhaps the unmodified hierarchyid of the record (roots would be their own parents) would be returned.

This is a simplified version of the above example which goes with the strategy of assigning root nodes a null ParentId.

create table Node
(
    Id hierarchyid primary key,
    ParentId AS case when Id.GetLevel() = 1 then 
                    Null 
                else 
                    Id.GetAncestor(1) 
                end PERSISTED REFERENCES Node(Id),
    check (Id.GetLevel() != 0)
)

insert into Node (Id) values ('/1/');
insert into Node (Id) values ('/1/1/');
insert into Node (Id) values ('/'); --Fails as the roots will be at level 1.
insert into Node (Id) values ('/2/1/'); --Fails because the parent does not exist.

select Id.ToString(), ParentId.ToString() from Node;

Only the valid inserts from above succeed.

Id ParentId

/1/ NULL

/1/1/ /1/

Holleyholli answered 25/4, 2014 at 10:16 Comment(1)
I believe Marc is correct in a technical-sense and you are in a semantic-sense. Marc is right in that you can have only one root node (/). That said, nothing is stopping you from calling the root node's children "roots" or "über-root" (as Marc calls them) and using those as the roots (as you did).Peatroy
H
2

can't you just have one, 'non-displayed' root and have all main articles at level 1?

Hairsplitting answered 30/8, 2009 at 19:40 Comment(0)
C
0

Your answer is no and yes at the same time and it depends to your purpose of using HierarchyID

HierarchyID is created for being a UNIQUE IDENTITY but You do not have to using it as UNIQUE IDENTITY

NO: If you use it as UNIQUE IDENTITY, You don't need another field or data to find childs or parents of a record but Every HierarchyID must be unique so You can have only one root

YES: If you don't use it as UNIQUE IDENTITY, You need another field or data to find childs or parents of a record but In this situation you can have multi HierarchyID with the same value so You can have multi roots (See the (Greg Gum)'s Answer for detail)

using HierarchyID as UNIQUE IDENTITY has many advantage as belows :

  1. You can create Clustered Index on it and increase your performance
  2. Clustered Index on one field is faster than Clustered Index on multi fields and needs less storage
  3. Some Additional Fields can be omitted and you save the storage
  4. You do not have to have first Level root in your records and you can skip it and start from level 2 (See the Jeremy's Answer for detail)
Childbirth answered 5/3 at 15:0 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.