Splitting a partition function when SQL Server columnstore indexes get

in the way (for SQL Server)

In SQL Server, columnstore indexes prevent splitting partition functions when the partition contains data.

Rowstore indexes do.

This post will focus on handling columnstore indexes.

I’ll show you how to deal with columnstore indexes in turn.

The backstory

(You can skip this section if you’re only interested in the solution) Recently, I tried to split a partition function used by both

rowstore and columnstore tables.

With a grin on my face, I ran the ALTER PARTITION FUNCTION… and got this 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 table ‘DaysWithoutCoding’ 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.

What the he…?

I considered dropping and recreating the columnstore indexes (which would take a long time).

• Truncating the table partition wasn’t possible; I need to keep the data.
• Creating a new table and renaming the old one, while possible, would upset current users.

I didn’t like either of the above options.

What else could I do?

Just before choosing one option, I saw a shed of light in this post.

Let’s walk through that solution now.

Preparing the test environment

(Again, if you want the solution right away, please go to the next section.)

Here is a simple scenario illustrating the split issue with columnstore indexes.

⚠️

Important: You need to use SQL Server 2016 or later for the test scenario of this post. Earlier versions of SQL Server don’t

fully support columnstore indexes.

I’ll use SQL Server 2022 and the AdventureWorks2017 database for this demo.

Run this code in your SQL Server instance:

use AdventureWorks2017
go

create partition function myDateRangePF (datetime2(0))  
    as range right for values ('2022-10-01', '2022-11-01', '2022-12-01')  
go  

create partition scheme myRangePS  
    as partition myDateRangePF  
    all to ('SECONDARY')  
go  

drop table if exists dbo.DaysWithoutCoding  
create table dbo.DaysWithoutCoding
(
   JournalDate datetime2(0), 
   Thoughts nvarchar(256)
)  
on myRangePS (JournalDate)  


go  

insert into dbo.DaysWithoutCoding (JournalDate, Thoughts)  
values  
    ('2022-10-15', 'Good day'),  
    ('2022-10-20', 'Great day'),  
    ('2022-11-10', 'Bad day'),  
    ('2022-11-05', 'Nice day'),  
    ('2022-12-25', 'Fun day')  

go 

Use this query to get the list of tables’ partitions.

use AdventureWorks2017
go

SELECT SCHEMA_NAME(t.schema_id) AS SchemaName, t.name AS TableName, i.name AS IndexName,  
    p.partition_number AS PartitionNumber, f.name AS PartitionFunctionName, p.rows AS Rows, rv.value AS BoundaryValue,  
CASE WHEN ISNULL(rv.value, rv2.value) IS NULL THEN 'N/A'  
ELSE  
    CASE WHEN f.boundary_value_on_right = 0 AND rv2.value IS NULL THEN '>='  
        WHEN f.boundary_value_on_right = 0 THEN '>'  
        ELSE '>='  
    END + ' ' + ISNULL(CONVERT(varchar(64), rv2.value), 'Min Value') + ' ' +  
        CASE f.boundary_value_on_right WHEN 1 THEN 'and <'  
                ELSE 'and <=' END  
        + ' ' + ISNULL(CONVERT(varchar(64), rv.value), 'Max Value')  
END AS TextComparison  
FROM sys.tables AS t  
JOIN sys.indexes AS i  
    ON t.object_id = i.object_id  
JOIN sys.partitions AS p  
    ON i.object_id = p.object_id AND i.index_id = p.index_id  
JOIN  sys.partition_schemes AS s  
    ON i.data_space_id = s.data_space_id  
JOIN sys.partition_functions AS f  
    ON s.function_id = f.function_id  
LEFT JOIN sys.partition_range_values AS r  
    ON f.function_id = r.function_id and r.boundary_id = p.partition_number  
LEFT JOIN sys.partition_range_values AS rv  
    ON f.function_id = rv.function_id  
    AND p.partition_number = rv.boundary_id  
LEFT JOIN sys.partition_range_values AS rv2  
    ON f.function_id = rv2.function_id  
    AND p.partition_number - 1= rv2.boundary_id  
WHERE  
    t.name = 'DaysWithoutCoding'  
ORDER BY t.name, p.partition_number;

You should have the following:

The table above shows the boundaries of each partition and the number of rows in each.

Splitting the non-empty partition from the columnstore table

You have to perform these steps:

1. Create a partitioned stage table using the same partition function as the original table.
2. Create a columnstore index in the stage table.
3. Switch out the target partition from the original table to the stage table.
4. Drop the columnstore index from the stage table.
5. Split the partition function as needed.
6. Recreate the columnstore index in the stage table.
7. Switch the data back from the stage table to the original table.

Note for step 5: When splitting a partition function, you must specify the filegroup for the new partition.

Following the scenario of the last section, suppose you want to continue your journal for 2023.

Since our partition function, myDateRangePF doesn’t have partitions for the year 2023, let’s create partitions for the first three months of 2023 with these statements:

use AdventureWorks2017
go

ALTER PARTITION FUNCTION myDateRangePF ()  
SPLIT RANGE ('2023-01-01')

ALTER PARTITION FUNCTION myDateRangePF ()  
SPLIT RANGE ('2023-02-01')

ALTER PARTITION FUNCTION myDateRangePF ()  
SPLIT RANGE ('2023-03-01')

You should get this error:

Indeed, SQL Server doesn’t allow us to split a partition function when there is data in a partition of a table with a columnstore index 🫤.

Now, we are going to fix that with the following code:

use AdventureWorks2017
go

/* 1. Create a **partitioned** stage table using the **same partition function** as the original table. */  
create table dbo.DaysWithoutCoding_Stage (JournalDate datetime2(0), Thoughts nvarchar(256))  
    on myRangePS (JournalDate)  

/* 2. Create a **columnstore index** in the stage table. */  
create clustered columnstore index CI_DaysWithoutCoding_Stage on dbo.DaysWithoutCoding_Stage  
    ON myRangePS (JournalDate)  

/* 3. Switch out the target partition from the original table to the stage table. */  
alter table DaysWithoutCoding  
    switch partition 4 to DaysWithoutCoding_Stage partition 4  

/* 4. Drop the columnstore index from the stage table. */  
drop index DaysWithoutCoding_Stage.CI_DaysWithoutCoding_Stage  

/* 5. Split the partition function. */  
ALTER PARTITION FUNCTION myDateRangePF ()  
SPLIT RANGE ('2023-01-01')  

/* Specify the filegroup for the new partition */  
ALTER PARTITION SCHEME myRangePS  
NEXT USED 'SECONDARY'  
ALTER PARTITION FUNCTION myDateRangePF ()  
SPLIT RANGE ('2023-02-01')  

/* Specify the filegroup for the new partition */  
ALTER PARTITION SCHEME myRangePS  
NEXT USED 'SECONDARY'  
ALTER PARTITION FUNCTION myDateRangePF ()  
SPLIT RANGE ('2023-03-01')  

/* 6. Recreate the columnstore index in the stage table. */  
create clustered columnstore index CI_DaysWithoutCoding_Stage on dbo.DaysWithoutCoding_Stage  
    ON myRangePS (JournalDate)  

/* 7. Switch the data back from the stage table to the original table. */  
alter table DaysWithoutCoding_Stage  
    switch partition 4 to DaysWithoutCoding partition 4

The sixth step (Recreate the columnstore index) will take time. Apart from that, the other steps execute in seconds!. Please be patient.

Let’s check out the partitions again:

And there you go!

Three new partitions for January, February, and March 2023.

This way, you can overcome the limitation that SQL Server puts on splitting partition functions that are used in columnstore

indexes. 😌

Summary

• For rowstore indexes, splitting partition functions works in either empty or non-empty partitions.
• For columnstore indexes, splitting partition functions works only in empty partitions.
• For splitting partition functions used by columnstore indexes, except if you are OK with creating a new table or truncating that partition, you should use a stage table.

I drew this.