Temporary table storage performance comparison

Can anyone break it down in plain English the performance difference between using temp tables vs. CTE's vs. table variables in MSSQL. I have used temporary tables quite frequently and have started using CTE's just because of the clear syntax but I have found them to be slower. I think that temp tables are using system memory and that is why they seem fast but may be a bottleneck if trying to do multiple jobs. Table variables I have used sparingly and do not know a great deal about. Looking for开发者_运维问答 some advice from the guru's out there!

This question is well covered in Books Online, MSDN and this site.

About temp tables and table variables you can read here What's the difference between a temp table and table variable in SQL Server?. There you will find that in many cases temp tables cause recompilation of a procedure which is their main disadvantage.

CTEs are well described here http://blogs.msdn.com/b/craigfr/archive/2007/10/18/ctes-common-table-expressions.aspx

CTEs are performance-neutral. They simplify a query for the developer by abstracting out SQL statements - usually complicated JOINs or built-in functions applied to fields. The database engine just in-lines the CTE into the query that uses it. So, the CTE itself isn't "slow", but you may find you are having better performance with temp tables because the database engine is creating better query plans on the queries using the temp tables.

This question was answered here and here. Briefly, this is a different tools fo different tasks.

• Table variables can lead to fewer stored procedure recompilations than temporary tables
• A temp table is good for re-use or to perform multiple processing passes on a set of data