Learn Hierarchies in SQL server using HierarchyID 

Hierarchy ID is nothing other then column which represent relational value in numeric format on which DFS (Depth First Search) & BFS (Breadth First Search) can be used
The concept is very simple, when we have to make relational structures which shows parent child level tree hierarchy, we can use hierarchyid datatype.
With hierarchy ID recursive queries can be converted into range query.
DFS (Depth First Search) and BFS (Breadth First Search) can be implemented using additional index which improves performance.
Uses .NET assembly library defined methods to provide easy maintenance.
Hierarchy ID is scalar column and is sequence of no. of var binary values.
For script follow link below:
www.techsapphir...