The documentation says that logical processing is used to know what I have available for the next clauses of a query, but later it contradicts itself by saying that the order can change, that confuses me.
Logical Processing Order of the SELECT statement
The following steps show the logical processing order, or binding order, for a SELECT statement. This order determines when the objects defined in one step are made available to the clauses in subsequent steps. For example, if the query processor can bind to (access) the tables or views defined in the FROM clause, these objects and their columns are made available to all subsequent steps. Conversely, because the SELECT clause is step 8, any column aliases or derived columns defined in that clause cannot be referenced by preceding clauses. However, they can be referenced by subsequent clauses such as the ORDER BY clause. The actual physical execution of the statement is determined by the query processor and the order may vary from this list.
1. FROM 2. ON 3. JOIN 4. WHERE 5. GROUP BY 6. WITH CUBE or WITH ROLLUP 7. HAVING 8. SELECT 9. DISTINCT 10. ORDER BY 11. TOP
The preceding sequence is usually true. However, there are uncommon cases where the sequence may differ.
For example, suppose you have a clustered index on a view, and the view excludes some table rows, and the view's SELECT column list uses a CONVERT that changes a data type from varchar to integer. In this situation, the CONVERT may execute before the WHERE clause executes. Uncommon indeed. Often there is a way to modify your view to avoid the different sequence, if it matters in your case.
- Does this mean that, for example, there will be times when the
WHEREclause is executed before the
FROMclause and times when the
FROMclause is executed first and then the
- Can I rely 100% on logical processing to know which objects (columns aliases, tables, etc) I have "available" in subsequent query clauses?
I also read this from a StackOverflow expert on SQL:
SQL, by the way, is a descriptive language not a procedural language. A query does not specify the "order of execution". That is determined by the compiler and optimizer. What you are referring to is the "order of parsing", which explains how identifiers are resolved in the query.
- So, what is the point of logical processing if physical processing (the database, optimizer, indexes, etc at the end of the day) decides the order of execution in a query?
- Does this mean that physical processing can "skip" (alter, modify, I don't know what the right word is) the logical processing steps?
- Does the logical processing is executed first and then the physical processing?, or how?