With earlier version before 2008 of the SQL server, When we need to required hierarchical data at that time we were using the loop to get the data of parent - child hierarchy. And it was make code lengthy , 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 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.

 
 Hierarchical data can get and this indexed in two ways.

1. Breath First Strategy : 

It traverse 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, below screen shot will very clear us.

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