>Hello everyone,
>
>Assume a header/detail situation:
>
>Invoices table:
>Primary key: invoiceID (unique integer)
>
I sit next to a DBA now. I asked him. He reccommended option 3. So selects will be much faster, since the table will be in that order.

>InvoiceDetails table:
>lineID (unique integer)
>invoiceID (integer, relates to invoices table)
>
>The lineID of the detail table is good enough to be the primary key. However, all (or almost all) joins on this table will involve the invoiceID. So it makes sense for invoiceID to be part of a clustered index. For the most part, invoices will be inserted sequentially. Deletions shouldn't happen.
>
>In many cases, but not all, a query involving the invoiceDetails would be returning all the rows from that invoiceID.
>
>Some options I see for the detal table:
>
>1) primary key: lineID, but with a non-clustered index. clustered index on invoiceID.
>
>2) primary key: lineID, but with a non-clustered index. A clustered index on invoiceID + lineID.
>
>3) primary key: invoiceID + lineID, with clustered index on the combination.
>
>Is the best option one of these three? Or something else?