Is anybody using HierarchyId in real production with tables of reasonable size, more than a few thousand rows? Is it reliable/performant? So far I have not found anyone not affiliated with the vendor recommend it, and Paul Nielsen advises against it here.

What is your experience with using HierarchyId in actual production systems?

Which criteria have you used when you were choosing HierarchyId over its alternatives?

5 Answers 5


I've implemented HierarchyID and found it to provide good performance and easy to use.

I've used it on relatively small datasets (tens of thousands of rows) with hierarchy up to 10 branches deep.

Why use it? The HierarchyID type provides a number of helper methods (such as IsDescendantOf) that make your job easier than rolling your own materialized path.

Paul Nielsen's comment over on StackOverflow is confusing to me - the HierarchyID is a materialized path. I'm more inclined to agree with this comment below his answer.

A better question might be 'why not use it'. It's easy to use, provides a lot of functionality that you'd otherwise be writing for yourself, and performs well (in my limited tests).

  • +1 How are you ensuring the integrity of your data? Can you use constraints to make sure there are no orphans?
    – A-K
    Commented May 8, 2012 at 16:10
  • 3
    From memory you can. You would use a function across the HierarchyID to determine the parent value, create a persisted computed column for that value, and then apply a FK constraint between that value and the parent. Commented May 8, 2012 at 23:28

This is an answer to Kirk's question 'why not use it (HierarchyId)'. As compared to materialized path, in some important cases HierarchyId seems to be both less performant and less convenient to work with.

The reason is simple: quoting from Microsoft comment on Connect, "The problem is that CLR calls, including hierarchyID's methods, are opaque to the query optimizer. This is by design. However, it means that the cardinality estimate for them can sometimes be quite wrong."

On the other hand, implementing materialized path is very easy the first time we need to do it, and next time it is essentially a copy-and-paste task. So, we get a more versatile and better performing solution with very little effort.

So I completely agree with Paul Nielsen, who wrote in his excellent book entitled "Microsoft® SQL Server® 2008 Bible" as follows: "The new HierarchyID is not without controversy. It’s new and gets plenty of press and demo time, but I’m not sure it’s a problem that needed another solution."


My company uses HeirachyID in direct sales, multi-level marketing software. It works. I haven't really done any work with it I just know we are using it.

The biggest problem I've seen with it is that we are iterating through the levels in a looping fashion instead of being more set-based. In that area it doesn't perform real well for us, but I'm not sure if that is a problem with the type or our implementation of it.

  • Jack, how big are your tables? How did you choose to use HierarchyId over its alternatives?
    – A-K
    Commented May 1, 2012 at 15:36
  • Since I didn't have email notification enabled I never saw this comment. Our tables are in the hundreds of thousands currently not millions. I was not with the company when the decision to use HierarchyID was made, so I'm not sure why it was chosen, other than it was the new way at the time. Commented Apr 25, 2013 at 15:20

One problem with hierarchyid is you get vendor lock-in. But I did find a great article by Adam Milazzo about how everything works internally:


With this I was able to write a Postgres script to convert my data set over from MSSQL. Also included it in a script I wrote to import the AdventureWorks database into Postgres:


Just search for "hierarchyid" in the install.sql file there and you'll soon find references to converting it.


Our team has implemented it in production, at first the performance is good, after 2 years, the table now contains 430,000 rows and getroot and getdecendent takes 3 seconds, both of them are required for calculating the next Id value for inserting record. Now a single subtree insertion takes about 16 seconds which is not acceptable at all.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.