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.
- Depth First
- 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.
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.
Now we’ll insert data.
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.
Now I insert a person in the next level.
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.
Now it’s a simple guess for you what should be done to insert a new node between Johnny and Robert.
Executing couple of inserts ….
Now let’s add 2 children nodes for Steve. Execute the following query.
IsDescendantOf method and GetAncestor methods are useful in querying the structure.
The following query returns the immediate set of nodes of the specified node.
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.
Gets the ancestors of the specified node in the passed level, if we pass 0 it returns the siblings.
2 steps above.
Deleting a node does not delete its children nodes. This results in orphaned children.
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.
The GetReparentedValue method is used to move the nodes to different locations.
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.