In effort to clean up database environments, we as DBAs are often asked to either move tables to a different file group or to consolidate multiple filegroups and the number of database files into one. The recommended way of accomplishing this task is to drop and create a clustered index on the new filegroup; however, whenever the clustered index is also a primary key, this process becomes very inefficient and resource-intensive since we have to drop all of the foreign keys, the clustered primary key, and then recreate the clustered primary key and all of its dependencies.

Good news- We have a much more efficient way around all of this.

Let us look at the below example using the AdventureWorks database and the Currency table that has a clustered primary key index and foreign key dependencies.

We have the clustered primary key index PK_Currency_CurrentCode that we want to move to a new database file and filegroup.

To show this using AdventureWorks, we first need to create a new filegroup and a new database file on that filegroup.

USE [master]
GO
ALTER DATABASE [AdventureWorks] ADD FILEGROUP [SALES]
GO
USE [master]
GO
ALTER DATABASE [AdventureWorks] ADD FILE ( NAME = N'AdventureWorks_SalesData', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATAAdventureWorks_SalesData.ndf' , SIZE = 131072KB , FILEGROWTH = 131072KB ) TO FILEGROUP [SALES]
GO

Again, the typical way of doing this would involve dropping all of the foreign key dependencies, dropping the clustered primary key index, recreating the primary key index on the new file and filegroup, and then recreating and checking the foreign key dependencies.

USE [AdventureWorks]
GO
ALTER TABLE [Sales].[CurrencyRate] DROP CONSTRAINT [FK_CurrencyRate_Currency_ToCurrencyCode]
GO
ALTER TABLE [Sales].[CountryRegionCurrency] DROP CONSTRAINT [FK_CountryRegionCurrency_Currency_CurrencyCode]
GO
ALTER TABLE [Sales].[CurrencyRate] DROP CONSTRAINT [FK_CurrencyRate_Currency_FromCurrencyCode]
GO
ALTER TABLE [Sales].[Currency] DROP CONSTRAINT [PK_Currency_CurrencyCode]
GO
ALTER TABLE [Sales].[Currency] ADD CONSTRAINT [PK_Currency_CurrencyCode] PRIMARY KEY CLUSTERED
(
[CurrencyCode] ASC
) ON [SALES]
GO
ALTER TABLE [Sales].[CurrencyRate] WITH CHECK ADD CONSTRAINT [FK_CurrencyRate_Currency_ToCurrencyCode] FOREIGN KEY([ToCurrencyCode])
REFERENCES [Sales].[Currency] ([CurrencyCode])
GO
ALTER TABLE [Sales].[CountryRegionCurrency] WITH CHECK ADD CONSTRAINT [FK_CountryRegionCurrency_Currency_CurrencyCode] FOREIGN KEY([CurrencyCode])
REFERENCES [Sales].[Currency] ([CurrencyCode])
GO
ALTER TABLE [Sales].[CurrencyRate] WITH CHECK ADD CONSTRAINT [FK_CurrencyRate_Currency_FromCurrencyCode] FOREIGN KEY([FromCurrencyCode])
REFERENCES [Sales].[Currency] ([CurrencyCode])
GO

As you can see, this is a lot of work. Instead, we can simply utilize the DROP_EXISTING option when creating the unique clustered index on the new filegroup. Also, we don’t need to bother with dropping all of the constraints and their dependencies on the table. The reason that drop/create is allowed on the index that is facilitating the PK constraint is due to the fact that we are not changing the index metadata; we are just moving it to a different storage location.

USE [AdventureWorks]
GO
CREATE UNIQUE CLUSTERED INDEX [PK_Currency_CurrencyCode] ON [Sales].[Currency]
(
[CurrencyCode] ASC
)WITH (DROP_EXISTING = ON) ON [SALES]
GO

After the change, we can confirm that our index resides on the SALES filegroup we created while preserving all of the primary key and foreign key dependencies.

As you can see, we accomplished the same task by doing a fraction of the work. Hopefully this tip will help you save a lot of late night hours, as we all know this is not something we can do during business hours in a production environment.