4

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?

Mark Storey-Smith
  • 31,860
  • 9
  • 90
  • 125
user42148
  • 177
  • 2
  • 4
  • 9

2 Answers2

7

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.

Thomas Kejser
  • 6,218
  • 2
  • 24
  • 46
4

If you want to remove the partition boundary the MERGE statement will do that:-

ALTER PARTITION FUNCTION PartitionFunctionName()
MERGE RANGE (Boundary Value);
GO

If you want to move the data itself out of the partitioned table, you have to have a table for the data to be 'switched' into. The second table must have exactly the same structure and constraints as the original table. You can then run the following:-

ALTER TABLE dbo.[Original Table]
SWITCH PARTITION 1
TO dbo.[Switch Table];
GO

I wrote a few posts on managing partitons on my blog here:- http://dbafromthecold.wordpress.com/2014/06/04/partitioning-basics-part-1/

Andrew

dbafromthecold
  • 982
  • 1
  • 9
  • 19