Skip to Content
avatar image
Former Member

How do I unpartion a table partioned by hash ?

How do I unpartion a table partioned by hash ?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Jul 05, 2017 at 05:28 PM

    ALTER TABLE UNPARTITION can only be applied to tables that are partitioned by roundrobin.

    It seems a little silly, but the way to do it is to first modify the hash-partitioned table to roundrobin partitioning, then use the unpartition option:

    1> alter table mysalesdetail unpartition
    2> go
    Msg 13964, Level 16, State 1:
    Server 'DENN00641548A', Line 1:
    ALTER TABLE 'mysalesdetail' failed. Unpartition operation is only permitted on
    roundrobin partitioned tables. Table is partitioned by hash. Use the ALTER TABLE
    ... PARTITION BY command to create an unpartitioned table. This will involve
    data relocation.

    1> alter table mysalesdetail partition by roundrobin 3
    2> go
    (0 rows affected)
    1> alter table mysalesdetail unpartition
    2> go

    The table is now unpartitioned.

    Add comment
    10|10000 characters needed characters exceeded