Back To Blog

SQL Server interview questions and answers: -What is the use of 'hierarchyid' in SQL Server?

By Shiv Prasad Koirala in SQL
Mar 25th, 2013
6626
0

This article is extracted from the SQL Server interview question book published by BPB publication and written by ShivprasadKoirala. You can read about the book by clicking on this link - http://www.flipkart.com/sql-server-interview-questions-1/p/itmdyuqz2a6tzhjw

Many times we need to store data which has a deep tree structure hierarchy. Below is a simple example which represents asales team organization hierarchy. Now if you want to get all people who work under "Shaam" you will really need to work hard on both database design and logic.

In the past many developers used to create database design using self-reference primary and foreign key relationship. You can see in the below figure we have a simple table which stores sales person. Every sales person is marked with a primary key called as "SalesId". We have one more column called as "Salesid_fk" which references the primary key "SalesId".

Now to establish the tree structure hierarchy we need to enter data in a linked list format. For instance you can see in the below data entry snapshot. The first row "Shiv" indicates the top sales person in the hierarchy. Now because "Shiv" is the top sales person in the hierarchy the "SalesIdFk" value is null. "Raju" and "Shaam" report to "Shiv", so they have "Salesidfk' value as "1" which is nothing but primary key value of "Shiv". Using this approach we can represent any deep hierarchy.

This approach is great but it needs cryptic DB design and some complicated logic to process data. For instance if I want to get how many people work under "Shaam" . I really need to write some complicated recursive logic at the backend.

So here's a good news, SQL Server has support of hierarchy data type which can accommodate such complex tree structure.

So first step is to get rid of "SalesIdfk" column and add "SalesHid" column which is of datatype "Hierarchyid".

Now the HID (Hierarchy id) data type column uses the below format to represent tree structure data:-

All data in the HID column should start with "/" and end with "/".
The top level root is represented by "/".
The next level below the root is represented by "/1/". If you have one more person on the same level you will enter it has "/2/".
If you want to enter one more child below "/1/", you need to enter "/1/1/".

Below is a pictorial representation of how HID values map with the tree structure levels.

Now if you want to search who are below "Shaam". You can fire the below query. The "IsDescendantOf" function evaluates to true if the records are child's of that level. This is easy as compared to creating a custom design and writing logic yourself.