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.

 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.