官术网_书友最值得收藏!

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:

  1. Parse the query for correctness; in other words, validate the syntax.
  2. Build a structure that represents the logic of the query as expressed by the developer—a query tree, also called a sequence tree.
  3. 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.
  4. Apply whatever pre-filters are defined in the WHERE clause that can reduce the number of incoming rows from those objects.
  5. Apply any aggregation defined in GROUP BY, followed by any filters that can only be applied to the aggregations.
  6. Keep only the required columns for the output, and account for any limits stated in a TOP or DISTINCT clause.
  7. Order the resulting row set as specified by the ORDER BY clause, and make the result set available for the client.
Keep in mind that even though TOP is processed before the ORDER BY clause, during execution the entire result set is sorted before the TOP clause is applied.

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:

主站蜘蛛池模板: 平潭县| 大石桥市| 济阳县| 张家界市| 施秉县| 太仆寺旗| 洛扎县| 曲阜市| 乾安县| 渭南市| 舟曲县| 濮阳市| 中超| 太和县| 新营市| 濮阳县| 广丰县| 天门市| 铜梁县| 兴文县| 黔西县| 千阳县| 改则县| 饶阳县| 海宁市| 宝兴县| 潜江市| 泰来县| 莱州市| 安陆市| 宁津县| 上高县| 闸北区| 新河县| 黄平县| 仙居县| 大安市| 息烽县| 昌邑市| 云南省| 鹤庆县|