Question: Does the order of conditions matter in WHERE clause? If we move the conditions in the WHERE clause does it increases the performance? Does SQL Server WHERE conditions support a short circuit?
Sr. Developer in my organization asked me the following question about WHERE clause.
In SQL Server order does not matter in the WHERE condition. SQL Server does not short circuit conditions as well it does not help in performance.
Today is a quick puzzle time.
I recently heard from someone it does matter and a senior SQL person was able to reproduce it, but again, I have no proof for it and I have not seen it before.
Here are the rules for you –
- You can use any numbers of the tables in your query
- You can only change the order of columns in WHERE conditions
- You need to use either AND or OR clause between conditions of the WHERE clause
- The performance will be measured using the Actual Execution Plan and SET IO Statistics ON
- The result set returned from the query should be the same before changing the order of columns in WHERE condition and after changing the order of columns in WHERE condition.
Winning solutions will be posted on this blog with due credit. Here is the related blog post: SQL SERVER – Does Order of Column in WHERE Clause Matter?
You can click on the above link and read the blog post with an example. Trust me it is a very interesting example and looking forward to reading your observations here.
I often get such questions in my SQL Server Performance Tuning Consultancy Comprehensive Database Performance Health Check. Here are two interesting posts which you may be interested in:
Reference: Pinal Dave (https://blog.sqlauthority.com)