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

  • Learn T-SQL Querying
  • Pedro Lopes Pam Lahoud
  • 400字
  • 2021-06-24 14:38:11

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:

主站蜘蛛池模板: 内乡县| 高平市| 双江| 天峨县| 杂多县| 和硕县| 闵行区| 巴南区| 平原县| 长沙市| 新昌县| 教育| 天台县| 琼结县| 定陶县| 沙河市| 专栏| 卓尼县| 祁连县| 昌平区| 光山县| 荆州市| 无锡市| 湖南省| 哈尔滨市| 黄冈市| 祁阳县| 修武县| 江永县| 平顺县| 托里县| 钟祥市| 龙泉市| 泾川县| 常熟市| 江门市| 自贡市| 尚志市| 阳山县| 扎赉特旗| 固原市|