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