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.

f you are using larger data sets in table variables it will use tempdb to store data physically. However, a CTE always uses memory. This can be an issue in electing for CTEs as you need to ensure that you are not using larger data sets that will drain memory. The following graph shows tempdb write transactions/sec for temp tables, table variables and CTE scenarios.