SQL Server Execution Order

SQL Server

One of the first things people learn about SQL Server is how to write Transact SQL statements that can execute correctly in the SQL Sever Query Engine. One common source of confusion is when users learn the simple fact that SQL syntax elements are not ordered during creation in the same way they are executed in the Query Engine. The written ordering is:

  • SELECT [ DISTINCT ]
  • FROM
  • WHERE
  • GROUP BY
  • HAVING
  • UNION
  • ORDER BY

We all learn the proper ordering of our Transact SQL statements to prevent errors. This written ordering differs fundamentally from the actual order of execution:

  • FROM
  • WHERE
  • GROUP BY
  • HAVING
  • SELECT
  • DISTINCT
  • UNION
  • ORDER BY

This can be confusing, particularly for developers who are accustomed to programming languages that execute commands in the exact order as listed in the written source code. There are a few important things to note from this information:

  1. The FROM is the first clause to execute, not SELECT. The engine must load data from the hard disk into server memory in order to start performing operations on the data.
  2. SELECT is executed after most other clauses. Most importantly, after FROM and GROUP BY. This is important to understand when you think you can reference stuff that you declare in the SELECT clause from the WHERE clause.
  3. UNION is placed before ORDER BY in both lexical and logical ordering. While some SQL dialects allow for ordering subqueries or derived tables, there is no guarantee that such ordering will be retained after a UNION operation

Note, not all databases implement things the same way. Rule number 2, for instance, does not apply exactly in the above way to MySQL, SQLite, or PostgreSQL.

You can learn more on this interesting and important subject here. There is also a poster available here.

Advertisements

2 thoughts on “SQL Server Execution Order”

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