Will SQL always run statement in CTE first?-CodePudding

Suppose that I run a SQL query as follow and there is indexing in ID column in both table as well as a where condition filtering.

Will SQL Server be smart enough to optimise the query? Or it will perform query to select everything inside both CTE first before doing join and filtering?

Wrapping code inside CTE makes it readable in my opinion but I am not sure if SQL Server will be smart enough to optimize the query.

WITH SomeCTE AS 
(
    SELECT ...
    FROM ...
), AnotherCTE AS 
(
    SELECT ...
    FROM ...
)
SELECT *
FROM SomeCTE 
INNER JOIN AnotherCTE ON SomeCTE.ID = AnotherCTE.ID 
                      AND SomeCTE.Column1 = 'Hello';

CodePudding user response：

Will SQL Server be smart enough to optimize the query. Or it will perform query to select everything inside both CTE first before doing join and filtering.

In general CTEs, Views, and Subqueries are not executed separately. The query is optimized as a whole, and predicates specified in the outer query may be pushed into the CTE.

But the exact optimization applied depends on the query details and the table structures, so you'll need to test. For instance if SomeCTE.Column1 is the result of an expression in the CTEs it would block pushing the predicate.

Sometimes you want the CTE to be materialized, and SQL Server doesn't have any query hint to force that. The normal pattern in that case is to factor out the CTE you want materialized into a temp table, and reference that in the CTE.