Do CTEs use any space in tempdb or does it use memory exclusively?
I've tagged the question with both mssql 2005 and 2008 as I use both.
I'll try not to copy/paste MSDN
It doesn't matter.
A CTE is independent of query execution: it is only a language construct. Think of it as neat derived table or subquery.
This means that except for recursive CTEs (see later), all CTEs can be coded inline. If you use the CTE code once, it is for readability. If you use the CTE twice or more, then it is defensive: you don't want to make a mistake and have the derived table different each use.
Where a CTE is used twice or more, then that code will be executed twice or more. It won't be executed once and cached in tempdb.
Summary: it may or may not, just like if the code was inline.
Note: a recursve CTE is simply a derived table inside a derived table inside a derived table inside a a derived table inside a der... so same applies.
You can see this in Tony Rogerson's article. The use of tempdb would happen anyway if coded inline. He also notes that using a temp table can be better because of the "macro" expansion I explained above
FYI: the same applies to views. Just macros.