Skip to main content

HierarchyId data type overview - SQL Server 2008

With earlier version before 2008 of the SQL server, When we needed to required hierarchical data at that time we were using the loop to get the data of parent - child hierarchy. And it was made code long, very complex and costly as well.

What should be other feature and alternative for above?
It is HierarchyId data type, introduced by SQL Server 2008. It is a really amazing feature and very useful to avoid above such scenario. It is really used to reduce the code complexity and better performer.

There are some introduction of HierarchyId data type and as following,
1. It’s a new CLR data type.
2. Stored as varbinary.
3. Can create an Index on that.

Now there are some methods which can be used with this feature.

1. GetRoot() : Return root node of the hierarchy tree
2. ToString() :  To get string representation of the hierarchy node.
3. Parse() : Get conversation from string representation to hierarchy node.
4. GetLevel() : Get level  depth of hierarchy node.
5. GetAncestor(n) : Get nth ancestor of the hierarchy node.
6. IsDescendant() : Returns of true/false if child node is descendant of hierarchy node.
7. GetDescendant () :  Get the child nodes of hierarchy node.
8. Reparent() : Move a node of hierarchy to new location.

Let’s look on the below hierarchy  where you can see the structure of it.


(Click on image to enlarge)
Hierarchical data can get and this indexed in two ways.

1. Breath First Strategy : It traverses the hierarchy from level by level means in breath first. Look on the screen shot below which give more idea.

2. Depth First Strategy: Traverse in depth node first as mentioned in below image.

I will future posts the details of the every method and functions with HierarchyId feature.


  1. Wow!!! Micro$oft just re-invented IMS!


Post a Comment