Column Order Sequence in a composite Non Clustered Index

A composite index is a Non clustered index created on two columns. An example below 

The question is does the order of column while creating the index matter? Below very simple example will make it clear 

Lets take a look at the query plan of two queries with a where clause on each column respectively.

1. Query 1 with where clause on FirstName which appears on right in the index does an index scan with a relative cost of 88% in query batch.
2. Query 2 with where clause on LastName which appears on left in the index does an index seek with a relative cost of only 12% in query batch. 

Conclusion here is such an index is only helpful when query involves left based subset of columns. Otherwise its a non clustered index scan, almost as bad as a table scan. So next time when you use Non clustered composite index take care of the order of columns while creating the index.