sql - Partition scheme change with clustered Index -

i have table has 600 million records , has partition on ps_trpdate(trpdate) column, want change partition ps_lpdate(lpdate).

so have tried small amount of data following steps. 1) drop primary key constraints. 2) adding new primary key clustered index new partition ps_lpdate(lpdate).

is feasible 600 million records? can guide me it? , how works non partitioned tables?

--343

my gut feeling should create parallel table using new primary key, file groups , files.

to test out assumption, looked @ old blog post in stored first 5 million prime numbers 3 files / file groups.

i used tsql view kalen delaney wrote , modified standards @ partition information.

as can see, have 3 partitions based on primary key.

next, drop primary key on my_value column, create new column named chg_value, update prime number, , try create new primary key.

-- drop primary key (pk) alter table tbl_primes drop constraint [pk_tbl_primes]  -- add new field new pk alter table tbl_primes add chg_value bigint not null default (0)  -- update new field update tbl_primes set chg_value = my_value  -- try add new primary key alter table tbl_primes add constraint [pk_tbl_primes] primary key (chg_value) 

first, surprise partition still stayed after dropping pk. however, view shows index no longer exists.

second, end receiving following error during constraint creation.

while merge/switch partitions 1 file group not part of scheme, drop/create primary key, partition function & partition scheme, , move data yet again appropriate merge/switch statements, not.

this generate ton of work (tsql) , cause alot of i/o on disks.

i suggest build parallel partitioned table, if have space, new primary key. reload data old table new.

if not using data compression , have enterprise version of sql server, why not save bytes turning on.

good luck!

john

www.craftydba.com