Monday, June 24, 2013

The Logical Query Processing in T-SQL

Today I’m going to discuss about the Logical Query Processing in T-SQL

Logical Query Processing:  The conceptual interpretation of the query that explain what the correct result of the query is


Physical Query Processing:  Processing of the query by database engine. Produce the result defined by logical query processing.

Following are the Main Query Clauses in the order that you are supposed to type (known as “keyed-in-order”)
  1. SELECT
  2. FROM
  3. WHERE
  4. GROUP BY
  5. HAVING
  6. ORDER BY

But the Logical Query Processing order, which is the Conceptual interpretation order, is different
  1. FROM
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. SELECT
  6. ORDER BY

Because of this logical order you won’t get expected result for below sample query,

SELECT Name , YEAR(Joindate) AS JoinDate
FROM dbo.Emplyees
WHERE JoinDate > 2000

This should be,

SELECT Name , YEAR(Joindate) AS JoinDate
FROM dbo.Emplyees
WHERE YEAR(Joindate)  > 2000