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.

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.