Was scanning through a SQL2005 database and saw the following two indexes for a table:
**PK_CLUSTERED_INDEX**
USER_ID
COMPANY_ID
DEPARTMENT_ID
**NON-unique_NON-clustered_INDEX**
USER_ID
COMPANY_ID
My initial thought is, drop the last index since the PK_CLUSTERED_INDEX already contains those columns, correct order and sort. Do the last index provide any gains at all?
In this case, drop the index, given it's non-unique, I would bet the optimizer never hits it, the first index is more unique and doesn't involve a row lookup after finding a match.
First one's all around better, you're not losing anything by dropping the second.
I would drop the NON-unique_NON-clustered_INDEX index, it is redundant and not needed.
If searching by User_ID, or User_ID and Company_ID columns, both indexes could fulfil that. However, only the PK index would then be ideal if the Department_Id is also queried on in addition to those 2 fields.
If a query filters on User_ID and Company_ID, and needs to return other data columns then the PK index is still best as it has all the data there to hand. Whereas the nonclustered index doesn't so would probably need a Key Lookup to pull out the extra fields which is not as efficient.
It looks redundant to me, so I'd definitely be considering removing it.
To see whether an index is actually being used/get a feel for the level of usage, you can run one of the various index usage stats scripts out there. A good example is here.
Thanks guys!
Its always nice to get this sort of confirmation/help. Will continue to monitor the the post if anyone else thinks different from "us". Thanks again!