Hierarchy data type is introduced in SQL Server 2008. It is a CLR data type. This post gives a simple step by step guide to implement HierarchyID data type

Hierarchical navigation or simply the navigation between nodes could happen in 2 ways.

1. Depth First
2. Breadth First

Having said that, we’ll start the tutorial. Create a table as follows. Node is the HierarchyID type, NodeLevel column is a calculated column which contains the level of the Node in the hierarchical tree. ID and Name are custom data type for the information we have. Let’s try to model a organizational reporting hierarchy in using the table h.

   1: create table h (

   2: Node HierarchyID primary key clustered,

   3: NodeLevel AS Node.GetLevel(),

   4: ID INT UNIQUE NOT NULL,

   5: Name VARCHAR(50) NOT NULL

   6: )

Since we’ve defined the Primary Key in the Node column, we get the Depth First index by default. To create a Breadth First index you can run the following query. In real scenarios you do not need to create both indexes, it depends on your requirement to decide which index to be created. Sometime you may need both as well.

   1: -- this creates the bfs index to the table.

   2: create unique index bfs_index

   3:  on h (NodeLevel,Node)

Now we’ll insert data.

   1: insert into h (Node, ID, Name)

   2:    values (HierarchyId::GetRoot(), 1, 'Thuru')

Here I’ve passed 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.

Mark this statement as our common SELECT statement. NodeText is the string representation of Node.

   1: select Node.ToString() AS NodeText, * FROM h

Now I insert a person in the next level.

   1: declare @parent hierarchyid = hierarchyid::GetRoot()

   2: insert into h (Node,id,Name) values

   3:     (@parent.GetDescendant(null,null),2,'Johnny')

   4: go

Here I get the parent Node value using the static function GetRoot(). GetDescendant() method returns the descendant Node value between the right and left sibling. In this case our top level node doesn’t have any children, apparently no siblings so we do pass null. After executing the above query now we have this structure.

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 in the level of Johnny and after him. So for the new node the right sibling is Johnny and the left sibling is null. The query goes like this.

   1: declare @parent hierarchyid = (select Node from h where name = 'Thuru')

   2: declare @jhony hierarchyid = (select Node from h where name = 'Johnny')

   3: insert into h (Node, ID, Name) values (@parent.GetDescendant(@jhony,null), 3, 'Robert')

Now it’s a simple guess for you what should be done to insert a new node between Johnny and Robert.

   1: declare @parent hierarchyid = (select Node from h where name = 'Thuru')

   2: declare @jhony hierarchyid = (select Node from h where name = 'Johnny')

   3: declare @robert hierarchyid = (select Node from h where name = 'Robert')

   4: insert into h (Node, ID, Name) values (@parent.GetDescendant(@jhony,@robert), 4, 'Alex')

Executing couple of inserts ….

   1: declare @parent hierarchyid = (select Node from h where name = 'Thuru')

   2: declare @alex hierarchyid = (select Node from h where name = 'Alex')

   3: declare @robert hierarchyid = (select Node from h where name = 'Robert')

   4: insert into h (Node, ID, Name) values (@parent.GetDescendant(@alex,@robert), 5, 'Niki')

   5: go

   6:  

   7: declare @parent hierarchyid = (select Node from h where name = 'Thuru')

   8: declare @alex hierarchyid = (select Node from h where name = 'Alex')

   9: declare @niki hierarchyid = (select Node from h where name = 'Niki')

  10: insert into h (Node, ID, Name) values (@parent.GetDescendant(@alex,@niki), 6, 'Steve')

  11: go

  12:  

  13: select Node.ToString() AS NodeText, * FROM h

  14: go

Now let’s add 2 children nodes for Steve. Execute the following query.

   1: declare @steveParent hierarchyid = (select Node from h where name = 'Steve')

   2: insert into h (Node, ID, Name) values (@steveParent.GetDescendant(null,null), 7, 'S1')

   3: go

   4:  

   5: declare @steveParent hierarchyid = (select Node from h where name = 'Steve')

   6: declare @s1 hierarchyid = (select Node from h where name = 'S1')

   7: insert into h (Node, ID, Name) values (@steveParent.GetDescendant(@s1,null), 8, 'S2')

   8: go

IsDescendantOf method and GetAncestor methods are useful in querying the structure.

The following query returns the immediate set of nodes of the specified node.

IsDescendantOf

   1: declare @parent hierarchyid = (select Node from h where name = 'Thuru')

   2: declare @parentNodeLevel int = (select NodeLevel from h where name = 'Thuru')

   3: select Node.ToString() AS NodeText, *  from h where 

   4: Node.IsDescendantOf(@parent) = 'TRUE' and node != @parent and 

   5: NodeLevel = @parentNodeLevel + 1

In the above query I restricted the query to return only the immediate children using the NodeLevel column. And also notice I have opted out the parent node because IsDescendantOf method includes the parent node as well in the result.

GetAncestor

Gets the ancestors of the specified node in the passed level, if we pass 0 it returns the siblings.

   1: declare @child hierarchyid = (select node from h where name = 'S1')

   2: select * from h where Node = @child.GetAncestor(2)

2 steps above.

Immediate parent

   1: declare @child hierarchyid = (select node from h where name = 'S1')

   2: select * from h where Node = @child.GetAncestor(1)

Siblings

   1: declare @child hierarchyid = (select node from h where name = 'S1')

   2: select * from h where Node = @child.GetAncestor(0)

Deletion

Deleting a node does not delete its children nodes. This results in orphaned children.

   1: delete from h where Name = 'Steve'

After deleting Steve if we execute our SELECT statement we get the following result

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

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

Moving Nodes

The GetReparentedValue method is used to move the nodes to different locations.

   1: declare @newParent hierarchyid = (select Node from h where name = 'Johnny')

   2: update h set Node = Node.GetReparentedValue(Node.GetAncestor(1),@newParent)

   3: where name = 'S1'

Here we want to move the S1 as a child node of Johnny. We get the node value of Johnny and get the ancestors in the level of where Johnny is and update the Node of S1. This moves the S1 under Johnny leaving the hierarchy like this.