Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am deploying a mapping table which connects Items to ItemGroups

Though an Item will only ever be in a single ItemGroup please accept a priori it is not appropriate in my use case to append a column to the Items table.

So a table will be created with two columns:

ItemId INT NOT NULL
ItemGroupId INT NOT NULL

It is tempting to make ItemId the CI and PK for this table.... but is that appropriate? I think it makes sense as a primary key but since it is neither sequential nor perpetually-increasing it seems incorrect as a CI. I will never execute range queries against ItemId

... But a surrogate key solely for the purpose of a sequential CI also doesn't seem appropriate.

I am equally likely to execute either:

SELECT [ItemGroupId] FROM [Table] WHERE [ItemId] = @id
SELECT [ItemId] FROM [Table] WHERE [ItemGroupId] = @id

So as far as I can tell I have few options:

  1. ItemId is both the CI and the PK
  2. ItemId is not the CI, but it is the PK
  3. ItemId is neither the CI nor the PK but has a UNIQUE constraint
  4. ItemId, ItemGroupId combination becomes the PK and ItemId has a UNIQUE constraint
share|improve this question

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer
    
I understand your recommendation pertaining to the lookups and the range scan; I hadn't thought of that... But the ItemGroupId is certainly not sequential. Won't I run into performance issues in INSERT caused by the page splits? Or is that a consequence I should consider accepting for the resulting performance of the range scans? –  Matthew Jun 17 at 16:37
    
@Matthew: I use this pattern with random GUIDs. If you're adamant about a sequential clustering key, the only sure-fire way is to add a surrogate column that's sequential, cluster on that, and implement the other two indexes as nonclustered. But this will use more storage and also add overhead to data modifications because now 3 indexes have to be maintained instead of 2, and the nonclustered indexes will still be fragmented. Is that more or less expensive than accepting page splits? Only you can answer that. (Tip: make sure you have good index maintenance procedures in place.) –  Jon Seigel Jun 17 at 18:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.