Post reply

Change tables from Partitioned to non-Partitioned

  • October 9, 2013 at 9:22 pm

    #281120

    We're considering moving a database from SQL 2012 Enterprise to Standard, but it has a number of partitioned tables I'd like to change to be non-partitioned. I found this link which gives some examples of doing this:

    http://www.patrickkeisler.com/2013/01/how-to-remove-undo-table-partitioning.html

    But I'm curious to know if I can just create new tables on [PRIMARY], copy the data from the partitioned tables to the unpartitioned tables, delete the original partitioned tables, then delete the partitioned schemes and functions. The tables I'm looking at are all heaps so no indexes.

    Thanks for any guidance or suggestions.

  • October 10, 2013 at 3:49 am

    #1657425

    samalex (10/9/2013)

    The tables I'm looking at are all heaps so no indexes.

    obviously those tables will be HEAP. but by the way why are not keoing the conept of paritioning here.?

    you had implemented the partitioning which measn that there is heavy data involve and how are you goinf to handle in it new environment,

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • October 10, 2013 at 5:59 am

    #1657471

    wrong thread :Whistling:

    The SQL Guy @ blogspot[/url]

    @SeanPearceSQL

    About Me[/url]

  • October 10, 2013 at 7:41 am

    #1657545

    Bhuvnesh (10/10/2013)

    samalex (10/9/2013)

    The tables I'm looking at are all heaps so no indexes.

    obviously those tables will be HEAP. but by the way why are not keoing the conept of paritioning here.?

    you had implemented the partitioning which measn that there is heavy data involve and how are you goinf to handle in it new environment,

    My predecessor enabled partitioning mostly as a test on these tables from what I could tell, and given each partition is going to the same PRIMARY filegroup there's really not much advantage to the way it's setup. The primary reason we want to remove partitioning is so we can move this instance from Enterprise Edition to Standard Edition, and this is the only Enterprise-only feature I've seen in play.

    In my testing I've found that I can create a new table on PRIMARY, say I have tblUsers that's partitioned I'd create tblUsers_New with the exact same schema, copy the data from tblUsers to tblUsers_New, drop tblUsers, then rename tblUsers_New to tblUsers. I just hoped someone could confirm there's no simpler way to do this since the URL I referenced before seemed to be rather involved.

    Thanks,

    Sam

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply