Click here to Skip to main content
Click here to Skip to main content

Hierarchy ID in SQL Server

, 8 Mar 2014
Rate this:
Please Sign up or sign in to vote.
Using Hierarchy ID in SQL Server

Introduction

SQL Server 2008 has a new data type HierarchyID to store hierarchical data. This tip gives a good step by step introduction about this data type.

Step by Step Tutorial

HierarchyID data type was introduced in SQL Server 2008. It is a CLR data type. First, we will create a table to store our hierarchical data.

CREATE TABLE H (
   Node HierarchyID PRIMARY KEY CLUSTERED,
   NodeLevel AS Node.GetLevel(),
   ID INT UNIQUE NOT NULL,
   Name VARCHAR(50) NOT NULL
 ) 

Node is the column which has the HierarchyID type, NodeLevel is a calculated column which has the level of a particular node. ID and Name are custom columns for additional information.

HierarchyID data type maps the data as a tree, so when traversing the tree structure, 2 types of methods could be possible:

  1. Depth First
  2. Breadth First

In order to make the query execution efficient, we create indexes; in HierarchyID also, we create indexes in order to make the above mentioned traversal efficient. Since we have a clustered index defined in the Node column, we get Depth First index by default. If you want to have a Breadth First index, execute the following query:

 CREATE UNIQUE INDEX bfs_index
   ON H (NodeLevel,Node)  

In real scenarios, you do not need to create both indexes, it depends on your requirement to decide which index to be created. Sometimes, you may need both as well.

Insert the Top Node.

INSERT INTO H (Node, ID, Name)
   VALUES (HierarchyId::GetRoot(), 1, 'Thuru') 

Here we use the HierarchyId::GetRoot() as the value for the Node. Few things to note here, we ask the SQL Server to give us the HierarchyID of the root node. We use the SQL CLR function GetRoot for this. The :: is marked because GetRoot is a static method inside the HierarchyID SQL CLR data type.

Execute the following SELECT statement. Here NodeText is the string representation of our Node value.

SELECT Node.ToString() AS NodeText, * FROM H 

Let's add a child node to our root node. We use GetDescendant method on the node to which we add child nodes. GetDescendant method takes 2 arguments indicating the left and right nodes on the child level respectively. As of now we have only the top node, it does not have any children nodes. So obviously, no left or right nodes in the child level. So we pass NULL.

DECLARE @parent HierarchyId = HierarchyId::GetRoot()
INSERT INTO H (Node,ID,Name) VALUES (@parent.GetDescendant(NULL,NULL),2,'Johnny') 

In the above code, we get the top node value using the in build function, but we could have queried that using other known elements such as asking for the Node of the element who's ID = 1 or Name = 'Thuru'.

After the above insertion, we have the following structure defined.

Let’s add another person below ‘Thuru’. Note this time I get the parent node using the SELECT statement which asks for the Node of ‘Thuru’. And now, I want to insert the new node left side of Johnny. So for the new node, the right sibling is Johnny and the left sibling is null. The query goes like this:

DECLARE @parent HierarchyId = (SELECT Node FROM H WHERE Name = 'Thuru')
DECLARE @jhony HierarchyId = (SELECT Node FROM H WHERE name = 'Johnny')
INSERT INTO H (Node,ID,Name) VALUES (@parent.GetDescendant(@jhony,NULL), 3, 'Robert') 

This creates the following hierarchical structure:

Now it’s a simple guess for you to decide on what should be done to insert a new node between Johnny and Robert. We have the left node (Johnny) and the right node (Robert).

DECLARE @parent HierarchyId = (SELECT Node FROM H WHERE Name = 'Thuru')
DECLARE @johnny HierarchyId = (SELECT Node FROM H WHERE Name = 'Johnny')
DECLARE @robert HierarchyId = (SELECT Node FROM H WHERE Name = 'Robert')
INSERT INTO H (Node,ID,Name) VALUES (@parent.GetDescendant(@johnny,@robert), 4, 'Alex') 

Let's add some more nodes:

DECLARE @parent HierarchyId = (SELECT Node FROM H WHERE Name = 'Thuru')
DECLARE @alex HierarchyId = (SELECT Node FROM H WHERE Name = 'Alex')
DECLARE @robert HierarchyId = (SELECT Node FROM H WHERE Name = 'Robert')
INSERT INTO H (Node,ID,Name) VALUES (@parent.GetDescendant(@alex,@robert), 5, 'Niki')
GO

DECLARE @parent HierarchyId = (SELECT Node FROM H WHERE Name = 'Thuru')
DECLARE @alex HierarchyId = (SELECT Node FROM H WHERE Name = 'Alex')
DECLARE @niki HierarchyId = (SELECT Node FROM H WHERE Name = 'Niki')
INSERT INTO H (Node,ID,Name) VALUES (@parent.GetDescendant(@alex,@niki), 6, 'Steve')
GO 

Now we have the following structure defined:

Let's add 2 children nodes for 'Steve':

DECLARE @steveParent HierarchyId = (SELECT Node FROM H WHERE Name = 'Steve')
INSERT INTO H (Node,ID,Name) VALUES (@steveParent.GetDescendant(NULL,NULL), 7, 'S1')
GO

DECLARE @steveParent HierarchyId = (SELECT Node FROM H WHERE Name = 'Steve')
DECLARE @s1 HierarchyId = (SELECT Node FROM H WHERE Name = 'S1')
INSERT INTO H (Node,ID,Name) VALUES (@steveParent.GetDescendant(@s1,NULL), 8, 'S2')
GO 

Now we have the following structure:

IsDescendantOf

This function returns 'TRUE' if the passed node is a descendant of a particular mode.

DECLARE @parent HierarchyId = (SELECT Node FROM H WHERE Name = 'Thuru')
DECLARE @parentNodeLevel int = (select NodeLevel from h where name = 'Thuru')

SELECT Node.ToString() AS NodeText, *  FROM H WHERE Node.IsDescendantOf(@parent) = 'TRUE'     AND Node != @parent AND NodeLevel = @parentNodeLevel + 1
GO 

In the above query, I restricted to return only the immediate children using the NodeLevel column (@parentNodeLevel + 1). And also notice that I have opted out the parent node because IsDescendantOf function includes the parent node as well in the result.

We will get this result:

GetAncestor

As its name implies, this function returns the ancestors of a specified node in the specified level.

DECLARE @child HierarchyId = (SELECT Node FROM H WHERE Name = 'S1')
SELECT * FROM H WHERE Node = @child.GetAncestor(2) 

The following query gives the immediate parent node.

DECLARE @child HierarchyId = (SELECT Node FROM H WHERE Name = 'S1')
SELECT * FROM H WHERE Node = @child.GetAncestor(1) 

When 0 is passed as the argument, it returns the siblings:

DECLARE @child HierarchyId = (SELECT Node FROM H WHERE Name = 'S1')
SELECT * FROM H WHERE Node = @child.GetAncestor(0)  

Deletion

Deleting a node does not automatically delete the child nodes, this would result in orphaned children.

DELETE FROM H WHERE Name = 'Steve' 

Note that S1 and S2 have the NodeText /1.1.1/1 and /1.1.1/2 where there’s no /1.1.1 resulting orphaned S1 and S2.

Moving Nodes

The GetReparentedValue function is used to move the nodes to different locations:

DECLARE @newParent HierarchyId = (SELECT Node FROM H WHERE name = 'Johnny')
UPDATE H SET Node = Node.GetReparentedValue(Node.GetAncestor(1),@newParent)
       WHERE Name = 'S1'  

The above query moves S1 under Johnny.

Points of Interest

Microsoft recommends to use proper stored procedures to match our business scenario to deal with the HierarchyID thus eliminating unwanted results like orphaned children.

Conclusion

This article explains few very fundamental functions of HierarchyID data type. This is sufficient for most of the time, you can explore other additional functions in this TechNet article.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

T h u r u
Software Developer Pyxle Private (Ltd)
Sri Lanka Sri Lanka

Comments and Discussions

 
QuestionDid you test this? PinmemberDewey8-Mar-14 14:56 
AnswerRe: Did you test this? PinmemberThurupathan8-Mar-14 22:07 
QuestionPerformance PinmemberBenny S. Tordrup8-Mar-14 6:42 
AnswerRe: Performance PinmemberThurupathan8-Mar-14 22:11 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140721.1 | Last Updated 8 Mar 2014
Article Copyright 2014 by T h u r u
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid