Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

reportdata
(id bigint,
 itemid bigint
 ...
)

reportdatacosts
(
 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

share|improve this question
add comment

1 Answer

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?

share|improve this answer
    
So the pk would be a combination of reportdataid and id? Is there ever a good reason to have clustered index not on pk? –  Adam Butler Jan 26 '12 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 Jan 26 '12 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 Jan 26 '12 at 8:04
    
Good point, @gbn. –  user1240 Jan 26 '12 at 8:04
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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