SQL Server Query Processing / SQL Server 查询处理过程

/* 推荐一个SQL Server 博客，后面准备把这个博客文章全部翻译。SQL Server 主页连接：https://logicalread.com/category/databases/sqlserver/ */

查询处理--由 SQL Server 中的关系引擎执行，它获取编写的 T-SQL 语句并将其转换为可以向存储引擎发出请求并检索所需结果的过程。

SQL Server 需要四个步骤来处理查询：分析、代化、优化和执行。

前三个步骤都由关系引擎执行；第三步输出的是优化计划，在此期间，将调用存储引擎以检索将成为正在执行的查询结果数据。

以下简要讨论解析和代化。

解析在分析阶段，SQL Server 对源代码（T-SQL 语句）执行基本检查。此分析查找无效的 SQL 语法，例如不正确地使用保留字、列名和表名等。 如果解析完成且没有错误，它将生成一个解析树，该树将传递到查询处理的下一阶段，即绑定。解析树是查询的内部表示形式。如果分析检测到任何错误，进程将停止并返回错误。

代化阶段也称为绑定阶段。在早期版本的 SQL Server 中，此阶段称为规范化。在代化过程中，SQL Server 对分析树执行多个操作，然后生成传递查询树给查询优化器。 代化期间执行的步骤遵循以下模型：

• 步骤 1：名称解析 — 确认所有对象都存在并在用户的安全上下文中可见。这是检查表和列名称以确保它们存在并且用户有权访问它们的位置。
• 步骤 2：类型派生 — 确定解析树中每个节点最终类型
• 步骤 3：聚合绑定 — 确定在何处进行任何聚合
• 步骤 4： 组绑定 — 将任何聚合绑定到相应的选择列表

在此阶段检测到语法错误。如果遇到语法错误，优化过程将停止，并将错误返回给用户。

Quoting：SQL Server Query Processing

SQL Server Query Processing

Query processing is performed by the Relational Engine in SQL Server. It is the process of taking the T-SQL statements you write and converting them into something that can make requests to the Storage Engine and retrieve the results needed.

SQL Server takes four steps to process a query: parsing, algebrizing, optimizing, and execution. They are shown in Figure 1. The first three steps are all performed by the Relational Engine. The output of the third step is the optimized plan that is scheduled, and during which calls are made to the Storage Engine to retrieve the data that becomes the results of the query you are executing.

Figure 1.

The following sections briefly discuss parsing and algebrizing.

Parsing

During the parsing stage SQL Server performs basic checks on the source code (your T-SQL batch). This parsing looks for invalid SQL syntax, such as incorrect use of reserved words, column and table names, and so on.

If parsing completes without errors, it generates a parse tree, which is passed onto the next stage of query processing, binding. The parse tree is an internal representation of the query. If parsing detects any errors, the process stops and the errors are returned.

Algebrizing

The algebrization stage is also referred to as the binding stage. In early versions of SQL Server this stage was referred to as normalization. During algebrizing, SQL Server performs several operations on the parse tree and then generates a query tree that is passed on to the Query Optimizer.

The steps performed during algebrizing follow this model:

• Step 1: Name resolution — Confirms that all objects exist and are visible in the security context of the user. This is where the table and column names are checked to ensure that they exist and that the user has access to them.
• Step 2: Type derivation — Determines the final type for each node in the parse tree
• Step 3: Aggregate binding — Determines where to do any aggregations
• Step 4: Group binding — Binds any aggregations to the appropriate select list

Syntax errors are detected during this stage. If a syntax error is encountered, the optimization process halts and the error is returned to the user.