How can I move a clustered primary key to a new filegroup? I already have found a possible "algorithm" but it is horribly inefficient:
- Drop non-clustered indexed (requires them to be resorted and rebuilt)
- Drop clustered index (requires the whole table to be resorted)
- Create new primary key constraint (huge sort operation)
- Create all non-clustered indexes (sorting and writing required)
Is there a more efficient way? This is horribly inefficient and will take a long time as the table is 50GB in size on a weak server.
Isn't there a way to skip all of these and just do a rebuild on a new filegroup? That would not require any sorting of data.