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.