How do I unpartion a table partioned by hash ?

[Former Member]

How do I unpartion a table partioned by hash ?

[former_member188958]

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.