CODEWITHSUNDEEP
sql-serversql-server-2005partitioning
Ron Harlev
Ron Harlev

Reputation: 16703

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?

Upvotes: 0

Views: 711

Answers (3)

Ron Harlev
Ron Harlev

Reputation: 16703

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."

Upvotes: 0

bdukes
bdukes

Reputation: 156025

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

Upvotes: 3

SQLMenace
SQLMenace

Reputation: 135141

lookup ALTER TABLE SWITCH TO PARTITION in BOL

Upvotes: 0

Related Questions