Hey PiMané I would recommend that you take your SW vendor out into the woods for a good spanking for making a clustered index on UNIQUEIDENTIFIER column. You are stuck with your table structure and refactoring the indexes is just about the only plan.

<Rant> Please under stand that the terms "Clustered Index" and "Primary Key" are not the same thing, have never been the same thing, and hopefully never will be the same thing. Just because that's what happens in SSMS when you click on the little key does not mean that it is required or even right </Rant>

So far you have the essence of a plan. The plan is for each affected table one at a time.

Step 0: Backup! - Stop all access to the database and do a backup just for the purpose of these steps.

Step 1: Script out the creation of all the FKs that point to this table.

Step 2: Drop the FKs

Step 3: Drop the clustered index. This renders the table as a heap but that is OK. The data will be just fine.

Step 4: Create a non-clustered index. Keep the name on the index the same as the original. It must have the unique attribute. To keep your software from freaking out nominate this as the primary key.

Step 5: Put back the FKs.

Step 6: TEST! TEST! TEST!

======================

It's bunch of work to be sure. The fine point here are: an FK must point to a unique index on the primary table. It does not have to be the PK or a clustered index. Just unique. I would keep the name the same even if it violates every convention in any book. Some software checks for the presence of indexes by name. Same thing with the PK designation.

For step 4 SSMS can be helpful. You can script out the current clustered index and change the clustered keyword to non-clustered.

Dropping the clustered index will force a rebuild of all the other indexes on the same table. They will be somewhat smaller by the virtue of the fact that the pointer will be the 8 byte RID rather than the 16 byte UNIQUEIDENTIFIER It saves 8 bytes per index entry and with 10K new rows per day that can add up.

The bad news is that even this wont end your fragmentation woes. Your SW generates a new UNIQUEIDENTIFIER for each row and you are stuck with that. Those inserts are going to be all over the place. You could set the fill factor to be stupid low but that would make the index crazy big.