This post follows on from the previous post Partitioning Basics – Part 1

Let’s have a look at the partitions setup in part 1, the following script will show the partition information:-

SELECT 
t.name AS TableName, i.name AS IndexName, p.partition_number, 
r.value AS BoundaryValue, p.rows
FROM 
sys.tables AS t
INNER JOIN
sys.indexes AS i ON t.object_id = i.object_id
INNER JOIN
sys.partitions AS p ON i.object_id = p.object_id AND i.index_id = p.index_id 
INNER JOIN
sys.partition_schemes AS s ON i.data_space_id = s.data_space_id
INNER JOIN
sys.partition_functions AS f ON s.function_id = f.function_id
LEFT OUTER JOIN 
sys.partition_range_values AS r ON f.function_id = r.function_id AND r.boundary_id = p.partition_number
WHERE 
t.name = 'DemoPartitionedTable'
AND 
i.type <= 1
ORDER BY p.partition_number;

These partitions will hold data until the 11th of June, after which all records in the table will be put into the end partition (number 16). This means that the end partition will end up holding a lot of records. In order to prevent this, a new partition needs to be created.

To do this, firstly the partition scheme needs to be told which filegroup the next partition will use:-

ALTER PARTITION SCHEME DemoPartitionScheme
NEXT USED [Demo];
GO

Then a SPLIT command is run against the partition function:-

ALTER PARTITION FUNCTION DemoPartitionFunction()
SPLIT RANGE ('2014-06-12');
GO

And now when the data checking script is run, the results are:-

Partition 16 now has a boundary value of 2014-06-12 and a new partition (17) has been created.

But how can a partition be removed? There’s a really simple MERGE command that performs this operation:-

ALTER PARTITION FUNCTION DemoPartitionFunction()
MERGE RANGE ('2014-05-28');
GO

And when the data checking script is run now, the results are:-

So the data in the partition with the boundary value 2014-05-28 has been merged into the partition with the boundary of 2014-05-29. The problem now is that the last partition has data from the partition that was just merged into it as well as the existing data that was already there. In order to prevent this from happening, the data from the partition that is about to be merged can be switched into another table before the MERGE command is executed.

I will cover this in the next part of this blog series.