From: tfs@dslextreme.com   
      
   "Erland Sommarskog"  wrote in message   
   news:Xns9E15F25E3F76FYazorman@127.0.0.1...   
   > tshad (tfs@dslextreme.com) writes:   
   >> If I replace the CTE with a temporary table or temporary variable - it   
   >> takes about 6 seconds.   
   >>   
   >> How can that be???   
   >>   
   >> I thought the CTE actually creates temp table internallly.   
   >   
   > No, it doesn't, and you can easily see this by looking at the query plan.   
      
   I wasn't sure about that - just that I saw that in a post somewhere.   
      
   >   
   > A CTE is just a macro, and the parser expands the query before the   
   > optimizer gets a chance to see it. It may be able to recast the   
   > computation   
   > order, but it is very unlikely that it will identify the identical parts.   
   >   
   > The correct workaround is as you did: materialise the CTE into a temp   
   > table.   
   >   
   It's just curious as to what it is doing since all the time is being done by   
   the SELECT after the CTE definition.   
      
   It is almost like it is doing the Select in the definition for each row in   
   the SELECT statement.   
      
   I would have assumed that the Select with the Temp table would work the same   
   as the CTE but obviously that is not the case.   
      
   Tom   
      
   > I have a very old Connect item for this:   
   > https://connect.microsoft.com/SQLServer/feedback/details/12554   
   /poor-execution-plan-with-cte   
   >   
   > --   
   > Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se   
   >   
   > Links for SQL Server Books Online:   
   > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx   
   > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx   
   >   
   >   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)