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 NULL is specified as a boundary value and RIGHT is indicated. In this case, the left-most partition is an empty partition, and NULL values are placed in the following partition.

So basically NULLs are going to end up in the left most partition(#1) unless you specifically make a partition for NULL and are using a RIGHT partition. So let’s start with a quick example of where NULL values are going to end up in a partitioned table (a simple version).

Click through to see Kenneth’s proof and the repercussions of making that partitioning column nullable.

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

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 […]

Read More


May 2017
« Apr