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?

  • 4
    @marc_s: it's not just DBAs in the "backup+restore monkey" sense. There are BI, NoSQL and advanced SQL questions. And not just SQL Server. Come and see chat.stackexchange.com/rooms/179/the-heap – gbn Dec 8 '11 at 21:23
  • ^ yes, that's an advert. Nothing personal :-) – gbn Dec 8 '11 at 21:28
  • Is there anything specific you're worried might happen, or are you just asking for any general precautions to take for this operation? – Nick Chammas Dec 8 '11 at 22:17
  • @Nick Chammas: Mostly general precaution, but I was thinking about how if a table doesn't have an existing clustered index, and then you add a primary key, it creates a clustered index, and I'm concerned that using the create index statement, it's dropping the PK because of this. Then of course, when it recreates it on the new filegroup, it does it as a regular index or something, and possibly killing any relational dependancies. – Zap Dec 8 '11 at 23:35
  • Fair enough. Show us the script you intend to use for the move, along with the DDL for the table. – Nick Chammas Dec 8 '11 at 23:51


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

  • Ok, so just to be clear, performing this action, isn't going to say drop any primary keys or anything like this, correct? It's just going to move the actual data to the new filegroup and not reseting anything (as far as data I mean)? And thanks! – user1088571 Dec 8 '11 at 21:30
  • @user1088571: Correct. You are simply changing physical disk location: no other logical or DDL changes will happen – gbn Dec 8 '11 at 21:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.