SQL Server: changing the filegroup of an index (which is also a PK)

Question

We're doing a cleanup on a group of databases, and the first step is to get all indexes in the database into the correct filegroups.

Currently, those indexes are mixed between the DATA filegroup and the INDEXES filegroup; they all need to move to the INDEXES filegroup.

This can be done easily enough in script I guess, however how do you best handle an index on a Primary Key?

The following command

DROP INDEX table.indexname

produces the error:

An explicit DROP INDEX is not allowed on index 'Answer.PK_Answer'. It is being used for PRIMARY KEY constraint enforcement.

So what is the best way? Do I need to drop the Primary Key, then drop the Index, then re-create the primary key and finally re-create the index on the correct filegroup? Are there any drawbacks to this method?

Answer 1

You can try the below statement which drops and recreates the index on the index filegroup

CREATE CLUSTERED INDEX PK_Answer ON tablename(Answer) WITH (DROP_EXISTING = ON);

Answer 2

Since he has a primary key:

CREATE UNIQUE CLUSTERED INDEX PK_Answer ON tablename(Answer) WITH (DROP_EXISTING = ON);

Answer 3

In case anyone else needed this information (I did), add the FILEGROUP at the end if you wish to MOVE the recreated PRIMARY KEY to another location. Neither of the previous answers stipulated this portion:

CREATE UNIQUE CLUSTERED INDEX PK_TableName_Answer ON TableName(Answer) WITH(DROP_EXISTING = ON) ON [INDEX];