Monday, March 28, 2011

Can I partition a DB table after it is already created on SQL 2005

Most examples dealing with table partitions, create the table on the partition scheme.

For example:

create table SomeTable
(
  Id int not null ,
  DueDate DateTime not null
) on MyPartitionScheme(DueDate)

Where MyPartitionScheme is a predefined partition scheme.

If I have a table that already exists, possibly with data with it. Can I "alter" it to use the partition scheme?

From stackoverflow
  • lookup ALTER TABLE SWITCH TO PARTITION in BOL

    bdukes : Does this just move the data on one partition to another table?
  • From Microsoft SQL Server 2005 Implementation and Maintenance:

    1. Create a partition function
    2. Create a partition scheme
    3. Drop the existing clustered index
    4. Re-create the clustered index on the partition scheme
    Ron Harlev : Won't this just partition the clustered index and not the table storage itself?
    bdukes : the table is stored in the clustered index. that's what a clustered index is
  • In Partitioned Tables and Indexes in SQL Server 2005 clearly states:

    "By defining a clustered index on a partition scheme, you will effectively partition the table after the load. This is also a great way of partitioning an existing table. To create the same table as a nonpartitioned table, and create the clustered index as a partitioned clustered index, replace the ON clause in the create table with a single filegroup destination. Then, create the clustered index on the partition scheme after the data is loaded."

0 comments:

Post a Comment