[SOLVED] How do I add a new SQL Server partition range to house future data without altering the existing data locations?

How do I add a new SQL Server partition range to house future data without altering the existing data locations?

Several years ago I partitioned a collection of very large columnstore indexed tables. For simplicity, let's say I have four partition files where data is stored based on ranges of customer ID.

CREATE PARTITION FUNCTION [CustomerPF](int) AS RANGE LEFT FOR VALUES (
    N'25'
    ,N'50'
    ,N'75')

CREATE PARTITION SCHEME [CustomerPS] AS PARTITION [CustomerPF] TO (
    customer0to25fg
    ,customer26to50fg
    ,customer51to75fg
    ,customer76plusfg

CREATE CLUSTERED COLUMNSTORE INDEX [CCI_GiantTable] ON [schema].[GiantTable] ON [CustomerPS]([CustomerId])

Now let's say I would like to create space, in advance, for customers 100 and above. I tried to do it like this:

ALTER PARTITION SCHEME CustomerPS NEXT USED customer100plusfg
ALTER PARTITION FUNCTION [CustomerPF]() SPLIT RANGE (100)

But I get the following error:

SPLIT clause of ALTER PARTITION statement failed because the partition is not empty. Only empty partitions can be split in when a columnstore index exists on the table. Consider an ALTER TABLE SWITCH operation from one of the nonempty partitions on the source table to a temporary staging table and then re-attempt the ALTER PARTITION SPLIT operation. Once completed, use ALTER TABLE SWITCH to move the staging table partition back to the original source table.

I was hoping that, because I do not yet have a customerID of 100 or greater that I would be allowed to add a new partition without having to alter the columnstore tables themselves. How do I add a new partition for customers 100-n? Is it possible to do this without moving all the data for "customer75plus"? There are a lot of large columnstore tables in these partitions and moving data around is not all that feasible.

Solution

The new partition you create with SPLIT contains the split point. So you are trying to create a new partition (75-100]. But the partition (75-MaxVal] contains data.

Instead use a RANGE RIGHT partition scheme, so when you split the new partition is on the end so you can split anywhere above the current max value.

With RANGE LEFT you would have to always maintain an empty partition on the end to split.