7cf8e016   
   From: esquel@sommarskog.se   
      
   SQL Learner (excelmodeling@gmail.com) writes:   
   > Thank you for your help.  This method is cool.  So the State_Average   
   > is like a temporary table that gets deleted after the select statement   
   > is ran?   
      
   Logically you could think of it that way. However, State_Average is   
   never materialised, and SQL Server may recast the computation order, as   
   long as the result is the same.   
      
   On the other hand, if the query has multiple references to the CTE, it   
   is very likely that SQL Server will compute it multiple times.   
      
   A better way to think of a CTE is a view which has the scope of a   
   single query.   
      
   CTE:s and derives tables are very similar concepts. The main difference   
   is that CTE:s are named, they can be referred to multiple times in the   
   query. (And then there is a special form of a CTE to deal with recursive   
   structures.)   
      
      
   --   
   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   
   SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)