Should I use a surrogate key in a mapping table?
Option 5:


(ItemGroupId, ItemId) (note the order) as the primary key (clustered)
Unique constraint (non-clustered) on ItemId


While searching by ItemId is not conducive to range scans (yet?), searching by ItemGroupId is 1, which makes it a good candidate to be the leading column of the clustered index key.

My normal design for this type of table is to index both combinations, i.e., (Column1, Column2) and (Column2, Column1) so I (and others) never have to think about whether it's indexed properly; it just is. In your case here, one combination is degenerate and so only one column goes into the key, but the non-key column is still covered by the index because it's part of the clustered index key. The situation becomes more complicated, of course, if there are non-key columns involved, but that's beyond the scope of what you asked here.

In this case, while you could flip-flop the primary key with the unique constraint, I chose the 2-column primary key on purpose because it will be more stable during the life of the table. This works to your advantage as a DBA (one word: Replication) and also to developers (it could be quite a bit of code updates if the primary key definition changes).



1 There might be an issue with terminology here. Searching by a single ItemId will return 0 or 1 row; searching by a single ItemGroupId (an incomplete key) returns >= 0 rows, and so is a seek + scan (it will show up as an index seek in the execution plan). Obviously this isn't a "range scan" on multiple ItemGroupIds. The idea is to physically group together (and order, in this case) the ItemIds for every ItemGroupId. I hope that's clear.