Clustered index on frequently changing reference table of one or more foreign keys
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 ?


Clustered index on frequently changing reference table of one or more foreign keys
===

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 ?

