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:

1_SQL_Server_Handling_Hierarchical_data_inside_the_database_Part3

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:

2_SQL_Server_Handling_Hierarchical_data_inside_the_database_Part3

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:

3_SQL_Server_Handling_Hierarchical_data_inside_the_database_Part3

You can think Breadth first search as shown below:

4_SQL_Server_Handling_Hierarchical_data_inside_the_database_Part3

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

5_SQL_Server_Handling_Hierarchical_data_inside_the_database_Part3

 

Regards

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