This is the second edition in a series of posts describing how to implement partition switching to load a very large Fact table. The first blog post gave some insight into what partition switching is and how it works. This post will focus on partitioning the Fact table and the problems encountered during the process.
Before you can do Partition Switching, the Fact table must be partitioned and the specific partition loaded with data must exist. In our project we partitioned the table per day. It was decided to add a new Partition dynamically to the Partition Scheme and Partition Function when the specific day’s data is ready for loading into the Fact table. (Brent Ozar has an extensive article with step by step code samples which you can refer to when building your own partitioning strategy)
This process worked perfectly up until a Clustered Column Store index was added to the Fact table. I received the following error when executing the partition split after adding the Clustered Column Store index.
Msg 35346, Level 15, State 1, Line 390
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 disabling the columnstore index before issuing the ALTER PARTITION statement, then rebuilding the columnstore index after ALTER PARTITION is complete.
I found a couple of workarounds on blog sites to fix this problem but in the end I decided to pre-generate the partitions in the Partition Function and Partition Scheme. When the partitions are pre-generated you don’t need to add a partition before each partition switch. The code below generates the script.
Select ‘Alter Partition Scheme [psFunction] Next Used FileGroupName’ + Convert(Varchar,MonthOfYear) + ‘;’,
‘Alter Partition Function [pfScheme]() Split Range (‘ + Convert(Varchar,DateID) + ‘) ;’
FROM DimDate Where DateID >= 20140101 And DateID <= 20251231
Order By DateID
Using the output of this script I generated partitions for the Fact table from 2014 up until the end of 2025 which solved the problem.
Big Data and Analytics 