This question already has an answer here:
I found a unique index on
(RetailerID, ID) in our database. The
ID column is the primary key so it's already uniquely indexed. I can't see how an index on
(RetailerID, ID) could help when looking up an individual record by
ID (or just
I suspect it might be helpful when querying all the
IDs under a particular
RetailerID, since it covers such a query. Is that true?
But then why not just make the index on
ID? Perhaps the unique index on
(RetailerID, ID) covers the query in the same way but performs better as a result of being a unique index.
As I said, there's a unique primary clustering key on
ID alone. There's also a unique index on
(RetailerID, ID). Since
ID is already unique, querying
where RetailerID = x and ID = y shouldn't be any more efficient than simply querying
where ID = y.
(ID) is unique and
(RetailerID, ID) is unique, so
ID is associated with at most one
RetailerID. Therefore, if a predicate has
ID, then including
RetailerID in the predicate is redundant. The clustered index would always be faster.
An index on
(RetailerID, ID) could be helpful for a query where the predicate is just on
RetailerID and the select is on just
ID, because it would cover the query, but for queries where
ID is in the predicate,
RetailerID in the predicate is redundant, and since it has no other included columns, it would be less-efficient than the clustered index when selecting any column other than
ID. That's why I'm not seeing the point of this index, given the predicates used (which always involve