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:

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