The HierarchyID data type has a lot of that kind of functionality. I built this solution in SQL 2000, and then in SQL 2005, where that wasn't available.

I've found that nested sets hierarchies still perform much better (in selecting) than the hierarchy ID data type does. Updates are still fastest in adjacency hierarchies, since they usually only involve one row. Deletion speed is fastest in nested sets, second fastest in adjacency, and slowest in hierarchy ID. Additions are fastest in either adjacency, or in a padded nested sets hierarchy (equally fast in either), except in cases where the nested sets version requires resizing or moving any ranges, and are slowest in a hierarchy ID, except when adding one row to the bottom of a chain.

So, even in SQL 2008, consider a few things before just going with the hierarchy ID data type, instead of nested sets (which is best if you can get past the issues with frequent changes), or a hybrid system like this. Adjacency should only be used if you absolutely have to. It's a performance killer except on very small data sets or very shallow hierarchies. (It's hard to argue against it on a family tree, for example, while you're building it. But for just about anything else I've run into, one of the others will be better.)