Logical Query Processing

What Happens When a Query is Submitted?

The main statement used to retrieve data in T-SQL is the SELECT statement. Following are the main query clauses specified in the order that you are supposed to type them (known as “keyed-in order”):

  1. SELECT
  2. FROM
  3. WHERE
  4. GROUP BY
  5. HAVING
  6. ORDER BY

But there is a logical query processor which is the conceptual interpretation order, is different. It starts with the FROM clause. Here is the logical query processing order of the six main query clauses:

  1. FROM
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. SELECT
  6. ORDER BY
Microsoft SQL Server Management Studio display...
Microsoft SQL Server Management Studio displaying a sample query plan. (Photo credit: Wikipedia)

When you submit a query to a SQL Server database, a number of processes

 on the server go to work on that query. The purpose of all these processes is to manage the system such that it will provide your data back to you, or store it, in as timely a manner as possible, whilst maintaining the integrity of the data.

When you pass a T-SQL query to the SQL Server system, the first place it goes to is the relational engine.

As the T-SQL arrives, it passes through a process that checks that the TSQL is written correctly, that it’s well formed. This process is known as query parsing . The output of the Parser process is a parse tree, or query tree (or even sequence tree). The parse tree represents the logical steps necessary to execute the query that has been requested.

The query optimizer is essentially a piece of software that “models” the way in which the database relational engine works. Using the query processor tree and the statistics it has about the data, and applying the model, it works out what it thinks will be the optimal way to execute the query – that is, it generates an execution plan.