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

Friday, February 22, 2008

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

posted @ Friday, February 22, 2008 10:37 AM | Feedback (11) | Filed Under [ T-SQL SQL Server ]

Powered by:
Powered By Subtext Powered By ASP.NET