Which are more performant, CTE or temporary tables?

Question

Which are more performant, CTE or temporary tables?

Answer 1

This is a really open ended question, and it all depends on how its being used and the type of temp table (Table variable or traditional table).

A traditional temp table stores the data in the temp DB, which does slow down the temp tables; however table variables do not.

Answer 2

Temp tables are always on disk - so as long as your CTE can be held in memory, it would most likely be faster (like a table variable, too).

But then again, if the data load of your CTE (or temp table variable) gets too big, it'll be stored on disk, too, so there's no big benefit.

In general, I prefer a CTE over a temp table since it's gone after I used it. I don't need to think about dropping it explicitly or anything.

So, no clear answer in the end, but personally, I would prefer CTE over temp tables.

Answer 3

I'd say they are different concepts but not too different to say "chalk and cheese".

A temp table is good for re-use or to perform multiple processing passes on a set of data.

A CTE can be used either to recurse or to simply improved readability.

A CTE (like a view or inline table valued function) can also be treated like a macro to be expanded in the main query. A temp table is a another table.

I have stored procs where I use both...

Answer 4

CTE has its uses - when data in the CTE is small and there is strong readability improvement as with the case in recursive tables. However, its performance is certainly no better than table variables and when one is dealing with very large tables, temporary tables significantly outperform CTE. This is because you cannot define indices on a CTE and when you have large amount of data that requires joining with another table (CTE is simply like a macro). If you are joining multiple tables with millions of rows of records in each, CTE will perform significantly worse than temporary tables.