SQL Server processes a single SQL statement

Processing a single SQL statement is the most basic way for SQL Server 2005 to execute an SQL statement.

1. Optimize the SELECT statement (Query Optimizer)

The SELECT statement is notProgramDoes not specify the exact steps for the database server to retrieve the requested data. This means that the database server must analyze the statements to determine the most effective way to extract the requested data. This is called the "optimize SELECT statement ". The component that processes this process is called the query optimizer. The optimizer operates mainly on input and output: input includes queries, database solutions (table and index definitions), and database statistics; the output is called a query execution plan, which is also called a query plan or directly called a plan ".

Generally, different methods are used to access data in each table. If you only need several rows with specific key values, the database server can use indexes. If you need all rows in the table, the database server can ignore the index and perform a table scan. If you want all rows in the table and an index key column in order by, performing index scan instead of table scan may save the individual sorting of the result set. If the table is small, table scanning may be the most effective method for almost all accesses to the table.

The process of selecting an optimal execution plan from multiple potential plans is called "optimization ". The query optimizer is one of the most important components of the SQL Server database system. Although the query optimizer needs to use some overhead when analyzing queries and selecting plans, when the query optimizer selects an effective execution plan, this will save several times. For example, two construction companies may have the same design drawing for a home. If a company takes a few days to plan how to build the house at the beginning, and another company starts construction without any planning, it is likely that the company that took the time to plan the project will be completed first.

The SQL Server query optimizer is a cost-based optimizer. For the amount of computing resources used, each possible execution plan has associated costs. The query optimizer must analyze possible plans and select a plan with the lowest estimated cost. Some Complex select statements have thousands of possible execution plans. In these cases, the query optimizer does not analyze all possible combinations, but uses complexAlgorithmFind an execution plan: the cost is reasonably close to the lowest possible cost.

The SQL Server Query Optimizer not only selects the execution plan with the lowest resource cost, but also selects the plan that can return the result set to the user as quickly as possible and the resource cost is reasonable. For example, compared with serial query, the resources used for parallel query are generally more, but the query speed is faster. Therefore, if the server load is not affected, the SQL Server optimizer will use the parallel execution plan to return results.

When the query optimizer estimates the resource costs required by different methods for extracting information from a table or index, it depends on the distribution content statistics. Statistics on the distribution of columns and indexes are retained. These statistics indicate the selectivity of values in a specific index or column. For example, in a table that represents a car, many cars come from the same manufacturer, but each car has a unique license plate number (VIN ). Vin indexes are more selective than manufacturer indexes. If the index statistics are not the current one, the Query Optimizer may not be able to make the best choice for the current table status. For more information about keeping index statistics up-to-date, see index statistics.

The query optimizer is very important because it enables the database server to dynamically adjust changes in the database without the need of input by programmers or database administrators. In this way, programmers can concentrate on describing the final query results. They can believe that each time a statement is run, the query optimizer can always generate an effective execution plan for the database status.

2. Process select statements

The basic steps for SQL Server to process a single SELECT statement include:

A. the analyzer scans select statements and divides them into logical units (such as keywords, expressions, operators, and identifiers ).

B. Generate a query tree (sometimes called a sequence tree) to describe the logical steps used to convert the source data to the format required by the result set.

C. The query optimizer analyzes different methods for accessing the source table, and then selects a series of steps that return the fastest results and use the least resources. Update the query tree to precisely record these steps. The final and optimized version of the query tree is called the execution plan ".

D. The relational engine starts to execute the execution plan. When processing the data in the base table, the relational engine requests data from the row set requested by the relational engine.

E. The relational engine processes the data returned by the storage engine into the format defined by the result set, and then returns the result set to the client.

3. process other statements

The basic steps for processing select statements are also applicable to other SQL statements, such as insert, update, and delete. The update and delete statements must target the row set to be modified or deleted. The process of identifying these rows is the same as that of identifying the source row that makes up the SELECT statement result set. Both the update and insert statements can contain embedded select statements to provide the data values to be updated or inserted.

Even if a Data Definition Language (DDL) statement such as create procedure or alter table is finally parsed as a series of relational operations on the system directory table, sometimes, the data is parsed based on the data table (such as alter table add column ).