sql server table partition function
sql server table partition function

How To Split Table Partitions Automatically

| 1 Comment

Table partitions can bring a lot of benefits to the manageability and performance of your SQL Server – that is if the partitions are configured and designed correctly. If you’ve got a large table with hundreds of millions of records, and you need to maintain a portion of that table only and not necessarily the whole table, table partitioning allows you to perform that maintenance on a partition-by-partition basis. And, also, when you enable lock escalation on the partition level, many of your locking and blocking woes might be fixed. If you have issues of locking/blocking on a big table in an application that performs high concurrency, table partitioning might help. Brent Ozar Unlimited has some good resources on SQL Server Table Partitioning.

What I want to focus on this post is the splitting of partition. It is very important to note that splitting a populated partition would cause the log file to bloat and, in some cases, the TempDB to run out of space. But you might have to deal with this if you’re adding partitioning in an existing table.

If you are still in the designing phase of your application development and you might be thinking of implementing table partitioning in the backend, then adding a task that would add partitions dynamically as your table grows should be on top of your to-do list. Creating the partitions in advance and staying few steps ahead of your data can save you from a lot of stress and headaches.

Here’s an example of how easy it is to implement a task that will automatically create partitions.

Test Table Partitions

Let’s create a test table. For this demo, we’re going to use a single filegroup (Primary) for convenience purposes.

At this point, without any records inserted yet to the table, the sys.partitions Object Catalog View would show something like this:
sys.partitions None Partitioned Table You can pull this metadata with this query:

Partition Function and Scheme

Let’s create the Partition Function and Scheme. Again, for convenience purposes, let’s create the Scheme on a single filegroup, which is the Primary filegroup in this case. Let’s supposed we want to partition our data into month buckets. Since this a date, I’m using Left Range which means the partition uses the upper boundary and includes all the points to its left. If you use Right Range instead, then you have to have a logic to handle the 31st day of some of the months. Just to illustrate that:

LowerBoundaryValue UpperBoundaryValue
NULL 1/1/2017
1/1/2017 2/1/2017

Upon successful creation of the Partition Function and Scheme, you’ll get a message something like this:

Add Indexes

Let’s also create indexes for demo purposes.

Populate Test Table

Although I said that you may want to avoid splitting populated partitions, for the purpose of this blog post, let’s create a single partition, which we already did above, then populate the table with data and add another partition on the populated table.

I am using Redgate SQL Data Generator to populate the test table with these settings on our Partition Column (SalesDate):

Redgate SQL Data Generator


You may remember that we created a partition (1/1/2017). Now with the 1,000 records added, our Table Partition looks like this:

populated table partition

Create Additional Partition

So, let’s create 4 additional partitions:

Insert Additional Records

Let’s insert few new records under April.

So far, we have 5 partitions for each month (January, February, March, April, and May). So we can have a better picture of our table partitions with the additional partitions and data, let’s query the underlying objects:

Which will give us this (click on the picture to enlarge it):

Table Partitions sql server

Dynamic Table Partition Split

So, let’s pretend it’s the month of April 2017 and this is the partition currently populated. Based on the query above, aside from the current partition bucket, we also have another available bucket month for May.

Say we want to maintain 3 available buckets at any given time. The next available bucket is May, so that means we need 2 more partitions to cover for June and July.

To do that, let’s do something like this:

Now we can see the additional partitions created:

Table Partitions sql server additional table partitions

Run the same code without changing the number of buckets to maintain (3) and you will get this message:

To make this truly automated, create a job that will run this code on monthly basis.


Also published on Medium.