12
Consider this example table (assuming SQL Server 2005):
create table product_bill_of_materials
(
parent_product_id int not null,
child_product_id int not null,
quantity int not null
)
I'm considering a composite primary key containing the two product_id columns (I'll definitely want a unique constraint) as opposed to a separate unique ID column. Question is, from a performance point of view, should that primary key be clustered?
Should I also create an index on each ID column so that lookups for the foreign keys are faster? I believe this table is going to get hit much more on reads than writes.