-4

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?

1
  • the data is the clustered index, assuming you have one on that table. whatever filegroup you set it to be, or several filegroups when partitioning, there is exactly where the data is. Sep 15 '17 at 21:13
29

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
3
  • I meant to say what happens if I point the new partition to the table... eventually the clustered index will point to new partitions Sep 15 '17 at 21:37
  • 1
    @GowthamRamamoorthy then why would the old partition function or scheme make a difference? Sep 15 '17 at 21:37
  • thanks for your comment... I thought there might be a data loss if there are new partition scheme used in the table because to use the new partition scheme the only way is by creating a clustered index pointing to the new partition scheme on that table... (provided you drop the existing indexes pointing to the old partition scheme).... I tested this process and came to know there won't be any data loss if new partitions are used in the table... Sep 15 '17 at 21:52
3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.