non clustered index on same table but the order is different?

Question

I have a table with 5 columns col1...col5 .

I need to create a non clustered index on col2,col3,col4. I did, then my boss is asking me to create one more non clustered index on col4,col3,col2. It doesn't make sense to me? Both are same right?

• Aaron Bertrand: No, they are not the same - in the key list, order matters. (In the INCLUDE list, order does not matter.)

Answer 1

It does matter some times. Colleen Morrow does a good demo on this at http://colleenmorrow.com/2011/04/07/composite-indexes-does-column-order-matter/

In essence she says that if your WHERE clause contains the first column in the index it will do an index seek. If it contains the third column in the index and not the first it will do an index scan which is not as good. In this case the new index would be better (assuming the optimizer chooses it). If your WHERE clause contains both columns then apparently either index would serve.