Sql-server – Move Primary Key to Filegroup (SQL Server 2012)

filegroupsindexsql serversql-server-2012

How can I move a clustered primary key to a new filegroup? I already have found a possible "algorithm" but it is horribly inefficient:

  1. Drop non-clustered indexed (requires them to be resorted and rebuilt)
  2. Drop clustered index (requires the whole table to be resorted)
  3. Create new primary key constraint (huge sort operation)
  4. 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.

Best Answer

CREATE UNIQUE CLUSTERED INDEX Your_PK_Name
    ON YourTable(YourColumnList)
WITH (DROP_EXISTING =  ON )
ON [YourOtherFileGroup]

This preserves the logical PK property despite it not being mentioned in the syntax.

Related Question