In this article I am going to explain that the order of the columns in an index is important.

Let's look at some examples. I am using the Person.Contact table from AdventureWorks. Create a non-clustered composite index as follows.

CREATE INDEX NonClust_Contact_Name
ON Person.Contact(LastName, FirstName);

A composite index is an index made up of more than one column. The main reason composite indexes are used is to make an index more selective.

Let's run the following query to find Contact named Catherine Abel.

select
 lastname
 ,firstname
 ,emailaddress
from
 Person.Contact
where lastname='Abel' and 
 firstname='Catherine'

If the non clustered index NonClust_Contact_Name wasn't there it would have searched the entire table to satisfy the query. With the non clustered index there it does an index seek as shown in Fig 1.

Fig 1

To retrieve the emailaddress a key lookup was used for each row. If you notice the WHERE clause in the select statement it appears in the same order as the 'index NonClust_Contact_Name'.

Let's now reverse the order of the WHERE clause in the select statement. If you now run the select statement shown below you will still see the same execution plan as shown in Fig 1.

select
 lastname
 ,firstname
 ,emailaddress
from Person.Contact
where firstname='Catherine' 
and lastname='Abel'

You are seeing the same execution plan because SQL server is still using the same index 'NonClust_Contact_Name'. But this is not always the case.

Consider the select statement below. I've removed the firstname from the WHERE clause.

select
 lastname
 ,firstname
 ,emailaddress
from
 Person.Contact
where lastname='Abel'

When you look at the execution plan, you will still see the same execution plan as before (Fig 1). This is because the lastname in the WHERE clause is still in the order of the index 'NonClust_Contact_Name'.

Let's now do the magic. Change the WHERE clause to firstname='Catherine'. Instead of lastname you are now searching for the firstname. The select statement is shown below.

select
 lastname
 ,firstname
 ,emailaddress
from
 Person.Contact
where firstname='Catherine'

The execution plan that you will get is shown in Fig 2.

Fig 2

If you notice SQL server is now using index scan operation to satisfy the query instead of the index seek as in fig 1. So why is this happening? Consider a telephone book. If you were to search for the firstname of Catherine, the telephone book wouldn't be very useful as you will have to start on the first page and search for every entry to see if the first name is Catherine. This is because telephone book is not sorted by firstname; it is sorted by lastname, firstname. Similarly in SQL Server, an index cannot be used to get rows of data if the first column from the index is not used in the WHERE clause.

This does not mean that the index 'NonClust_Contact_Name' was not useful. It's still much better than the Clustered scan. So if ths index did not exist, SQL Server would have done a clustered scan or a table scan which is a more expensive operation.

This article was to show you that the order of the column in an index does matter.