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?

Improve this question
1
  • 5
    No, they are not the same - in the key list, order matters. (In the INCLUDE list, order does not matter.)
    – Aaron Bertrand
    Commented Nov 11, 2014 at 17:44

1 Answer 1

Reset to default
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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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