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.

Structured Query Language

By a “set” we mean any collection M into a whole of definite, distinct objects m (which are called the “elements” of M) of our perception or of our thought.

—George Cantor, in “Georg Cantorby Joseph W. Dauben (Princeton University Press, 1990)

Standard SQL is based on the relational model, which is a mathematical model for data management and manipulation. The relational model was initially created and proposed by Edgar F. Codd in 1969. Since then, it has been explained and developed by Chris Date, Hugh Darwen, and others.

Transact-SQL (T-SQL) is the main language used to manage and manipulate data in Microsoft SQL Server.

Microsoft SQL Server Management Studio showing...
Microsoft SQL Server Management Studio showing a query, the results of the query, and the Object Explorer pane while connected to a SQL Server database engine instance. (Photo credit: Wikipedia)

In fact, T-SQL is based more on multiset theory than on set theory. A multiset (also known as a bag or a superset) in many respects is

similar to a set, but can have duplicates. The T-SQL language does give you enough tools so that if you want to follow relational theory, you can do so. For example, the language provides you with a DISTINCT clause to remove duplic

ates.

It then moves on to describe one of the most important concepts you need to know about the language—logical query processing.

Thinking in Sets

Thinkins in sets is a great book by Joe Celko’s. This book tell us about concepts how we need to think when we were working with databases.
Columns are not fields, rows are not records and tables are note files, hence SQL is declarative, not procedural. There is no sequential access or ordering in table, so “first,” “next,”
and “last” rows are totally meaningless.
I agree when he write “One of the first things that a newbie does is use a proprietary autonumbering feature in their SQL product as a PRIMARY KEY”. This is completely wrong, and it violates the definition of a relational key.
An attribute has to belong to an entity in the real world being modeled by the RDBMS. Autonumbering does not exist in an entity in the real world being modeled by the RDBMS. Thus, it is not an attribute
and cannot be in a table, by definition.
That’s how I think and was totally wrong before.

So lets do something sets!

20130501-200608.jpg