>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

>
>As per readability, I strongly believe CTEs are much more readable than subqueries.

yes, CTE is much more readable than derived tables