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 Execution Order
- GROUP BY
- ORDER BY
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
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
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
- 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.
- 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.
- Virtual tables formed in each phases are logical only.
This is pretty much it about 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!