Option 1.
Imagine your search / join scenarios: are you likely to be looking up a surrogate key value or the model_id and/or colour_id?
Most often and most likely: the latter. Therefore it makes sense for this to be your clustered index. [aside: remember that the primary key doesn't have to be the clustered index!]
Also, using a compound key in this scenario is much more obvious to other developers what your intention is and adding an arbitrary integer as a surrogate key will only widen the table.
Caveat: But as with all performance related questions the answer is always: it depends.
No comments:
Post a Comment