>>
>>Never mind the OUTER APPLY, you're right it could as well be a CROSS APPLY, I'm referring to that it does not need a CTE.
>>My personal opinion is that a CTE is overvalued. In many occasions it only makes a query less readable. I tend to restrict its use to solving queries of hierarchical nature.
>
>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.
>
>As per readability, I strongly believe CTEs are much more readable than subqueries.

Again, just my opinion. If I have to analyse a query I don't like to jump to the top to figure out on how the CTE is defined. It does more harm than good IMO in troubleshooting. So yes, I'd like to avoid CTE in subqueries as much as I can. Hence my opinion they are overvalued.

As far as your observation of CTE not being as efficient as could: Yes I've stumped against that wall several times, often causing me to take a different rout to a better performing solution (like executing in two steps, like table variables or temp tables).