We have some data saved for reporting each of our tables currently has a unique id column that has an auto generated clustered index on the pk and a non-clustered index for the fk.

I am thinking that we could replace these with one fk index since we never really access the tables by the id only the fk.

for example two tables

(id bigint,
 itemid bigint

 id bigint,
 reportdataid bigint,
 decimal cost,

as I see it reportdatacosts has no need for an index on id because it is always accessed by the reportdataid so this is a perfect candidate for changing to just use clustered index. So I should change this to be a clustered index on reportdataid then id, right?

The table reportdata needs the id to be indexed for the fk on reportdatacosts to work so for this I am unsure. I believe we could perhaps get benefits by removing the fk and because this table is only populated in one place in the code it should not get bad data in. So if we remove teh fk and change to just the clustered index is there any drawback. Could we expect much benefit or might this give worse performance?

Edit: I'm trying to understand when a clustered index would not be just on the pk - this is the only example I can think of

1 Answer 1


Your problem will come in when you insert data into the ReportDataCosts table, as it will have to perform splits to insert the data, with a clustered index on the FK.

If you're so hell-bent on a clustered index on the FK, why not widen the PK to include the FK and make that clustered?

  • So the pk would be a combination of reportdataid and id? Is there ever a good reason to have clustered index not on pk? Commented Jan 26, 2012 at 6:01
  • 1
    @AdamButler: Having your "surrogate key "(IDENTITY) as the PK is a choice to replace the "natural key". Whether you cluster the PK or another index is a separate decision. DOn't conflate index type with keys, and don't assume all PKs are IDENTITY
    – gbn
    Commented Jan 26, 2012 at 8:03
  • 1
    You don't need a clustered index on a PK, but keep in mind that having a cluster on the FK may result in page splits over time, because your table will add new rows to ensure that the clustered index is logically sorted according to the FK. Depending on the size of the table, and your fill factor, this may not even be an issue. That's my only flag to raise.
    – user1240
    Commented Jan 26, 2012 at 8:04
  • Good point, @gbn.
    – user1240
    Commented Jan 26, 2012 at 8:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.