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

Posted on

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?

Answer :

It does matter some times. Colleen Morrow does a good demo on this at

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.

Leave a Reply

Your email address will not be published. Required fields are marked *