Undo Table Partitioning

Question

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)

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

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

Answer 1

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 */

Answer 2

I found that some additional steps should be added to the answer given by Storm, since, there are some points like ... before deleting the clustered index, the publication needs to be dropped because it wont allow the clustered index to get dropped before that and after dropping those partition-scheme and partition function needs to be recreated, and publication also again needs to be started.

Note: I am using SQL 2012
Primary key is normally the most commonly used clustered constraint, so wherever i used primary key, consider that those steps also apply to Clustered constraints too. Also, PS= Partition Scheme, PF = Partition Function.

Some preparatory steps:

1. Make a drop publication script
2. Make a create publication script
3. Identify all tables who have dependencies with the Partition Scheme,and also note down their primary key/clustered index.Also, note down all the fields which the primary key column uses (better to generate the script for primary key or clustered index)
4. Make a script for tables getting affected.
5. Make a script for PF and PS that are in question.

   All the above, using SQL Management Studio "generate scripts" upon right click. And choose drop and create.

Now, when everything is ready.
1. Drop the publication (use the script that you generated)
2. Drop the Primary key constraint of those tables which are linked with the PS. alter table [tablename] drop constraint [pk_name]
3. Make a dummy constraint but use the same fields that you used in that deleted primary key. CREATE CLUSTERED INDEX CIDX_X1 ON tablename(field1,field2,field3,field4) ON [PRIMARY]
4. drop the partition scheme and function.

DROP PARTITION SCHEME [PartitionSchemeName_PS]
GO

DROP PARTITION FUNCTION [PartitionfunctionName_PF]
GO

5. Drop the Dummy Index DROP INDEX CIDX_X1 ON tablename
6. Add back the Clustered index/primary key constraint(using the generated scripts as mentioned earlier.
7. Create the Partition function and Partition schema (ensure that the number of elements of PS > PF. where PS = PF+2 elements)
8. Add back the publisher (use the generated scripts)