Logical statement processing flow
When writing T-SQL, it is important to be familiar with the order in which the SQL Server Database Engine interprets queries, to later create an execution plan. This helps anticipate possible performance issues from poorly written queries. However, it also helps to understand cases of unintended results. The following steps outline the process that the database engine follows to process a T-SQL statement:
- Parse the query for correctness; in other words, validate the syntax.
- Build a structure that represents the logic of the query as expressed by the developer—a query tree, also called a sequence tree.
- Process all the source and target objects stated in the FROM clause (tables, views, TVFs), together with the intended logical operation (JOIN, APPLY) to perform on those objects.
- Apply whatever pre-filters are defined in the WHERE clause that can reduce the number of incoming rows from those objects.
- Apply any aggregation defined in GROUP BY, followed by any filters that can only be applied to the aggregations.
- Keep only the required columns for the output, and account for any limits stated in a TOP or DISTINCT clause.
- Order the resulting row set as specified by the ORDER BY clause, and make the result set available for the client.
It becomes clearer now that properly defining how tables are joined (the logical join type) is important to any scalable T-SQL query, namely by carefully planning on which columns the tables are joined. For example, in an inner join, these join arguments are the first level of data filtering that can be enforced, because only the rows that represent the intersection of two tables are eligible for subsequent operations.
Then, it also makes sense to filter out rows from the result set using a WHERE clause, before applying any post-filtering conditions that apply to sub-groupings using a HAVING clause. This is because SQL Server evaluates a WHERE clause before a HAVING clause, and it can limit the row count earlier in the execution phase, translating into reduced I/O and memory requirements, and also reduced CPU usage when applying the post-filter to the group.
The following diagram summarizes the logical statement processing flow for the building blocks discussed in this chapter:
