title: SQL 2005 CTE vs TEMP table Performance when used in joins of other tables
body: <p>I have a complex query that I need to use in a subsequent query (actually update statement).  I have tried both using a CTE and a temp table.  The performance using the CTE is horrible vs the temp table approach.  Its something like 15 seconds vs milliseconds. To simplify the test instead of joining the CTE/Temp table in the subsequent query I simply selected * from it.  In that case they perform the same.</p>
  
  <p>I Have Looked at The Execution Plan for both approaches both with the joins in the subsequent query and then simply select *.  With the simple select the query plans are about the same, but with the joins in the subsequent select the query plans are not.  Specifically the portion of the query plan for creating and populating the temp table stays the same, while the query plan portion for creating and populating the CTE changes dramatically when it is subsequently used in a query with a join.</p>
  
  <p>My question is why does the query plan for the creation and population of the CTE change by how it is subsequently used while the temp table is not.  Also in what scenarios then would a CTE yield better performance than a temp table?</p>
  
  <p>*Note I have used a table variable as well and it is comparable to the temp table approach.</p>
  
  <p>Thanks</p>