@bbaird I agree that one can get overzealous with CTEs, but you seem to imply that taking a step-wise approach to writing queries is somehow a bad thing, why? Or why would it mean being lazy?
Usually, what I've seen is that an attempt to re-use CTEs, like re-using views, often introduces an unnecessary dependency - typically related to aggregates - which sometimes hurts optimization. In some of those cases, I have resorted to using ITVFs where an explicit parameter can often punch through places where aggregation stops the optimizer.
I have a strong dislike for long GROUP BYs, so I usually try to aggregate as early as possible. And if an aggregated set represents a logical step of my query, I certainly won't shy away from defining it as a CTE.
I'm happy to deal with derived tables instead of CTEs, but in my personal experience, CTEs usually make a query more readable, and therefore more maintainable
I use CTEs, there is a point where you can have too many, unfortunately, even when they are well organized, and the optimizer just seems to give up or something.
Like one of my procs generates documentation, and every documentation is of the same form but they need to be unioned together, but some documentation might be an index of some other documentation.
So like each observation has a page of form Title, Text and then there is an Observation Index with links to all of those. So the output is Observations UNION ObservationIndex.
But it's a lot more than that, so in the end, instead of UNION, I ended up inserting into a table in steps. It just ended up being a lot faster.
I used to tend to use a single query almost always. It might have become even worse when CTEs were introduced in SQL Server. I still like single query solutions but I've also learnt to like splitting big complex queries into simpler, lighter ones.
At the end of the day, a CTE is just a tool like many others. And like other tools, it can be overused/abused/misused. It doesn't means it can't also be used appropriately and effectively.
@AndriyM I think there's a lot of thought of Do A then Do B then Do C with the chaining of CTEs, when it might be easier to do C -> A -> B, takes away some of what the optimizer can do. I see a lot of people do some sort of derivation in CTE A, aggregate that in B, then do a windowing function in another CTE - entirely possible to do all of those steps in one, clean SELECT statement. I use them only when I need to do some serious fighting against a bad data model.
