Index on HierarchyID : Handling Hierarchical data inside the database – Part3

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:

USE HierarchyTest
create clustered index IXClus_xthid on xthid(id)
select [id].ToString() as id, name from xthid


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:

USE HierarchyTest
Alter table xthid add level int
update xtHid set level =id.GetLevel()
select level,[id].ToString() as id, name from xthid


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.

USE HierarchyTest
Create clustered index IXClus_xthid on xthid(level) with Drop_existing
Select level,[id].ToString() as id, name from xthid




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


About Prince Rastogi

Prince Rastogi is working as Database Administrator at Elephant Insurance, Richmond. He is having more than 8 years of experience and worked in ERP Domain, Wealth Management Domain. Currently he is working in Insurance domain. In the starting of his career he was working on SQL Server, Internet Information Server and Visual Source Safe. He is post graduate in Computer Science. Prince is ITIL certified professional. Prince likes to explore technical things for Database World and Writing Blogs. He is Technical Editor and Blogger at He is a regular speaker at DataPlatformDay events in Delhi NCR. He has also presented some in depth sessions about SQL Server in SQL Server Conferences in Bangalore.

View all posts by Prince Rastogi →

4 Comments on “Index on HierarchyID : Handling Hierarchical data inside the database – Part3”

  1. There are two problems here, which one does not suffer if one has a Relational database. There is no need to implement Level as a column. That is (a) hard-coding and (b) breaches Normalisation, which results in an Update Anomaly.
    1.Every time a level is added, the Level column in all the rows in the entire table has to be updated. Fine for an example, but not so fine for a real table with millions of rows.
    2.SQL is a Data Manipulation language, it does have limitations, but that does not mean that we should not use it properly. You can do everything you require in a single SELECT. The RM states explicitly, that we should not rely on physical ordering; and SQL has an ORDER BY to service our needs. What is wrong with this (assuming you eliminate the Level column and the clustered index for it):

    — Vertical or “depth first search”
    SELECT [id].ToString() as id,
    FROM xthid
    ORDER BY [id].ToString()
    — Horizontal or “breadth first search”
    SELECT [id].GetLevel(),
    [id].ToString() as id,
    FROM xthid
    ORDER BY [id].GetLevel(),

    As I stated in the comment on Part 1, it is most important that you understand the data. With that understanding, you can build and navigate a tree, regardless of platform, you do not need funny new datatypes; Without that understanding, no amount of new datatypes and functions will help. And you will end up with Update Anomalies.


    Derek Asirvadem

  2. @ Derek,

    As I said in the comment in Part 1, let’s some some code that supports your position.

  3. @user201713

    As I said in Part 1, and above, you need formal education, not code segments. Code segments without the formal education that provides the understanding is meaningless, and leads to a barrage of further questions.


  4. There is more detail re the Level being totally incorrect, in the commentary in Part 1.


Leave a Reply

Your email address will not be published.