It's an oldie but a goody, but if you've ever had the need to do any sort of performant querying on branches of a tree, take a look at my article on SQL Hierarchies. I've gotten a lot of great feedback regarding it, and it still seems to be serving a lot of people's needs.

Of course SQL Server 2005 brings Common Table Expressions, which offers a syntactically easier method of querying hierarchies, but it still relies upon recursive looping to build its set, whereas the technique I detail can build a response set using a very high performance index backed operation.