Removing partitioning from a table

Question

Post reply

Removing partitioning from a table

winston Smith

SSCoach

Points: 19500
More actions
September 15, 2011 at 4:24 am

#244885

Im trying to un-partition a table. The table is just a heap, created on the partition scheme:
CREATE TABLE TestTable
(
ID INT NOT NULL,
DateCol DATETIME
)
ON YearlyPartitionScheme (DateCol);
GO
It has no indexes, clustered or otherwise. I checked after creating it, to ensure partitioning did not create any indexes i was not aware of.
So far the only way i can unpartition it is to copy the data to a non partitioned table. is there a way to un-partition a table with no indexes, besides copying the data to a new table?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

You could add a clustered index and then move it to the unpartitioned filegroup and drop the index.

-- Gianluca Sartori

winston Smith SSCoach Points: 19500 More actions · Answer 2

Your still copying data via a new structure. Can you just alter the table ter the table to remove the partition layout?

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

It makes a huge difference: you don't have to create a new table with all contraints, foreign keys, defaults and the like.

With the index create+move+drop you move just the logical storage.

However, I don't think there's another way.

-- Gianluca Sartori

winash SSChampion Points: 11446 More actions · Answer 4

Maybe using the MOVE clause of ALTER TABLE might help? I haven't tested this out - but I've used this to move a table onto a partition scheme and maybe the reverse works?

From BOL (http://msdn.microsoft.com/en-us/library/ms190273(v=SQL.100).aspx:

MOVE TO { partition_scheme_name ( column_name [ 1, ... n] ) | filegroup | "default" }

Specifies a location to move the data rows currently in the leaf level of the clustered index. The table is moved to the new location.

Note:

In this context, default is not a keyword. It is an identifier for the default filegroup and must be delimited, as in MOVE TO "default" or MOVE TO [default]. If "default" is specified, the QUOTED_IDENTIFIER option must be ON for the current session.

Edit: After re-reading the original question and answers- since the table has no clustered indexes you'd have to create an index and then use this option. And I've realized I'm echoing what Gianluca has already said 😛