Recursive Common Table Expressions are solving a lot of problems for me at work today, so I thought I'd throw a note up on here about them. Its a common problem with databases that records often have a parent record in the same table, which can in turn have another parent and so forth up the chain. What happens is that at some point you need to find out the whole structure of the tree, which becomes much more difficult. Enter RCTE. While I've known that other servers had capabilities like this for a while (Oracle for example), I'd not seen a good way to do it in SQL Server.
Find out a little more at the bottom of this page.
And an official Microsoft page can be found here.