Tao:

The clarity thing is totally a personal preference. To me, being able to look at the query at the end, especially in things like complex updates, and have that query be only a couple of lines, is more clear than even a well-formatted derived table. But that's for me. I can read either one, I just find the CTE easier. And, as you mention, that's mainly because I've been using them for a while now.

The other point, using them for consistency, is where I think it really matters. If you use them at all, use them as exclusively as possible. Same as any other standard.

fenomenales:

In the example you give, no, there isn't a clear advantage that I'm aware of for the CTE over the temp table.

As with derived tables, the real difference is when you want one that's recursive (like a hierarchy or bill of materials).

Temp tables, like CTEs and unlike derived tables, can be referenced more than once in a query. Temp tables also have the advantage that they can be referenced in more than one query, unlike both CTEs and derived tables. That's significant in many cases.

CTEs, like temp tables, table variables, and derived tables, all reside in memory till they get too big, then they get dumped into the tempdb. (I've tested all of these and they do. You should have seen the 200 Gig tempdb I ended up with from one runaway test. Which is why I do that testing on a desktop box instead of a production server.:) ) So there's no advantage of one over the other in that regard.

Temp tables can also generate statistics, and can be indexed. Again, that can be an important advantage.

Temp tables, on the other hand, aren't recursive. They also require more code to set up, and can easily result in procs that have to recompile every time they are run. (In either the case of mixing DDL and DML, or the case of running a cursor on a temp table, the proc will have to recompile every time it's called. This results in a compile lock for every run, which can result in serious delays for user queries.) CTEs have the advantage there.

It's all a matter of knowing which tool to use for which job.

Use a temp table if more than one query in a proc/script will reference the data. (A table variable works the same way, but table variable vs temp table is a whole discussion all by itself.) Use a CTE if it will be recursive, or only referenced once. Use a CTE to populate a temp table if you need both recursion and multiple references. And so on.