Common Table Expressions Versus Temp Table

Question

I'm new to SQL 2008. As I look at the Common Table Expressions (WITH keyword), how is the performance compared to using a temp table. Besides syntax / readability, are there any other benefits to using CTEs?

Answer 1

I have not done exesive measuring, but temp tables are stored in the temp database. CTEs are not, so in most simple cases they should be faster. But in some cases you might create big temp tables and would create indexes on them to speed up further calculations. That's not possible with CTEs. In such cases they are probably slower. But as usually: I don't think that there is a general answer. It always depends on your query and the resulting query plan.