SQL server Logical Query Processing Phases

Many aspect of SQL language are based on the solid mathematical foundations like Set theory and predicate logic, and those foundations have given it a long life span compare to other computation subjects. T-SQL is Microsoft flavor of standard SQL and it has two sides in term of processing the queries: Physical and logical.

Logical aspect of T-SQL is conceptualized interpretation of different phases involved in the query to return an appropriate result from the data. Order of processing of these phases is important when you write the queries. The keyed in order of query phases are:

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

But the engine would not process the query in that order and logically the orders of query processing are different which is:

  1. From
  2. Where
  3. Group By
  4. Having
  5. Select
  6. Order By

Each phase output would be virtual table input to the next phase. It’s important to notice there is a hierarchy notion in this phases, for instance aliases that are defend in Select phase are unavailable in higher phases and  accessible only in lower phase which is Order By  phase.

SELECT
 EmployeeID AS [Employee ID]
 ,Name AS [Full Name]
 ,Title AS [Job Title]
FROM Employees
WHERE Name='King' --You Can't use [Full Name] alias, because it’s not defined yet
ORDER BY [Full Name]

This is really important concept that is mostly neglected.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s