Splitting partition and removing partition scheme

user_15574502 0
Viewed 61

I created a partition function and scheme for a table with 3million lines.

CREATE PARTITION FUNCTION PartFunc (int) AS RANGE RIGHT FOR VALUES (1, 100,300);
CREATE PARTITION SCHEME PartFuncScheme AS PARTITION PartFunc ALL TO PRIMARY;

later, in search for better perf, I splitted with:

ALTER PARTITION FUNCTION PartFunc () SPLIT RANGE (200);

The above query was accepted, but when I tried to split further :

ALTER PARTITION FUNCTION PartFunc () SPLIT RANGE (250);

I got the error msg:

The partition scheme '' does not have any next used filegroup. Partition scheme has not been changed.

Bewildered by the message, I then tried to unpartition by rebuiding the clustered index (col1) of the table, by directly erasing it :

CREATE CLUSTERED INDEX [col1]
    ON [dbo].[PartitionTable1]([col1])
    WITH (DROP_EXISTING = ON)
    ON [PRIMARY];

then within SMSS , Properties/Storage pane of the table displays that the it is not partitioned. However when I tried to drop the partition scheme, it was denied with a message saying the scheme is either non existent or being used on a table. Within SMSS, the partition scheme and the partition function are still displayed as icons, and their properties indicates that they have the table as dependencies.

What's wrong?

database-partitioning sql-server sql
1 Answers

Annoyingly, you have to alter the partition scheme to specify that PRIMARY is the next-used filegroup after the first split. eg

use tempdb

CREATE PARTITION FUNCTION PartFunc (int) AS RANGE RIGHT FOR VALUES (1, 100,300);
CREATE PARTITION SCHEME PartFuncScheme AS PARTITION PartFunc ALL TO ([PRIMARY]);

create table t(id int primary key identity, a int) on partfuncscheme(id)

insert into t(a) select 32 from sys.objects

ALTER PARTITION FUNCTION PartFunc () SPLIT RANGE (200);


ALTER PARTITION FUNCTION PartFunc () SPLIT RANGE (250);
--Msg 7710, Level 16, State 1, Line 12
--Warning: The partition scheme 'PartFuncScheme' does not have any next used filegroup. Partition scheme has not been changed.

go

alter partition scheme PartFuncScheme next used [PRIMARY]
ALTER PARTITION FUNCTION PartFunc () SPLIT RANGE (250);

message saying the scheme is either non existent or being used on a table.

There's probably a non-clustered index from your table still on the partition scheme (or some other table). You can check with a query like:

select object_name(i.object_id) table_name, i.name index_name, ds.name partition_scheme
from sys.indexes i
join sys.data_spaces ds
  on i.data_space_id = ds.data_space_id
where ds.type = 'PS'
user_72977000
Related