Alex Rivera | Logout

Should a Composite Primary Key be clustered in SQL Server?

Asked 2008-12-23T16:29:34.720
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.

Edit
Report

1 Answer

2

The real question here is what will you be querying on the most? If you will be looking for both values all the time, then the clustered should be on the pair. If you are going to query more heavily on one or the other you would want the clustered on that specific one.

answered 2008-12-23T16:32:40.470

Your Answer