What about the interesting issues raised by the question?

To me, it is strange why the sqlserver team is avoiding adding an option to materialize intermediate tables from CTE's. Oracle has this with the /*+ materialize */ hint.

If we could have the option to get rid of the automatic expansion of inline views/CTEs/TVF, we could control the query execution by query semantics instead of fighting stale statistics with query hints.

A person writing query, will often know instinctively the best sequence of steps to execute a query would be.

"I'll get my few candidates from this CTE1, and then get some more data with CTE2 that consumes CTE1 and then I'll join a little more. "

To get this to perform consistently, you'll often see developers make up all kinds of #temp tables etc, because the got burned by the optimizer one time to many.

Its no secret that the optimizer will generate strange plans when there are a lot of joins and the statistics a not as fresh as they could be.

On Oracle, /*+ materialize */ did the trick. On sqlserver you'll have to do the materializing yourself, making the code less readable and maintainable.