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