title: Performance impact of chained CTE vs Temp table
body: <p>I have the following Chained CTE query (simplified):</p>
  
  <pre><code>;WITH CTE1
  AS(    
      SELECT * FROM TableA
  ),
  CTE2
  AS(
      SELECT * FROM TableB b INNER JOIN CTE1 c ON b.id = c.id
  )
  
  SELECT * FROM CTE2
  </code></pre>
  
  <p>If I break CTE chain and store data of CTE1 into a temp table then the performance of the overall query improves (from 1 minute 20 seconds to 8 seconds). </p>
  
  <pre><code>;WITH CTE1
  AS(    
      SELECT * FROM TableA
  )
  
  SELECT * INTO #Temp FROM CTE1
  
  ;WITH CTE2
  AS(
      SELECT * FROM TableB b INNER JOIN #Temp c ON b.id = c.id
  )
  
  SELECT * FROM CTE2
  DROP TABLE #Temp
  </code></pre>
  
  <p>There are complex queries in CTE1  and CTE2. I have just created a simplified version to explain here. </p>
  
  <p>Should breaking CTE chair improve the performance? </p>
  
  <p>SQL Server version: 2008 R2</p>