[atwebb]

It can wind up with more of a procedural thought though than set-based. Not always, just something to pay attention to and pushing filters early / explicitly. It is asking more of the optimizer a lot of the times and that's were a bunch of the cautions come in to play.

[mwigdahl]

Just tuned a query like this today. Many chained CTEs, joined together at each stage to do range checking logic within subgroups. There were so many self joins that the row estimates (and requested memory grant) were through the roof.

I love CTEs, but the query semantics matter too.