Good article. The only question I have is on the performance testing. By making the heirarchyid column the primary key you gave that method a distinct advantage over the classic method because that is now the clustered key. How likely is it that your heirarchy is going to be the clustered key? I do not have SQL 08 so I cannot test against it, but by changing the clustered key to the manager id column you get a clustered index seek within the CTE. How would this performance compare to the heirarchyid method? Could someone who has SQL 08 check it?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question