Split a Partition
Published: Sep 24, 2017
Cleary this is a direct follow-up to my last post in which I showed how to merge a partition… this is the exact opposite in which we’ll split a partition into 2.

This is just as simplistic as the Merge and with pretty much the same syntax… however with SPLIT there’s a slight caveat that’s worth knowing and therefore I kept it in a separate post.

We’ll use the same demo as for Merge, at the exact point that the post ended (therefore after the Merge command has been executed) and we’ll split the partitions up again so that we effectively put things back as they were before I started to tamper.

This is all you need to do:

alter partition function testPartitionFunction()
  
split range ('2010-12-01')
go

select *
from sys.partitions
where object_id = object_id('partitionSOHE')
go


So where’s the caveat, I hear you cry (or maybe it’s just the voices in my head, but we’ll gloss over that for the moment :o) )…

Well it’s in the output of the sys.partitions query:



If you look closely you’ll see that the native ordering has changed and the “new” partition we’ve created has been placed at the end of the output.

This seems innocuous but is actually very important…

Okay, okay, I KNOW that there’s no “set” order to results, but the above actually worked as it’s showing the order by partition_id so I’m going with it…

In the background, when you create a new partition, SQL Server doesn’t actually split the data up, it creates a new partition at the END of the table and physically moves the required data into it. Therefore the above screenshot is actually correct… the data is in a new partition at the end of the table.

So why does this matter? You’ll never see it as far as query results are concerned?

Well, it matters for one reason… filegroups.

We’re actually using a simplistic example in which all partitions go to PRIMARY, but if you weren’t then you could find some odd behavior.
For example, let’s say that your monthly partitions are all on PRIMARY but your older data is on a filegroup called Archive… In the above example, SQL Server will place the next partition created into the PRIMARY filegroup… so it would have created our December “old” partition in the PRIMARY filegroup and not Archive where we wanted it (if you’re following so far).

So what we would actually have needed to do is the following:

alter partition scheme testPartitionScheme
   next used [Archive]
go

alter partition function testPartitionFunction()
  
split range ('2010-12-01')
go

alter partition scheme testPartitionScheme
   next used [Primary]
go

select *
from sys.partitions
where object_id = object_id('partitionSOHE')
go


This will ensure that your SPLIT ends up where you want it, and that your subsequent new partition (likely to be created for new data) will be back in Primary once more.

Just something to note so that you don’t fall into the trap of spreading data randomly across incorrect filegroups.

Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron