Hi Glenn,

Thanks for the feedback.

I apologize for using the term but "It Depends". I've not tried using a HierarchyID for a SortPath column and so I don't know if it would sort correctly for what I needed to sort for. I also know it won't parse quite as easily as what I needed to split the column for.

If your ultimate goal is to use the HierarchyID instead of Nested Sets and don't require the same functionality from the column that I did, then the code will easily build your HierarchyID column and you can skip most of the rest of the code in the article. In fact, if you lookup HierarchyID in "Books Online", the method I used is the virtually identical as the example there except for the data-type of the column.

The reason why I don't use the HierarchyID data-type is simply because there doesn't appear to be a clear cut method to determine what the maximum number of nodes the data-type can hold on such large tables and I don't want to find out the hard way on production code. I know the SortPath that I used will hold 2,000 levels and that can be increased quite a bit (more than 500 million levels) by changing it to VARBINARY(MAX) with some penalty for performance.

If you do go with the HierarchyID, I'd still recommend rebuilding from an Adjacency List when there's a change because it's to troubleshoot problems in an Adjacency List (more natural for most humans) than it is for a positional HierarchyID. That's just my opinion, though.