SQL Server Hierarchial Data (HierarchyId)

   HierarchyId is a built-in data type, It is optimized for representing trees. It is not a new data type, It has been in SQL Server since the version 2008. It's very powerful and it can make your life easy if you are dealing with hierarchy.

Let's say, we have a grocery store with many departments. We want to be able to list the products in our store by department. I am going to use the hierarchyid to do this. First I am going to create a lookup table for our departments.

Now we have some data we can start to query with hierarchyid functions. Let's look at the most common hierarchyid function.

IsDescendantOf(parent hierarchyid)

This should give us the all the departments under Pharmacy department.

GetLevel()

This will give us the Level of the hierarchy

ToString()

You can use this function to see the hierachyid in string format

GetAncestor(level)

You can list the ancestors with this function.

Let's see which sub departments we have under Groceries.

Now, we can make this little bit better. Let's create another table. We are going to store the products and their locations in this table.

Now we can use the same hierarchyid functions to find where the products are. Let's see what do we have in our Drinks department. Do we need to order any juices?




Current EntityFramework does not support hierarchyid, you can use this link to get the EntityFramework with hierarchyid support. This package adds all the hierarchyid functions to .NET environment.