I think a cool feature of sql server 2005 and above that you don't always hear a lot about is the recursive query with the CTE. We use this a lot. In our software product we have a Bill Of Material (BOM) structure. This BOM structure starts with a parent Item Nbr and then lists which children Items make up the parent. Some of our clients have BOM structures that go quite deep. Anyway, a recursive query with using a CTE has worked really nicely to return the whole bom structure for a parent item quickly and easily.

Ben