Joe Webb

Musing and observations about SQL Server, other technogies, and sometimes just life in general
posts - 85, comments - 857, trackbacks - 0

My Links

News

This blog has moved!

Click here for the new location.

Follow me on Twitter
Add to Technorati Favorites

Search this Blog
 




Archives

Post Categories

About me

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

Print | posted on Friday, February 22, 2008 10:37 AM | Filed Under [ T-SQL SQL Server ]

Feedback

Gravatar

# 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
2/24/2008 3:50 AM | Raj
Gravatar

# re: Does the order of criteria the WHERE clause matter?

yah, but it might make things a lot more difficult for users on dialup
3/27/2008 11:13 AM | anonymous
Gravatar

# re: Does the order of criteria the WHERE clause matter?

Ha Dial-up?!?! who cares, get broadband
12/17/2009 10:24 AM | c
Gravatar

# 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
2/17/2010 10:39 PM | Boni Spielautomat
Gravatar

# 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?
5/20/2010 1:06 AM | Murukesh
Gravatar

# re: Does the order of criteria the WHERE clause matter?

the index is of no use to SQL Server for resolving that query. To see the proof, refer to this post.
10/15/2010 12:44 AM | columbia jackets
Gravatar

# re: Does the order of criteria the WHERE clause matter?

Let's look at the execution plan so see if that makes any difference to SQL Server.
10/15/2010 12:52 AM | snow boots for women
Gravatar

# re: Does the order of criteria the WHERE clause matter?

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.

furry boots | womens snow boots | columbia outlet | columbia sportswear jackets | the north face jackets | north face jacket | cheap mac makeup | discount makeup
10/19/2010 4:26 AM | fur boots
Gravatar

# re: Does the order of criteria the WHERE clause matter?

This super video converter for mac is developed by Emicsoft Studio, it is currently the best video converter running under Mac os x, comparied by isqunite, Visualhub and other Video Converter for Mac Free vide under "format" option choose the output video formats by your needs, such as to mp4 for ipod, iphone, zune, psp, creative zen
10/26/2010 1:32 AM | hanly
Gravatar

# 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
11/27/2010 11:34 AM | corey bui
Gravatar

# 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!
12/22/2010 9:28 AM | order
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET