>Through i recognise the place of CTE's , I really hate to use them, and would avoid them whenever possible. One of the problems is that it is not an ANSI standard and often there is a better (readable and maintainable) alternative.
>
>Interesting. I really like CTEs and I think that, in many instances, replacing derived tables with them makes the code much more readable and maintainable.


Do you think that:

   ;WITH POTemp as
      ( SELECT VendorID, SUM(TotalDue) as VendorTotal 
                FROM Purchasing.PurchaseOrderHeader
                WHERE	 where OrderDate BETWEEN '1-1-2008' AND '12-31-2008'  
                  GROUP BY VendorID) 
   
  select Vend.Name,  VendorTotal
    from  Purchasing.Vendor   as Vend
      left outer   join POTemp
               on Vend.BusinessEntityID = POTemp.VendorID
      order by VendorTotal desc

Is more readable than

select Vend.Name, SUM(TotalDue) as VendorTotal
      from  Purchasing.Vendor   as Vend
         left outer  join  Purchasing.PurchaseOrderHeader AS POH  
               on Vend.BusinessEntityID = POH.VendorID AND POH.OrderDate BETWEEN '1-1-2008' AND '12-31-2008'   
         group by Vend.Name   
         order by VendorTotal desc

or

select Vend.Name, (SELECT SUM(TotalDue) FROM   Purchasing.PurchaseOrderHeader 
     WHEREVend.BusinessEntityID = VendorID AND OrderDate BETWEEN '1-1-2008' AND '12-31-2008') as VendorTotal
      from  Purchasing.Vendor   as Vend
      order by VendorTotal desc

?

As I was saying, in an awfull lot of case you just do not need CTE's as there is a better alternative.

Aside from that, CTE's cannot be used as a total replacement of temp tables which are dirived from more complex statement as you cannot nest CTE's and cannot use two CTE's in one SQL statement.

In my carreer I've used CTE's only in a handful of cases, but only as a last resort. Esspecially the recursive variant is sometimes very difficult to follow and therefore hardly maintainable. I appreciate people use it to show their capabilities, but to me, simplicity and maintainability is worth much more than to score with a SQL statement that can only be analysed and maintained by high skilled SQL programmers.