As a SQL Server practitioner, it is important to understand logical query processing. The following list is found in the book called T-SQL Querying by Itzik Ben-Gan, Microsoft Press, copyright 2015, in Chapter 1. The list below contains the general form of a query along with the step numbers assigned according to the order in which different clauses are logically processed.
- (5) SELECT (5-2) DISTINCT (7) <TOP_specification>
- (1) FROM (1-J) <left_table> <join_type> JOIN <right_table> ON <join_predicate>
- | (1-A) <left_table> <apply_type> APPLY <right_input_table> AS <alias>
- | (1-P) <left_table> PIVOT (<pivot_specification>) AS <alias>
- | (1-U) <left_table> UNPIVOT (<unpivot_specification>) AS <alias>
- (2) WHERE <where_predicate>
- (3) GROUP BY <group_by_specification>
- (4) HAVING <having_predicate>
- (6) ORDER BY <order_by_list>
- (7) OFFSET <offset_specification> ROWS FETCH NEXT <fetch_specification> ROWS ONLY
In most programming languages the code is processed in the order in which it is written. This is called typed order. In SQl the first clause that is processed is the FROM clause and the SELECT clause, which is typed first, is processed almost last. This is referred to the local processing order to distinguish it from the typed order and the physical processing order.
Each step in the logical processing order produces a virtual table that is used as the input of the next step. Onlt the table generated by the last step is available to the caller. If a certain clause is not specified in a query the corresponding step is simply skipped.
A query is processed in steps or phases. After each phase a virtual table is generated. We will call them VT1, VT2 and so on.
- FROM -> VT1
- WHERE -> VT2
- GROUP BY -> VT3
- HAVING -> VT4
- SELECT -> VT5
- evaluate expressions -> VT5-1
- distinct -> VT5-2
- ORDER BY -> virtual cursor VC6. Abscent an ORDER BY clause VT5-2 becomes VT6
- TOP | OFFSET-FETCH -> VC7 or VT7 from VC6 or VT6 respectively