Sql-server – How to remove a table partition

partitioningsql serversql-server-2012

I would like to remove a partition from a table in a SQL Server 2012 database. The table has a primary key but the partition column is not a primary key. I would also like to keep the data structure and schema intact.

I tried:

select * 
into [dbo].[new_Non_partitioned_Table] 
from [dbo].[partitioned_table]

I then dropped the partitioned table and renamed the new table. However, it caused me to lose the data structure and some dependency (FK) that forced me to drop constraints. This is for production and loss of data is not an option.

Is there a better way to remove a partition from an existing table?

Best Answer

Rebuild the indexes on the table to a filegroup instead of a partition schema.

Do so in the following order:

  • CREATE INDEX ON [MyFileGroup] WITH DROP EXISTING on the cluster index
  • CREATE INDEX ON [MyFileGroup] WITH DROP EXISTING on all non clustered indexes (one at a time)

If you can take some downtime, consider scripting and dropping the non-clustered indexes first, rebuild the cluster index and then reapply the non-clustered indexes (targeted at the filegroup, not the partition schema of course)

This is faster than merging the partitions together because it can run at high concurrency using the SQL Server index build engine.

With this trick, the schema remains in place, no need to move it all to another table. Depending on your SQL Server edition, you may even be able to do this online.

Related Question