SQL SERVER – Does Order of Column in WHERE Clause Matter?

Today is a quick puzzle time. Let us learn about – Does the order of column used in WHERE clause matter for performance?

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 clause
• 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 order of columns in WHERE condition.

Winning solutions will be posted on this blog with due credit.

I am giving here a few sample queries and as per my observations, they are always giving me the same performance no matter how I change the order of the column in the WHERE clause.

Example 1:

USE AdventureWorks
GO
SELECT *
FROM HumanResources.Employee e
INNER JOIN HumanResources.EmployeeAddress ea ON e.EmployeeID = ea.EmployeeID
WHERE ea.AddressID = 188 AND e.ManagerID != 44
GO
USE AdventureWorks
GO
SELECT *
FROM HumanResources.Employee e
INNER JOIN HumanResources.EmployeeAddress ea ON e.EmployeeID = ea.EmployeeID
WHERE e.ManagerID != 44 AND ea.AddressID = 188
GO

Example 2:

USE AdventureWorks
GO
SELECT *
FROM HumanResources.Employee e
INNER JOIN HumanResources.EmployeeAddress ea ON e.EmployeeID = ea.EmployeeID
WHERE e.ManagerID = 44 AND ea.AddressID != 188
GO
SELECT *
FROM HumanResources.Employee e
INNER JOIN HumanResources.EmployeeAddress ea ON e.EmployeeID = ea.EmployeeID
WHERE ea.AddressID != 188 AND e.ManagerID = 44
GO

Example 3:

USE AdventureWorks
GO
-- Many results
SELECT *
FROM Sales.SalesOrderDetail
WHERE SalesOrderID = 43659 AND SpecialOfferID = 1
GO
-- One condition wrong
SELECT *
FROM Sales.SalesOrderDetail
WHERE SalesOrderID = 43649 AND SpecialOfferID = 1
GO
-- One condition wrong
SELECT *
FROM Sales.SalesOrderDetail
WHERE SpecialOfferID = 0 AND SalesOrderID = 43659
GO

Let us see what is your solution to this question. Here are a few recent blog posts which you may all be interested in.

• SQL SERVER – Performance Test – sqlcmd vs SSMS
• SQL SERVER – Performance Test – oStress vs SSMS
• SQL SERVER – Stress Testing with oStress – Load Testing
• SQL SERVER – Query Store Status for All the Databases
• What is Transactional Replication Supported Version Matrix? – Interview Question of the Week #274
• SQL SERVER – Turning OFF or ON Query Store for All the Database
• SSMS – Bug – Execution Time-Out
• SQL SERVER – SSMS – SET ROWCOUNT – Real-World Story
• SQL SERVER – sp_helpdb – Accidental Discovery
• SQL SERVER – Blocking Tree – Identifying Blocking Chain Using SQL Scripts

Reference: Pinal Dave (https://blog.sqlauthority.com)