Anybody using HierarchyId in production? Is it reliable?

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

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.