Move clustered index to a new filegroup

Recreating clustered index

Today I had the task of moving the primary key index which is also a clustered index, to a new file group. But before I explain the procedure, it is interesting to note the necessity to do this.

During the planning phase, we had recommended that the table data and the index data be placed on two different LUNs. This would give the best IO performance. But unfortunately the vendor made a slight error in giving us the DDL for the tables and indexes.

The CREATE table command also added the clustered primary key constraints and specified the filegroup reserved for indexes for the clustered index. I guess they thought since our recommendation was to have all indexes on a separate filegroup, they would create the clustered index also on the index filegroup.

But as most of the DBAs know the clustered index is nothing but the table itself. So when the clustered index was created on the index filegroup it moved the data from the data filegroup to the index filegroup. So essentially we ended with both the data and the index on the same filegroup. The data filegroup was unused after this change.

But unfortunately the non clustered indexes had been created and the data was inserted.

Now if I tried to drop and recreate the  clustered index then the non-clustered indexes would be rebuilt twice.
There is no option but to rebuild the non clustered indexes. But if you do not drop the non clustered indexes first then they will get rebuilt twice. Once when you drop the clustered index and the second time when you create the clustered index. To avoid the trouble just drop all non clustered indexes before you drop the clustered index. This way you avoid rebuilding them at least one time.

So I had drop all indexes and then recreate them and this way I could avoid rebuilding the non clustered indexes twice.

Following is the summary of commands that I ran to achieve this.

1)      Script out the CREATE statements for the table including the CREATE statements for the indexes. (Always have a backup plan, if it helps take a backup of the database.

2)      Drop all the non clustered indexes.

            DROP INDEX [AK_Department_Name] ON [HumanResources].[Department]

WITH ( ONLINE = OFF )

3)      Drop the clustered index.

ALTER TABLE [HumanResources].[Department] DROP CONSTRAINT [PK_Department_DepartmentID]

4)      Create the new clustered index

ALTER TABLE [HumanResources].[Department] ADD  CONSTRAINT [PK_Department_DepartmentID] PRIMARY KEY CLUSTERED

(

            [DepartmentID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Do not forget to mention the new filegroup for the primary key index.

5)      Create all the remaining non clustered indexes and specify the index filegroup.

CREATE NONCLUSTERED INDEX [AK_Department_Name] ON [HumanResources].[Department]

(

            [Name] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,

      ALLOW_PAGE_LOCKS  = ON) ON [INDEXES]