I had a fairly straightforward SQL problem to solve the other day. Given a child record, go find the top most parent (i.e. it's parent, and then go find its parent until you hit the top most record). This is a typical data design pattern if you ever had a table of employees and wanted to create an organisational structure diagram.

One can "walk the tree" within T-SQL by recursively calling a stored procedure and passing in an identifier, and keep looping until you basically hit a NULL field value with the identifier column. However, what appears to be a more performant solution and one that's definetly more elegant is to use Common Table Expressions available with SQL Server 2005 and up.

MSDN has a straightforward article on how to use Common Table Expressions so if you ever have to need to do recursive queries in SQL Server definetly check it out.