>
>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).
Yes, it is just your opinion. With a CTE you actually read the code from top to bottom, like reading any book or code, instead of locating the subquery(ies) buried at bottom, read and then return to top.
If it is nested (I mean one CTE use the ones before that), it makes much more clear that they are not overvalued but underestimated. Like doing multiple queries in succession in VFP, instead of one giant query with subqueries (if you can do).
CTE inefficiency I was talking about, is specific to MS SQL Server (could be considered an inefficiency in MS implementation of CTEs but. CTEs are not specific to MS SQL Server.
PS: CTEs are also not for just readability but for query performance. And not only that, using CTEs you can create queries that are not possible with subqueries.