Primary Key Questions

I recommend Option 2 because a clustered index on model_id and color_id could potentially generate a lot of I/O if yous are inserted in between.

Let's say that you inserted rows in this sequence:

1. 10,000 rows for model_id 1/color_id 1


2. 15,000 rows for model_id 1/color_id 2


3. 25,000 rows for model_id 2/color_id 2.

If you then, try to insert 20,000 rows for model_id 2/color_id 1, the SQL Server engine will have to reorganize the index.

As a general rule of thumb, only create cluster indexes (PK as clustered), if are sure rows are never going to be inserted out of sequence.

Kind regards,

M

---

Kind regards M