0

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.

Mariusz
  • 908
  • 1
  • 13
  • 28
  • This question is not at all specific. Query optimization is going to depend on table structure, table type, indexes, etc. as well as table/index contents. Have you written such a query against your actual data (not this hypothetical query) and viewed the execution plan? – CMGeek Oct 05 '21 at 15:45
  • 1
    SQL is a declarative language. The database engine is free to execute the query any way it sees fit based on the query, the schema, statistics, resources, etc. In addition, there is nothing particularly special about a simple CTE like this. If you want to learn about optimizing sql queries, then you need to educate yourself and learn how to read execution plans. – SMor Oct 05 '21 at 15:47
  • @CMGeek Yes I did but can't figure it out by looking at execution plan. – Mariusz Oct 05 '21 at 15:49
  • Does this answer your question? [SQL Server Views | Inline View Expansion Guidelines](https://stackoverflow.com/questions/67975467/sql-server-views-inline-view-expansion-guidelines) Note that a non-recursive CTE and a view behave identically – Charlieface Oct 05 '21 at 19:15
  • In this instance, the compiler is fully able to optimize out the view. If `TOP` had been inside, it could not do that – Charlieface Oct 05 '21 at 19:17
  • @Charlieface In theory yes. It looks like CTE will become part of one query. I'll test it in practice but it looks promising. Cheers – Mariusz Oct 07 '21 at 09:39

1 Answers1

1

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...

SQLpro
  • 3,994
  • 1
  • 6
  • 14
  • Thanks for taking time to answer. "giving the algebraic tree is passed to the optimizer". Do you mean that it will be passed or it will optimize the query if it will be passed? I just try to avoid loading whole table. It's not large right now but I don't know how much data there will be. – Mariusz Oct 05 '21 at 15:52
  • Optimization is done just after algebrization in terms of logic precedence... But some optimizers uses som tricks ! This is a hot R&D topic, and the big RDBMS editors does not communicate frequently on what they really do to find an efficient execution plan quickly.... They don't want to see there advances copyed in the hands of free actors... – SQLpro Oct 07 '21 at 14:58