What Rules Determine When SQL Server Use a CTE as an "Optimization Fence"?
...list of known cases where SQL Server will reliably honor the CTE as an optimization fence


Any such list would rely on observed behaviour, with no guarantee of reliability.

The SQL Server query optimizer never treats a common table expression as an optimization fence per se, though some constructions are clearly difficult to optimize across. Recursive CTEs are a good example of this.

CTEs are treated very similarly to views/inline functions/subqueries/derived tables and inlined into the query. Any observed 'fence' behaviour depends on the optimizer either not being able to, or deciding not to, optimize across that in-principle permeable border.

Generally speaking, the simpler and more 'relational' the CTE is, the more likely it is that the optimizer will be able to move bits around.

Features that would allow the optimizer to consider, or force it to materialize the 'result' of a CTE have been suggested, but not yet implemented:


The optimizer should considering materialising results of CTEs
Additional Query Hint: OPTION (MATERIALIZE (cte_name,...,n)


In the meantime, the most common workaround is to explicitly materialize the intermediate result set in a temporary table or table variable. This obviously requires a scenario not limited to a single statement.