1

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?

  • 5
    No, they are not the same - in the key list, order matters. (In the INCLUDE list, order does not matter.) – Aaron Bertrand Nov 11 '14 at 17:44
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.