Sean Lange (3/18/2013)
Of course a CTE can use tempdb. Just like a temp table, if the memory pressure is too much it will absolutely start writing data to tempdb.
I saw a server stopped operating because it's run out of memory, while its tempdb was basically empty.
At the moment I fixed the problem by changing CTE's to DT's.
But probably I did something else to improve the queries.
Will need to check later.
But anyway - even if you're right and CTE's can use tempdb - it makes them totally equal to DT's.
No advantage to any method, except for that extra line of code required by CTE syntax.
Do you agree with me on this?