MS SQL Query Execution Order or SQL order of operations determines the sequence in which SQL clauses are executed by database engine . This helps in writing the error free and optimised queries .This SQL Query Processing is applied from a basic query to a thousands lines of queries , our database engine uses this execution order . See below the MS SQL Query Execution Order

  1. FROM
  2. ON
  3. OUTER
  4. WHERE
  5. GROUP BY
  6. HAVING
  7. SELECT
  8. DISTINCT
  9. ORDER BY
  10. TOP

Also see : SQL SELECT Query & Statements

Recommended : MS SQL Group By Clause

Also see : MS SQL Having Clause

One of the biggest differences & advantages of SQL Server is the way it processes SQL Code. Where other programming languages have sequential order i.e. from top to bottom . MS SQL Query Execution Order is unique and systematic phases. Each phase is responsible for the successful execution of next phase . Each phase creates a virtual tables and feed it to the next phase and at the end all the pieces of puzzle are put together to display a result.

Let’s see some examples & we will apply the MS SQL Query Execution Order on the examples to see how it all goes behind the scene .

MS SQL Query Execution Order Examples


SELECT CourseID ,[StudentID] 

FROM [School].[dbo].[StudentGrade] 

WHERE [StudentID] in (7,8,9) 

First of all “FROM [School].[dbo].[StudentGrade]” clause will apply to the query

FROM Clause BI Talks BI

This table has 40 rows , we are only showing 10 here but SQL Server will obviously takes the complete table. So , SQL Server now has 40 rows in it’s virtual table. Next phase applicable is WHERE clause.

SQL Server will now execute this line WHERE [StudentID] in (7,8,9) . Result will be as follows

WHERE Clause BI Talks BI

Out of those 40 rows Where clause have filtered students with StudentID 7,8,9. At the end SELECT will select the columns which will be displayed.

So , you see how SQL Server handles the query. Can you write the order for the following query?

 
SELECT SUM([Grade]) As TotalGrades , StudentID 
FROM [StudentGrade] 
GROUP BY StudentID 
HAVING SUM(Grade); 

3 Things To Know About MS SQL Query Execution Order

  1. Aliases for the column cannot be used in WHERE clause. Because WHERE is executed first & SQL Server has no way of knowing about your alias.
  2. For INNER JOIN ON is like a WHERE clause. So if you put your filter criteria in ON clause it will behave as WHERE. For details read this from Pinal Dave. Huge fan of his blog for years.
  3. Virtual tables formed in each phases are logical only.

 

This is pretty much it about MS SQL Query Execution , if you like this article share with your friends 0n Social Media & recommend them to read us.

We are moving to advance tutorials on SQL in future, so if you have some certain topics in mind . I’ll advise you to comment them below and we will surely add them in the series as well.

See you next time

May the Force be with you!

 

About

Professional Data Analyst and Business Intelligence Developer with experience of delivering industrial projects for Supply Chain and Insurance Industry . Sharing all my experience and insights in databases and data warehousing and open to learn from fellows ! Happy Reading and Learning!

Leave a Reply

%d bloggers like this: