Does an index where the last column is unique and already indexed make sense? 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 RetailerID and ID (or just ID).

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 RetailerID include 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 ID).
I can think about one scenario where such index is needed. If you want another table to have a Foreign Key constraint that refers (RetailerID, ID), you must create a unique constraint (index).  This pattern makes sense in some cases, for instance if you want to have a common parent table which holds common attributes, and multiple detail tables, and at the same time enforce that details are saved in the proper table.  

Update. Example to illustrate an idea  : 

vehicle (vehicle_id, vehicle_type_id, year, model, ...
         PK (vehicle_id),  
         Unique (vehicle_type_id,car_id),
         CHECK (vehicle_type_id IN ('car','truck'))
);  

car (vehicle_id, vehicle_type_id default 'car', 
     num_passengers, car_type, ....
     PK,FK(vehicle_id, vehicle_type_id) ref vehicle, 
     CHECK (vehicle_type_id ='car')
); 

truck (vehicle_id, vehicle_type_id default 'truck',  load, 
     dimensions, ... ,  
     PK,FK(vehicle_id, vehicle_type_id) ref vehicle, 
     CHECK (vehicle_type_id ='truck')
);


Thus you enforce that truck's attributes are always stored in truck table, car's - in car, and at the same time refer to vehicle if vehicle type is not important, say vehicle_owner  can look like vehicle_id, person_id, date_from ,date_thru, FK (vehicle_Id) ref vehicle(vehicle_id)).