>>Most CTEs could be written as a subquery, fine, a preference. OTOH CTEs are not overvalued, all CTEs cannot be written as subqueries. However, if you think CTEs in the context of MS SQL Server only, sometimes MS SQL Server doesn't create 'temp' data as it should.
>
>SQL Serve does not process CTE as separate unit but as part of the query. It would be nice to have a hint to tell SQL Server not to combine CTE with main query but considering that it's declarative language, it's unlikely ever happen

Its a double edge sword. In some cases materializing the CTE is the best way, however in most cases the optimizer loses the ability to optimize the query in the best way. If the CTE is used multiple times within one query (The main problem), the optimizer will optimize the two independedly in the context where they are used.

This is often the best way to do it, but not always. In that case its better to materialize it into a temp table or table variable first and then use it into a second SELECT Statement.

A hint would be a good idea to spool the result in the execution plan for multiple usage.

Walter,