Kevin Wilkie talks common table expressions:
Most of our coding these days has Common Table Expressions, also known as CTEs. If not, you’re either working on an older version of SQL Server or you haven’t been introduced to this piece of goodness.
CTEs can make reading SQL queries a lot easier if the logic is convoluted. For example, let’s use the following in a CTE.
I’ll admit that I probably over-use common table expressions, but I like them more than sub-queries—I find them easier to read, and if they’re going to perform as well (or poorly) as sub-queries, I’d might as well use the form that makes more intuitive sense to me.