Joe Webb Blog

Musing and observations about SQL Server, other technogies, and sometimes just life in general

Does the order of criteria the WHERE clause matter?

Recently, I demonstrated that the order of columns in a composite index greatly determines its usefulness. Microsoft SQL Server can efficiently resolve queries using a composite index if the search criteria includes the first column in the index. If the search criteria omits the first column and includes only subsequent columns, the index is of no use to SQL Server for resolving that query. To see the proof, refer to this post.

The example I used in the prior post included the following query as a basis for the discussion.

SELECT         
*
FROM
Customers
WHERE
Last_Name = 'smith' AND
First_Name = 'Jake'

One reader noted the order of the search criteria in the WHERE clause and wondered if it has any impact on the applicability of a composite index. In other words, would the following query still make use of the composite index?

SELECT         
*
FROM
Customers
WHERE
First_Name = 'Jake' AND
Last_Name = 'smith'

Great observation! To answer that question, let's once again look at the query execution plan for the first query.

WhereOrder1-2008-02-22

From this execution plan, we can see that the query optimizer found the composite index named ix_Customer_Name to be useful in resolving the query. This is certainly to be expected since the order of search criteria in the WHERE clause exactly matched the order of columns in the composite index.

In the second query, however, the order of columns in the search criteria in the WHERE clause is reversed as compared to the composite index. Let's look at the execution plan so see if that makes any difference to SQL Server.

WhereOrder2-2008-02-22

This query produced the exact same execution plan as the prior query. Why?

Generally speaking, the order of criteria in the WHERE clause is evaluated and optimized by the query optimizer prior to creating an execution plan. This is good. However, I would encourage you to review the query execution plan for each query prior to putting it into production. You may have some unique expressions or circumstances in your scenario that may be the exception to this rule.

Cheers!

Joe

kick it on DotNetKicks.com

Legacy Comments


Raj
2008-02-24
re: Does the order of criteria the WHERE clause matter?
hi joe,

nice post... 1 suggestion : can u plz increase the size of thumbnails such that they become readable... that will save me few mouse clicks and make the reading faster and easier :-)

raj

anonymous
2008-03-27
re: Does the order of criteria the WHERE clause matter?
yah, but it might make things a lot more difficult for users on dialup

c
2009-12-17
re: Does the order of criteria the WHERE clause matter?
Ha Dial-up?!?! who cares, get broadband

Boni Spielautomat
2010-02-17
re: Does the order of criteria the WHERE clause matter?
Recently, I demonstrated that the order of columns in a composite index greatly determines its usefulness. Microsoft SQL Server can efficiently resolve queries using a composite index if the search criteria includes the first column in the index. If the search criteria omits the first column and includes only subsequent columns, the index is of no use to SQL Server for resolving that query

Murukesh
2010-05-20
re: Does the order of criteria the WHERE clause matter?
| Boni Spielautomat - your point was about the case when you omit the index? what about changing/reversing the order of the conditions in the where clause? isnt query optimizer intelligent to figure it out?

corey bui
2010-11-27
re: Does the order of criteria the WHERE clause matter?
You can use SQL Locator to search for anything inside SQL server databases. Search results can be replaced, removed, saved, and shared with other people. It is easy to use and feature rich.

http://www.SQLLocator.com

order
2010-12-22
re: Does the order of criteria the WHERE clause matter?
I've seen the demo for the SQL locator - I think the order does matter, so that it can do a more narrow search for you. I haven't installed it yet, though!