Removing Partitioning on tables
I answered a question on the newsgroups the other week about removing partitioning from a partitioned table. The answer is very similar to the way that you partitioned the table in the first place so you may want to read my previous post on this.
If you had the following table with a partitioned clustered and non-clustered index:
CREATE DATABASE partitioneddb
GO
USE partitioneddb
GO
DECLARE @InitialPartitionDate date = '20090201';
CREATE PARTITION FUNCTION PartitionFunction (datetime)
AS RANGE LEFT FOR VALUES (@InitialPartitionDate);
GO
CREATE PARTITION SCHEME PartitionScheme
AS PARTITION PartitionFunction
ALL TO ( [PRIMARY] );
GO
CREATE TABLE [dbo].[PARTITION_TABLE]
( id int not null identity,
indexedcol int not null,
PARTITION_COLUMN datetime not null );
GO
CREATE CLUSTERED INDEX [idx_PARTITION_TABLE] ON [dbo].[PARTITION_TABLE] ( id )
ON [PartitionScheme]([PARTITION_COLUMN]);
GO
CREATE INDEX ncidx_PARTITION_TABLE ON [dbo].[PARTITION_TABLE] ( indexedcol ) ;
GO
You will see that each index has two partitions:
SELECT LEFT(OBJECT_NAME(object_id),16) as [Table Name],
[partition_id],
[object_id],
[index_id],
[partition_number]
FROM sys.partitions
WHERE OBJECT_NAME(object_id) = 'PARTITION_TABLE' ;
GO
/*
Table Name partition_id object_id index_id partition_number
---------------- -------------------- ----------- ----------- ----------------
PARTITION_TABLE 72057594038845440 2105058535 1 1
PARTITION_TABLE 72057594038910976 2105058535 1 2
PARTITION_TABLE 72057594038976512 2105058535 2 1
PARTITION_TABLE 72057594039042048 2105058535 2 2
*/
And each index is using the partition scheme:
SELECT LEFT(OBJECT_NAME(i.object_id),16) as [Table Name],
LEFT(d.name,16) as [Partition/Filegroup Name],
LEFT(i.name,22) as [Index Name],
i.index_id,
i.[type],
LEFT(i.[type_desc],12) AS [type_desc]
FROM sys.indexes i
JOIN sys.data_spaces d ON i.data_space_id = d.data_space_id
WHERE OBJECT_NAME(i.object_id) = 'PARTITION_TABLE' ;
GO
/*
Table Name Partition/Filegroup Name Index Name index_id type type_desc
---------------- ------------------------ ---------------------- ----------- ---- ------------
PARTITION_TABLE PartitionScheme idx_PARTITION_TABLE 1 1 CLUSTERED
PARTITION_TABLE PartitionScheme ncidx_PARTITION_TABLE 2 2 NONCLUSTERED
*/
To move the data from the partition scheme to a filegroup all you need to do is rebuild the clustered index using the filegroup instead of the partition scheme. (Note: this may be any filegroup not just PRIMARY)
CREATE CLUSTERED INDEX idx_PARTITION_TABLE ON [dbo].[PARTITION_TABLE] ( id )
WITH ( DROP_EXISTING = on )
ON [PRIMARY];
GO
The clustered index will then only have one partition:
SELECT LEFT(OBJECT_NAME(object_id),16) as [Table Name],
[partition_id],
[object_id],
[index_id],
[partition_number]
FROM sys.partitions
WHERE OBJECT_NAME(object_id) = 'PARTITION_TABLE' ;
GO
/*
Table Name partition_id object_id index_id partition_number
---------------- -------------------- ----------- ----------- ----------------
PARTITION_TABLE 72057594039107584 2105058535 1 1
PARTITION_TABLE 72057594039173120 2105058535 2 1
PARTITION_TABLE 72057594039238656 2105058535 2 2
*/
This leaves the clustered index (i.e. data) using the filegroup:
SELECT LEFT(OBJECT_NAME(i.object_id),16) as [Table Name],
LEFT(d.name,16) as [Partition/Filegroup Name],
LEFT(i.name,22) as [Index Name],
i.index_id,
i.[type],
LEFT(i.[type_desc],12) AS [type_desc]
FROM sys.indexes i
JOIN sys.data_spaces d ON i.data_space_id = d.data_space_id
WHERE OBJECT_NAME(i.object_id) = 'PARTITION_TABLE' ;
GO
/*Table Name Partition/Filegroup Name Index Name index_id type type_desc
---------------- ------------------------ ---------------------- ----------- ---- ------------
PARTITION_TABLE PRIMARY idx_PARTITION_TABLE 1 1 CLUSTERED
PARTITION_TABLE PartitionScheme ncidx_PARTITION_TABLE 2 2 NONCLUSTERED
*/
i.e. any non-clustered indexes will still be partitioned and you need to re-create them also:
CREATE INDEX ncidx_PARTITION_TABLE ON [dbo].[PARTITION_TABLE] ( indexedcol )
WITH ( DROP_EXISTING = on )
GO
SELECT LEFT(OBJECT_NAME(i.object_id),16) as [Table Name],
LEFT(d.name,16) as [Partition/Filegroup Name],
LEFT(i.name,22) as [Index Name],
i.index_id,
i.[type],
LEFT(i.[type_desc],12) AS [type_desc]
FROM sys.indexes i
JOIN sys.data_spaces d ON i.data_space_id = d.data_space_id
WHERE OBJECT_NAME(i.object_id) = 'PARTITION_TABLE' ;
GO
/*
Table Name Partition/Filegroup Name Index Name index_id type type_desc
---------------- ------------------------ ---------------------- ----------- ---- ------------
PARTITION_TABLE PRIMARY idx_PARTITION_TABLE 1 1 CLUSTERED
PARTITION_TABLE PRIMARY ncidx_PARTITION_TABLE 2 2 NONCLUSTERED
*/
If nothing is using the partition scheme and function they can then be dropped:DROP PARTITION SCHEME PartitionScheme ;
GO
DROP PARTITION FUNCTION PartitionFunction ;
GO