Hi Friends,

Today we will see how we can implement Index on HierarchyID Column, and what benefit we can achieve by using these indexes. In My previous blogs on HierarchyID we saw how can we use HierarchyID Column as well as how can we search ancestors and descendant values for HierarchyID column. You can go through on previous blogs by using below links:

SQL Server: Handling Hierarchical data inside the database Part1

SQL Server: Handling Hierarchical data inside the database Part2

There can be two type of search on a tree structure first is Depth First Search as shown below in figure:


This is also the default behavior of HierarchyID column. In simple words we can say that it will search immediate descendant nodes first .Data is already inserted in the tables from previous blogs mention above. Now just create clustered index on id column here:


The second type of search on tree structure is Breadth First Search i.e. search will be performing on same level of nodes first. For this you have to add one more column as shown below:


You can think Breadth first search as shown below:


Now we will create clustered index on Level column here to get the benefit of BFS Search.




Prince Rastogi

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook