SQL Server HierarchyID

Hello Folks,

Do you really know what SQL Server HierarchyID is???

HierarchyID is a new feature which has been included with the release of Microsoft SQL Server 2008. It is one of the three features which are also being meant for Traversing Hierarchies.

Organizations have struggled in the past with the representation of tree like structures in the databases, lot of joins lots of complex logic goes into the place, whether it is organization hierarchy or defining a BOM (Bill of Materials) where one finished product is dependent on another semi-finished materials / kit items and these kit items are dependent on another semi-finished items or raw materials.

Well SQL Server 2008 has the solution to the problem where we store the entire hierarchy in the data type HierarchyID. HierarchyID is a variable length system data type. HierarchyID is used to locate the position in the hierarchy of the element

So there are three techniques based upon which the Hierarchies are being traversed, and each with its own pros and cons:

  • Adjacency List
  • Materialized Path
  • HierarchyID

Adjacency List pattern also called the self-join pattern. It is the traditional pattern used to model hierarchical data. The adjacency list pattern stores both the current node’s key and its immediate parent’s key in the current node row.

Materialized Path is another excellent method to store and navigate hierarchical data. It stores a denormalized, comma-delimited representation of the list of the current node’s complete ancestry, including every generation of parents from the top of the hierarchy down to the current node. For e.g., a file path can have:

C:\Users\Piyush\Desktop  

Now we will go on to discuss HierarchyID in a brief:

  • HierarchyID is a new data type targeted specifically at solving the hierarchical problem.
  • HierarchyID is a binary version of materialized path.
  • A column of type HierarchyID does not automatically represent a tree. It is up to the application to generate and assign HierarchyID values in such a way that the desired relationship between rows is reflected in the values.
  • HierarchyID is implemented as a CLR data type with CLR methods, and we don’t need to enable CLR to use HierarchyID, since it’s always in a running mode.
  • The HierarchyID will be more clear to you, if you see this example;

So we will be using a database “AdventureWorks2008R2”. Under this we have used two table, “Person.Person” and “HumanResources.Employee”.

   

The query can be written as:

SELECT E.BusinessEntityID, P.FirstName + ' ' + P.LastName as 'Name',
OrganizationNode, OrganizationNode.ToString() as 'HierarchyID.ToString()',
OrganizationLevel
FROM HumanResources.Employee E
JOIN Person.Person P
ON E.BusinessEntityID = P.BusinessEntityID

Here, the third column returns the binary data from OrganizationNode. The fourth column “HierarchyID.ToString()” uses the “.ToString()” method to convert the HierarchyID data to text.

The result can be seen as:

1_SQL_Server_HierarchyID

Here, the CEO is a root node, so it’s HierarchyID is represented just by “/”. Since “Kevin Brown” is the first node under “David Bradley”, so it is represented with a HierarchyID “/2/1/”. Likewise, it goes for every node.

The HierarchyID data type can be converted to other data types as follows:

  • Use the ToString() method to convert the HierarchyID value to the logical representation as an nvarchar (4000) data type.
  • Use Read() and Write()  to convert HierarchyID to varbinary.

Well this all about HierarchyID.

Hope you liked it 🙂

 

Regards

Piyush Bajaj

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook

   

About Piyush Bajaj

I am very passionate about SQL Server. I also did certification on MCSA – SQL Server 2012, Querying and Administering; MCTS – SQL Server 2008, Database Development; and MCTS – SQL Server 2005, Implementation & Maintenance, which helped me to get more knowledge and interest on this field.Please feel free to drop me any question online or offline, I will try to give you the best possible answer from my side.Right now I am working as a SQL Server developer in TCS. I have an experience of just 2.6 years, well I can only say that “If you have an interest and passion, experience might become a very small thing”.

View all posts by Piyush Bajaj →

4 Comments on “SQL Server HierarchyID”

  1. Very good article and presentation is easy to understand.

    Thanks Piyush for putting your efforts in that.

Leave a Reply

Your email address will not be published.