Partitioning Existing Tables

Published 22 July 09 07:00 PM | MartinBell

Table partitions have been available since SQL Server 2005 and there are plenty of examples of how to create them available on the internet such as Craig Freedman's blog and Nigel Rivett’s article as well as Books Online topics  So why the reason for this post? The title may have given you a clue!! I have not found any examples of changing an existing table to be partitioned.

If you have ever moved a table between filegroups, then you will already be familiar with the procedure required to partition a table, as it is effectively the same, although there are specific issues you may have to address.

So how do you do it?

For these examples I will use the following partition function and scheme:

DECLARE @InitialPartitionDate date = '20090201';
CREATE PARTITION FUNCTION PartitionFunction (datetime)
AS RANGE LEFT FOR VALUES (@InitialPartitionDate);

CREATE PARTITION SCHEME PartitionScheme
AS PARTITION PartitionFunction
ALL TO ( [PRIMARY] );
GO

If your table is a heap then you will need to create a clustered index that uses the partition scheme and then drop it. e.g. for the table:

CREATE TABLE [dbo].[PARTITION_TABLE]
( id int not null identity,
 PARTITION_COLUMN datetime not null
 ) ;
GO

Create the clustered index which uses the partition scheme:

CREATE CLUSTERED INDEX idx_PARTITION_TABLE ON [dbo].[PARTITION_TABLE] ( id )
ON [PartitionScheme]([PARTITION_COLUMN]);
GO

The following SQL will show the table partitions:

SELECT OBJECT_NAME(object_id),*
FROM sys.partitions
WHERE OBJECT_NAME(object_id) = 'PARTITION_TABLE' ;
GO

This will return the results similar to:

 

After dropping the index you will see that the table is still partitioned:

DROP INDEX idx_PARTITION_TABLE ON [dbo].[PARTITION_TABLE] ;
GO

SELECT OBJECT_NAME(object_id),*
FROM sys.partitions
WHERE OBJECT_NAME(object_id) = 'PARTITION_TABLE' ;
GO

 

You will notice that the index id which was previously the clustered index (1) is now the heap (0).

I received and email about doing this from Dave Levy who pointed out when you create the clustered indexes any non-clustered index will be re-built and when the index is dropped they will be re-built again, therefore you should consider if you need the table to remain as a heap.

If you already have a clustered index, then you will need to re-create the index, as the ALTER INDEX statement can’t be used to move an index to another filegroup or change the partition scheme you need to use CREATE INDEX and the DROP_EXISTING option.

e.g with the following table and index

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PARTITION_TABLE]') AND type in (N'U'))
      DROP TABLE [dbo].[PARTITION_TABLE] ;
GO

CREATE TABLE [dbo].[PARTITION_TABLE]
( id int not null identity,
 PARTITION_COLUMN datetime not null
 ) ;
GO

CREATE CLUSTERED INDEX idx_PARTITION_TABLE ON [dbo].[PARTITION_TABLE] ( id ) ;
GO

Check out the index columns with the following SQL:

SELECT t.name, c.name, i.*
FROM sys.index_columns i
JOIN sys.tables t ON t.object_id = i.object_id AND name = 'PARTITION_TABLE'
JOIN sys.columns c ON c.object_id = i.object_id AND c.column_id = i.column_id ;
GO

You will see something similar to:

 

CREATE CLUSTERED INDEX idx_PARTITION_TABLE ON [dbo].[PARTITION_TABLE] ( id )
WITH ( DROP_EXISTING = ON )
ON [PartitionScheme]([PARTITION_COLUMN]) ;
GO

Using the DROP_EXISTING clause will not re-built non-clustered indexes and so they will not be alligned with the partitions which I will look at in another blog post.

SELECT t.name, c.name, i.*
FROM sys.index_columns i
JOIN sys.tables t ON t.object_id = i.object_id AND name = 'PARTITION_TABLE'
JOIN sys.columns c ON c.object_id = i.object_id AND c.column_id = i.column_id ;
GO

If you then run the above query to view the index columns it will look like:

 

As you can see the partition column will also be added.

The partition column also has to be part of a unique index or clustered primary key, but in that situation it needs to be explicitly added to the index/primary key e.g. The following statement to create the tables cause errors:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PARTITION_TABLE]') AND type in (N'U'))
      DROP TABLE [dbo].[PARTITION_TABLE] ;
GO

CREATE TABLE [dbo].[PARTITION_TABLE]
( id int not null identity,
 PARTITION_COLUMN datetime not null,
 CONSTRAINT [pk_PARTITION_TABLE] PRIMARY KEY CLUSTERED ( id )
 )
 ON [PartitionScheme]([PARTITION_COLUMN]) ;
GO

/*
Msg 1908, Level 16, State 1, Line 1
Column 'PARTITION_COLUMN' is partitioning column of the index 'pk_PARTITION_TABLE'. Partition columns for a unique index must be a subset of the index key.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
*/

CREATE TABLE [dbo].[PARTITION_TABLE]
( id int not null identity unique,
 PARTITION_COLUMN datetime not null
 )
ON [PartitionScheme]([PARTITION_COLUMN]) ;
GO
 
/*
Msg 1908, Level 16, State 1, Line 1
Column 'PARTITION_COLUMN' is partitioning column of the index 'UQ__PARTITION_TABLE2__72C60C4A'. Partition columns for a unique index must be a subset of the index key.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
*/

Adding the partitioning column will solve this issue. So if you have a clustered primary key on your existing table, you will need to drop the constraint and re-create it with the extra column.

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PARTITION_TABLE]') AND type in (N'U'))
      DROP TABLE [dbo].[PARTITION_TABLE] ;
GO

CREATE TABLE [dbo].[PARTITION_TABLE]
( id int not null identity,
 PARTITION_COLUMN datetime not null,
 CONSTRAINT [pk_PARTITION_TABLE] PRIMARY KEY CLUSTERED ( id )
 );
GO

BEGIN TRANSACTION ;
ALTER TABLE [dbo].[PARTITION_TABLE] DROP CONSTRAINT [pk_PARTITION_TABLE] ;-- Primary key index has to be on partitioned column
ALTER TABLE [dbo].[PARTITION_TABLE] ADD  CONSTRAINT [pk_PARTITION_TABLE] PRIMARY KEY CLUSTERED
(
      [id],[PARTITION_COLUMN]
)
ON [PartitionScheme]([PARTITION_COLUMN]) ;
COMMIT TRANSACTION ;
GO 

SELECT OBJECT_NAME(object_id),*
FROM sys.partitions
WHERE OBJECT_NAME(object_id) = 'PARTITION_TABLE' ;
GO

Filed under: Partitioned Tables

Comments

# Dew Drop – July 23, 2009 | Alvin Ashcraft's Morning Dew said on July 23, 2009 02:09 PM:

Pingback from  Dew Drop – July 23, 2009 | Alvin Ashcraft's Morning Dew

# MartinBell said on February 5, 2010 11:37 AM:

I've updated this article following comments and a discussion with Dave Levy.

# Martin Bell UK SQL Server MVP said on July 25, 2010 05:52 PM:

In a previous post I talked about partitioning an existing table . I briefly talked about having to specify

# Martin Bell UK SQL Server MVP said on July 25, 2010 07:06 PM:

You may be wondering how to remove a table from a partition schema?