I think you may be giving CTEs a little too much credit, at least as implemented in SQL Server. As the name implies they are expressions. They are not necessarily persisted, though the compiled plan may spill the results to tempdb. They neither help nor hinder performance on their own. What they are (except for the recursive variety) is a way to write sub queries ahead of the main query instead of after it. Of course you can also refer to a cte multiple times in the main query if you need to (or another cte or subquery.) Very handy! In general though, writing:
with
c1 as (select ...),
c2 as (select ...),
...
select ...
from c1 join c2 ...
compiles the same as
select ...
from (select ...) as c1
join (select ...) as c2
...
Officially, A CTE "Specifies a temporary named result set". Of course that's exactly the same as a sub query. after the compiler is finished building the syntax tree from a CTE-based query and the same semantic query using sub queries, the results are the same. Which is why the execution plans are usually the same.
I use CTEs extensively in my work and have for years. I like them because they help make code more readable. I find it much easier to scan a linear set of CTEs than a heavily nested set of sub queries. Call it personal preference, if you like.
For further actions, you may consider blocking this person and/or reporting abuse
We're a place where coders share, stay up-to-date and grow their careers.
I think you may be giving CTEs a little too much credit, at least as implemented in SQL Server. As the name implies they are expressions. They are not necessarily persisted, though the compiled plan may spill the results to tempdb. They neither help nor hinder performance on their own. What they are (except for the recursive variety) is a way to write sub queries ahead of the main query instead of after it. Of course you can also refer to a cte multiple times in the main query if you need to (or another cte or subquery.) Very handy! In general though, writing:
with
c1 as (select ...),
c2 as (select ...),
...
select ...
from c1 join c2 ...
compiles the same as
select ...
from (select ...) as c1
join (select ...) as c2
...
Officially, A CTE "Specifies a temporary named result set". Of course that's exactly the same as a sub query. after the compiler is finished building the syntax tree from a CTE-based query and the same semantic query using sub queries, the results are the same. Which is why the execution plans are usually the same.
I use CTEs extensively in my work and have for years. I like them because they help make code more readable. I find it much easier to scan a linear set of CTEs than a heavily nested set of sub queries. Call it personal preference, if you like.