Remove table partitioning

• NicHopper

  SSCrazy Eights

  Points: 9090

  More actions

  August 2, 2021 at 3:07 pm

  Go to Answer

  #3916186

  Hi all,

  Just wanted to check something out if I may, we have a table (call it TableA) that is 29GB in size, its currently split over a number of partitions, I need to remove the partitioning and I wanted to check to see if there was another/easy way to do this, as of now my plan is;

  1. Script the FK's, indexes etc for the table (TableA)
  2. Create a new table called TableA_tmp  with no partition
  3. Copy all the data from TableA to TableA_tmp
  4. Drop TableA (this will involve dropping the scripted FK's)
  5. Rename TableA_tmp to be TableA
  6. Re-add all the Fk's,indexes etc
  7. Drop the partition function and scheme as they will no longer be used,

  So what do you think? does my plan sound about right or is there an easier way of doing this?

  Thanks,

  Nic

  Check out my blog http://www.sqlservercentral.com/articles/Best+Practices/61537/

• tripleAxe

  SSCertifiable

  Points: 5706

  More actions

  August 2, 2021 at 3:45 pm

  Answer

  #3916204

  Drop all the non-clustered indexes then re-create the clustered index with the DROP EXISTING option and use the FILEGROUP name instead of the partition scheme name, and then re-create the non-clustered indexes.

  Restore a copy of the database somewhere you can test first.

   

• NicHopper

  SSCrazy Eights

  Points: 9090

  More actions

  August 3, 2021 at 10:37 am

  #3916448

  Hi,

  Thanks for this, all work nicely.

  Nic

  Check out my blog http://www.sqlservercentral.com/articles/Best+Practices/61537/

• August 3, 2021 at 2:14 pm

  This was removed by the editor as SPAM