Sometimes it becomes necessary to move tables to a different file group. This may be necessary if disk space runs out and a new one is added. In order to use the newly available space, a new file group needs to be created with ndf files assigned to it. Database objects can then be moved to this new file group residing on the newly available space.
Non clustered indexes and clustered indexes can be moved to the new file group
The process of moving a Non clustered index is fairly simple with the CREATE INDEX statement used in conjunction with the DROP EXISTING clause
Clustered indexes however in many cases are created along with the primary key constraint. In such a case, the clustered index cannot be dropped and re-created without dropping the constraint first
Below is an example of a simple table created with a CLUSTERED PRIMARY KEY
CREATE TABLE Index_Drop_Test
( c1 INT NOT NULL
,c2 INT
CONSTRAINT PK_Primary PRIMARY KEY CLUSTERED
)
When we attempt to move this table to another file group by dropping and re-creating the clustered index, the following error is encountered
SQL Statement to drop and re-create clustered index (not the right approach to use DROP INDEX)
DROP INDEX PK_Primary
ON dbo.Index_Drop_Test
Error Message
Msg 3723, Level 16, State 4, Line 1 An explicit DROP INDEX is not allowed on index 'dbo.Index_Drop_Test.PK_Primary'. It is being used for PRIMARY KEY constraint enforcement.
Correct Approach
1) Drop the constraint
ALTER TABLE Index_Drop_Test
DROP CONSTRAINT PK_Primary
2) Re-create the constraint on the secondary file group
ALTER TABLE Index_Drop_Test
ADD CONSTRAINT PK_Primary PRIMARY KEY CLUSTERED (c1)
ON SECONDARY
