Moving a primary key

Moving a primary key

I recently had some fun un-partitioning some tables.  Deleting the schemes, functions, and the files/filegroups means getting the data out of there.

Now if you have a clustered primary key, moving the data can be a real pain.  You typically will have to drop all the constraints using that primary key, drop the primary constraint, and then add it back on the new filegroup.

While playing with the syntax I stumbled upon this, and it actually worked:

CREATE UNIQUE CLUSTERED INDEX PK_NAME ON TableName(PK_Column)
WITH (DROP_EXISTING = ON) ON [YourFileGroupHere]

example:

create unique clustered index PK_Members_ID on Members(ID)
WITH (DROP_EXISTING = ON, FILLFACTOR = 100)
ON [PRIMARY]

The wonderful thing about this is you don't have to drop the foreign keys, drop the primary key, recreate everything.  One command, in and out.

Make sure you have the database to yourself! It can be a timely operation to complete.