What is the effect on primary keys when moving a CLUSTERED index to a new filegroup?

Posted on

Question :

I’m moving a CLUSTERED index from one filegroup to another, using the with drop_existing=on functionality.

Do I need to be concerned about primary keys? In other words, is there some special handling I need to do to retain the existing primary/unique key?

I have tables on the primary filegroup that I need to move to a new filegroup. There are some tables with and some without indexes, some with and some without clustered indexes, and amongst those, some with and some without Primary Keys.

I’m wondering if there is any special handling I should consider for the tables with Primary Keys during the CREATE INDEX statements?

Does this even matter in this scenario?

Answer :


From an “on disk” organisation perspective the fact it is a constraint doesn’t matter. That is, the physical and logical table layouts are separate

And PK when not clustered is just a unique non-clustered index with some rules

Leave a Reply

Your email address will not be published. Required fields are marked *