SQL Server and optimizing Common Table Expression [duplicate]-CodePudding

I have this query:

;WITH My_CTE AS
(
     SELECT
         col1,
         col2
     FROM
         My_Table
)
SELECT TOP(10)
    col1,
    col2
FROM
    My_CTE
WHERE
    col1 > 10
ORDER BY
    col2

Will SQL Server load all records from My_Table first and then filter out records not satisfying where clause or it will manage to optimize select within CTE ?

Notice that TOP, ORDER BY and WHERE clauses are only in outer query.

CodePudding user response：

CTE will be optimized as well as non CTE queries.

CTE is only a "ease of writing" except for RECURSIVE queries.

The algebrizer part of the SQL engine will re-arange the compound query into a single query, (algebraic simplification) then the given algebraic tree is passed to the optimizer...