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:

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:

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

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:

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

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.

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

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

Avatar

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 SQLServerGeeks.com. 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,
    name
    FROM xthid
    ORDER BY [id].ToString()
    — Horizontal or “breadth first search”
    SELECT [id].GetLevel(),
    [id].ToString() as id,
    name
    FROM xthid
    ORDER BY [id].GetLevel(),
    [id].ToString()

    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.

    Cheers

    Derek Asirvadem

  2. @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.

    Cheers
    Derek

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

    Cheers
    Derek

Leave a Reply to user201713 Cancel reply

Your email address will not be published. Required fields are marked *