Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 4 down vote accepted

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).

share|improve this answer
+1 How are you ensuring the integrity of your data? Can you use constraints to make sure there are no orphans? – A-K May 8 '12 at 16:10
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. – Kirk Broadhurst May 8 '12 at 23:28
I started answering your question 'why not use it' in a comment, but that was kind of too big, so I moved that to a separate answer. – A-K May 10 '12 at 20:31

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."

share|improve this answer

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.

share|improve this answer
Jack, how big are your tables? How did you choose to use HierarchyId over its alternatives? – A-K May 1 '12 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. – Jack Corbett Apr 25 '13 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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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