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.
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.
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
