What will happen if I drop an existing partition scheme/function from a table with (huge volume of records)?

Question

I have a few questions regarding Partitioning. I'm planning to drop the existing partition scheme/function in table1 and then point the new partition scheme/function to table1 . I know how to go ahead with the process, but before that I would like some suggestions/answers to the below questions:

1. If I drop the old/existing partition scheme/function with data in the table, what will happen to the data in old partition.ndf files? Will they be deleted or modified?

2. If I drop the old/existing partition scheme/function with data in the table, what will happen to the data in the table? Will there be any loss of records?

Answer 1

You can't drop partition schemes or functions that are in use.

USE tempdb

CREATE PARTITION FUNCTION yourmom ( INT )
    AS RANGE LEFT FOR VALUES ( 10, 20, 30, 40, 50 );

CREATE PARTITION SCHEME yourdad
    AS PARTITION yourmom
    ALL TO  ([PRIMARY]);

CREATE TABLE dbo.yourauntdebbie
(
    id INT,
    whatever VARCHAR(50)
) ON yourdad (id);

Not even adding any data.

DROP PARTITION FUNCTION yourmom;

DROP PARTITION SCHEME yourdad;

First Error:

Msg 7706, Level 16, State 1, Line 17 Partition function 'yourmom' is being used by one or more partition schemes.

Second Error:

Msg 7717, Level 16, State 1, Line 19 The partition scheme "yourdad" is currently being used to partition one or more tables.

Let's create an index and add some data so you don't think I'm crazy.

CREATE CLUSTERED INDEX ix_whatever ON dbo.yourauntdebbie (id) ON yourdad (id)

INSERT dbo.yourauntdebbie WITH (TABLOCK) (id, whatever )
SELECT TOP 1000 n, REPLICATE('A', n % 50)
FROM (SELECT ROW_NUMBER() OVER (ORDER BY @@ROWCOUNT) AS n
        FROM sys.messages AS m) AS x

Now, let's rebuild the clustered index away from the partitioning scheme.

CREATE CLUSTERED INDEX ix_whatever ON dbo.yourauntdebbie (id)  WITH (DROP_EXISTING = ON) ON [PRIMARY]

We can drop the scheme;

DROP PARTITION SCHEME yourdad;

Drop the function:

DROP PARTITION FUNCTION yourmom;

And still select the 1000 rows:

SELECT *
FROM dbo.yourauntdebbie AS y

And then clean up the table.

DROP TABLE dbo.yourauntdebbie

Answer 2

You cannot change that part of a table.

Instead you need to create your new scheme, function and a new copy of your table. Then you can insert all the rows from the old table, and finally drop the old table, scheme and function.

It means you need to have room to store two copies (and a bit) of your table in your database. Often this means your database doubles in size.