Partitioning 5: The extra partition

So, we’ve set up a table with table partitioning on the RIGHT for CreationDate. We defined 11 partitions, and in Partitioning 4, we saw 11 partitions. However, if we look in sys partitions, there’s actually 12.

SELECT 
p.partition_number,
p.rows
FROM sys.partitions AS p
JOIN sys.tables AS t ON t.object_id = p.object_id
WHERE t.name = 'Posts_Partitioned'
AND p.index_id = 1;

What does this mystery partition contain?

Well, in Partition 2, I queried for the values in the 3rd partition. That was from the start to end of 2009. Let’s take a quick look at the partition function I created.

CREATE PARTITION FUNCTION Posts_Partition_Function (DATETIME)  
    AS RANGE RIGHT FOR VALUES ('2008-01-01',
	'2009-01-01',
	'2010-01-01',
	'2011-01-01',
	'2012-01-01',
	'2013-01-01',
	'2014-01-01',
	'2015-01-01',
	'2016-01-01',
	'2017-01-01',
	'2018-01-01') ; --This is the 11th partition  
GO  

Let’s change some data and see what sys partitions shows for row count.

SELECT CreationDate
FROM Posts_Partitioned
WHERE Id IN (1,2);
GO

UPDATE Posts_Partitioned
SET CreationDate = '2001-04-01'
WHERE Id = 1;

UPDATE Posts_Partitioned
SET CreationDate = '2020-05-01'
WHERE Id = 2;
GO

SELECT CreationDate
FROM Posts_Partitioned
WHERE Id IN (1,2);
GO

And here’s the results:

Great! Now back to the sys partitions query from above.

Now there’s data in both partition 1 and 12. Let’s query by partition function and see what data is inside those partitions.

SELECT CreationDate,
$PARTITION.Posts_Partition_Function(CreationDate) AS PartitionNumber
FROM Posts_Partitioned AS p
WHERE $PARTITION.Posts_Partition_Function(CreationDate) = 1;
GO  

SELECT CreationDate,
$PARTITION.Posts_Partition_Function(CreationDate) AS PartitionNumber
FROM Posts_Partitioned AS p
WHERE $PARTITION.Posts_Partition_Function(CreationDate) = 12;
GO  

What happened to the extra partition?

Well, data that falls outside the range of the last partition on the right side will go into the last partition for my range right partitioning function. This is handy in case bad data is input into your system or your partition maintenance jobs haven’t created enough partitions.

Hopefully this throws up warning signs about either the source of the bad data or the lack of extra partitions.

Stay tuned!