sql-serverdatabasesql-server-2008partitioning

Undo Table Partitioning

I have a table 'X' and did the following:

  1. CREATE PARTITION FUNCTION PF1(INT) AS RANGE LEFT FOR VALUES (1, 2,
3, 4)

  2. CREATE PARTITION SCHEME PS1 AS PARTITION PF1 ALL TO ([PRIMARY])

  3. CREATE CLUSTERED INDEX CIDX_X ON X(col1) ON PS1(col1)

These 3 steps created 4 logical partitions of the data I had.

My question is, how do I revert this partitioning back to its original state?

Solution

  • After 2 days of continuous searching

    The Steps:

    1. DROP INDEX CIDX_X on X /* drop the clustered */
    2. CREATE CLUSTERED INDEX CIDX_X1 ON X(col1) ON [PRIMARY] /* Create another clustered index on the table to free it from the partitioning scheme; Here, the "ON [primary]" part is key to removing the partition scheme from the table ! */
    3. DROP PARTITION SCHEME PS1
    4. DROP PARTITION FUNCTION PF1
    5. DROP INDEX CIDX_X1 ON X /* drop the dummy clustered index you created, as it was only created to free the table from the partitioning scheme */