Partitioned Columnstore Tables

Denny Cherry makes an important point about dealing with columnstore tables:

ColumnStore indexes are all the rage with data warehouses. They’re fast, they’re new(ish) and they solve all sorts of problems when dealing with massive amounts of data.  However they can cause some issues as well if you aren’t very careful about how you setup your partitions on the ColumnStore index.  This is because, you can’t split a ColumnStore partition once it contains data.

Now, if everything is going according to plan you create your partitions well in advance and there’s no issues.

However, if everything hasn’t gone according to plan and someone forgets to create the partitions and you end up with rows in the final partition, you can’t create any more partitions because you can’t split the partition.

Ideally, you get those ducks in a row first.  Keep reading for a repro script and a couple potential workarounds.

Related Posts

Finding Partition Boundaries

Kenneth Fisher shows how to find the min and max values for a partition: So what does it do? Per BOL Returns the partition number into which a set of partitioning column values would be mapped for any specified partition function in SQL Server 2016. So it basically tells us which partition any given row is in. This […]

Read More

Partitioning Nullable Columns

Kenneth Fisher looks at what happens when you use a nullable column as a partition key: So to start with how does partitioning handle a NULL? If you look in the BOL for the CREATE PARTITION FUNCTION you’ll see the following: Any rows whose partitioning column has null values are placed in the left-most partition unless […]

Read More

Categories

April 2017
MTWTFSS
« Mar May »
 12
3456789
10111213141516
17181920212223
24252627282930