Clustered index on frequently changing reference table of one or more foreign keys
- by Ian
My specific concern is related to the performance of a clustered index on a reference table that has many rapid inserts and deletes.
Table 1 "Collection" collection_pk int (among other fields)
Table 2 "Item" item_pk int (among other fields)
Reference Table "Collection_Items" collection_pk int, item_pk int (combined primary key)
Because the primary key is composed of both pks, a clustered index is created and the data physically ordered in the table according to the combined keys.
I have many users creating and deleting collections and adding and removing items to those collections very frequently affecting the "Collection_Items" table, and its clustered index.
QUESTION PART: Since the "Collection_Items" table is so dynamic, wouldn't there be a big performance hit on constantly resorting the table rows because of the clustered index ?
If yes, what should I do to minimize this ?
