Tuesday, August 26, 2008

Converting a Partitioned Table to a Nonpartitioned Table In Sql Server 2005

Several months ago while working with Sql Server 2005 partitioned tables for the first time, I discovered an interesting bug/hidden feature that doesn't seem to be documented anywhere: Adding a clustered primary key constraint can quietly revert a partitioned table to a nonpartitioned one. At the time I found this behavior quite annoying, but it actually came in handy today when I needed to change the data type of a column used in the table's partition scheme from smalldatetime to datetime. Microsoft's knowledge base article on modifying partitioned tables indicates only that you may collapse multiple partitions into a single partition. It doesn't provide any options for departitioning tables--other than dropping and recreating them from scratch, of course.

To demonstrate departitioning we must first create a simple partitioned table. To do so execute this Sql:

   CREATE PARTITION FUNCTION MyPartitionRange (INT) 
   AS RANGE LEFT FOR VALUES (1,2) 

   CREATE PARTITION SCHEME MyPartitionScheme AS 
   PARTITION MyPartitionRange 
   ALL TO ([PRIMARY]) 

   CREATE TABLE MyPartitionedTable 
          ( 
          i INT NOT NULL, 
          s CHAR(8000) , 
          PartCol INT 
          ) 
   ON
    MyPartitionScheme (PartCol)     

(If you don't understand what's happening in each of the steps above, read this tutorial for more complete instructions--see "Creating the Partitioned Table"). Execute this Sql to see a list of partitions for the new table (you should see three):

   SELECT *
   FROM sys.partitions
   WHERE OBJECT_ID = OBJECT_ID('MyPartitionedTable')

Now, let's say this table has been running in production for several months, has lots of data, and you realize you need to expand PartCol to a bigint. You can't change the PartCol data type with an alter table statement:

   ALTER TABLE MyPartitionedTable ALTER COLUMN PartCol bigint

The Sql above fails with the somewhat obscure error "The object 'MyPartitionedTable' is dependent on column 'PartCol'." This won't change even if you collapse multiple partitions into a single partition using ALTER PARTITION with the SPLIT and MERGE options (the approach recommended by Microsoft), because the table is still partitioned on PartCol. Instead, you can execute this Sql to departition the table completely:

   ALTER TABLE MyPartitionedTable
   ADD CONSTRAINT [PK_MyPartitionedTable] PRIMARY KEY CLUSTERED([i]) ON [PRIMARY]

You should now see just one partition for this table in sys.partitions:

   SELECT *
   FROM sys.partitions
   WHERE OBJECT_ID = OBJECT_ID('MyPartitionedTable')

Other important points to note:

  1. This only works if the primary key column is not included in your partition function definition.
  2. If you forget to include "ON PRIMARY" when creating the primary key you'll run into another obscure error: "Column 'PartCol' is partitioning column of the index 'PK_MyPartitionedTable'. Partition columns for a unique index must be a subset of the index key." Sql Server is trying to tell you that you can't create a clustered index on "i" because it's not used in your partition function. In other words, you could create a clustered index including both "i" and "PartCol" and still maintain partitioning.
  3. A non-clustered primary key won't departition the table, even if you specify "ON PRIMARY".
  4. Adding any clustered index should accomplish the same thing--but I haven't verified this.

Finally you are free to change the PartCol data type. You can also repartition the table if necessary by following the steps in this article.

4 comments:

Unknown said...

This worked for me. However recreating the clustered index on the partitioned table removed all relationships to other tables. Any ideas how to preserve those?

Thanks,

Scott

AT said...

So other tables have foreign key columns pointing to the partitioned table's primary key? If that's the case, I don't know that you could preserve those relationships, since you're effectively dropping and recreating the primary key for the partitioned table. I believe your only recourse is to redefine the foreign key relationships.

Unknown said...

Thanks for the response. I thought that was the case. I think I'll use SQL Compare to rebuild them. I'll create an empty schema and then synch up after everything is done.

Thanks again for this tip. It's the only place I've found it.

AT said...

Actually, there is one thing you might try. As I mentioned at the end of the post, creating any clustered index should have the same effect as creating a clustered primary key.

Creating a clustered index on a different column (one that is not part of the primary key or the partition function) may departition the table without harming your relationships.

 
Header photo courtesy of: http://www.flickr.com/photos/tmartin/ / CC BY-NC 2.0