THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Geek City: ALTERing a Partition Function

Finally, a real geeky post. I received two questions in as many weeks about what really happens to existing partitions when you alter the underlying partition function. The big question being… does altering a partition function cause any data movement? The answer, as usual, is “It depends”

Most people who work with partitioning are aware that the SWITCH operation does not cause data movement. But SWITCH is an option to ALTER TABLE, not ALTER PARTITION FUNCTION. ALTER TABLE SWITCH … allows you to ‘switch’ the contents of a populated partition with an empty one, and if all the prerequisite conditions are not met, the SWITCH cannot proceed and you’ll get an error.

However, altering a partition function  is different. When you alter a partition function with the SPLIT option, you can end up with an empty partition, so many people believe that it’s just a simple operation.  But it’s not always.

The ALTER PARTITION FUNCTION statement is deceptively simple:

ALTER PARTITION FUNCTION partition_function_name()
    SPLIT RANGE ( boundary_value )
  | MERGE RANGE ( boundary_value ) 
} [ ; ]

You can either specify a new boundary value (with SPLIT) or an existing one (with MERGE). Let’s look at SPLIT.

Suppose I have a partition function with boundary points at 1, 10, 100, 1000.   Suppose I have a table created on a partition scheme that uses this function, and I have populated the table.  If I alter the function with SPLIT RANGE (50), the partition between 10 and 100 will be split into 2. If I already have data between 10 and 50 and other data between 50 and 100, some of the data will have to move.  But which data?

Let me create a simple example in the AdventureWorks database. The following statements will create a copy of the Production.Product table and partition it into 5 partitions, with the boundary points suggested above.

USE AdventureWorks;

  AS RANGE LEFT FOR VALUES ( 1.00,10.00,100.00,1000.00) ;

AS PARTITION price_parts
   ALL to ([PRIMARY]);

SELECT * INTO NewProduct
FROM Production.Product;

  ON NewProduct(ProductID)
  ON price_scheme (ListPrice);

You can use the $partition reference to see how many rows are in each partition:

  SELECT $partition.price_parts(ListPrice), COUNT(*)
  FROM dbo.NewProduct
  GROUP BY $partition.price_parts(ListPrice);

Or, you can use the sys.partitions catalog view:

SELECT * FROM sys.partitions
WHERE object_id = object_id('dbo.NewProduct');


The output shows me 5 partitions, and the number of rows in each.

Now lets see what happens when I split partition number 3, which contains the rows between 10 and 100.


Run the sys.partitions query again, and you’ll see 6 rows:


Partitions 3 and 4 together now have 77 rows, which were in the previous partition 3. But how can we tell which rows moved? You need to look at partition_id. Do not confuse partition_number with partition_id. Partition_number values are always consecutive so where we had partition_number values 1-5 before, we now have 1-6. What was partition 3 is now split into 3 and 4, what was partition 4 is now 5 and what was 5 is now 6.  Partition_id values are actually tied to the physical storage and do NOT change unless the data moves. So by comparing the two outputs above, you can see that the old partition 5, using partition_id 72057594058571776 is now partition 6, but it still uses partition_id 72057594058571776. For partitions 3 and 4, we can see that partition 4 uses the partition_id of the old partition 3, and the new partition 3 has an entirely new partition_id. It was the data between 10 and 50 that moved.


The answer is clearly stated in BOL, in the description of ALTER PARTITION FUNCTION:

Based on boundary_value, the Database Engine splits one of the existing ranges into two. Of these two, the one where the new boundary_value resides is considered the new partition.

This says that whatever partition contains the new value (50, in our case) is the one that moves! And how do we know which partition contains the boundary point? That depends on whether your partition function is defined using RANGE LEFT or RANGE RIGHT. In my case, the function was defined using RANGE LEFT, so any rows with a ListPrice equal to the boundary value go in the partition on the LEFT, that is, with values smaller that the boundary value. So the old partition 3 contained these values:

10 < ListPrice <= 100

The new partition 3 contains these values:

10 < ListPrice <= 50

And the new partition 4 contains these values:

50 < ListPrice <= 100

So what about when we add a new boundary point  at the end to create a new empty partition? The maximum ListPrice value is 3578.27, so I’ll add a new boundary point at 5000, after also altering the partition scheme to allow for another filegroup.

  SPLIT RANGE (5000);


Here is what the new partitions look like:


Note that even though partition 7 is empty, it uses the existing partition_id 72057594058571776. The data in partition 6 was moved to a new physical location with a new partition_id. For 86 rows, it doesn’t take all that long to move the rows, but what if partition 6 had 86 BILLION rows? You may think that adding a new empty partition is a very fast operation, but if your partition function definition requires the existing 86 billion rows to move, it could be a extremely slow operation.

So what happens when you use the MERGE option to ALTER PARTITION FUNCTION? As a former university instructor, it’s really great to be able to say… that’s your homework!

So have fun!


Published Sunday, August 16, 2009 2:31 PM by Kalen Delaney

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Tom Tait said:

What about partitioning based on two fields like geography and date?  Can this be done?

September 23, 2009 2:14 PM

Kalen Delaney said:

Tom -- Partitioning can only be defined on a single column.

September 23, 2009 8:13 PM

Tom Tait said:

Ouch. That is disappointing.  Maybe they'll add that in the next release.   Ok. Thanks.  

September 24, 2009 7:08 PM

David Lean said:

"What about partitioning based on two fields like geography and date?  Can this be done?"

"Tom -- Partitioning can only be defined on a single column."

Kalen's answer is totally accurate but leads to an incorrect conclusion / belief.

1. You CAN.

The Partitioning can be done on a single COMPUTED column, So long as it is persisted. Thus with a small amount of effort you can Partition on multiple columns.

2. You CAN'T

But in your example you mentioned using the geography datatype. There are datatype restrictions on partitioning columns: CLR based types (like Geography), Binary & Varchar(max) are amoung those columns that don't make good indexes & aren't permitted as a Partition column.

3. You CAN

If you were really keen you could use a Geography Function convert it to WKT or some integer value that represented a square or other area. This would then work.

4. You probably don't want to.

Geography has great Spatial Indexes, attempting to partition via a non-spatial approach would require some deep thought on how your use the data, anything less would probably suck. Perhaps Partition on Date & compliment with a (non)alighned Spatial index  

January 11, 2010 6:29 AM

David Lean said:

Oh! PS: Kalen

Long time no chat.

Great Article,

And I've enjoyed reading the 2008 Series of "Inside SQL 2008" books by You,  Itzik  & others.

January 11, 2010 6:33 AM

Tatyana said:

I wonder how ALTER... SPLIT is logged. I tried to split a partition on a very large table and wounded up having transaction log filling up the whole disk. Now I put the database in a simple recovery mode and am trying to do the same... and I see, in the Windows resource Monitor, the transaction log gets hit big, again. I wonder, why? What's actually getting logged?

October 27, 2011 1:57 PM

Kalen Delaney said:

Hi Tatyana

If you SPLIT such that data moves, each row moved is logged. The removal from the old partition is logged as a DELETE, the addition to the new partition is logged as an INSERT.

As per my other blog posts, these operations are not logged any differently in SIMPLE recovery. SIMPLE does not affect the logging of DELETE and INSERT operations.

This is why it is recommended that you plan your SPLITs carefully, and try to only split at the beginning or end of the table so that no data is moved.



October 27, 2011 2:49 PM

Tatyana said:

Thank you, Kalen, you are very correct :) Right now I just added another log file, temporarily, on another drive; but in the future I'm going to follow simple and smart recommendations from your article! Thank you very much!


October 27, 2011 3:18 PM

GoodStuff said:

This is very helpful


December 29, 2011 12:18 PM

Roberto Reyes said:

If in the example, there was an extra empty partition (not the default partition) on the rightmost of the range, splitting that would have not caused any data movement (because there was no data in that range to begin with). This is the reason why some advice having an extra empty partition on the right of the range. Splitting between boundaries will, of course, cause data movement if there were data on the left and right of the new boundary.

Thanks this article is helpful.

May 31, 2012 10:19 AM

Kalen Delaney said:

Hi Roberto

Yes, thanks for the data point. This was not actually supposed to be a complete description of everything you need to know and do when working with partitions; that would be more than a simple blog post. My purpose was to show you how to use the metadata to tell what had happened when you split or merge.



May 31, 2012 1:46 PM

Alex said:

Hi Kalen,

Your article is very helpful.

I have done a lot of maintenance on partitioned tables in the past. Recently, I have encountered the following error for the first time:

Msg 7707, Level 16, State 1, Line 1

The associated partition function '<partition_function_name>' generates more partitions than there are file groups mentioned in the scheme '<partition_scheme_name>'.

I am trying to add partitions to the right of the last partition (it is a date based partition function and the last 8 partitions on the right are still unoccupied - so there should be no data movement involved). I issue an alter partition scheme command setting next used and then try to issue the alter partition function split range command.

Any ideas?

June 23, 2012 7:27 PM

Alex said:

I was able to resolve the issue successfully. The problem was that the underlying partition function was being shared by multiple partition schemes. Once I moved the additional partition schemes off of the partition function onto new ones, I was able to run the command successfully.

July 3, 2012 4:36 PM

Tiago said:

Although I've tried the split on a non empty partition(several times) with a range higher than the existent one but I never had data movement( the partition_number never changed and a new partition was created).

I cannot reproduce what you've done.

I really want to see a data movement splitting   a non-empty partition when the range is higher than the existing upper bound.

PS: Code used

June 17, 2013 12:32 PM

Tiago said:

Hi Kalen.

I was able to reproduce the data movement with the RANGE LEFT instead of RANGE RIGHT..

There is any basic explanation for this?

Thanks for your post. It gave me a great help understanding better the partitioning split and merge.



June 17, 2013 1:11 PM

al said:

Kalen, is there a way to record/monitor the amount of writes happening during a split of non-empty partition? The explanation of how data movement is related to the RANGE LEFT/RIGHT was great, thanks!  

August 23, 2013 9:02 PM

Todd said:

Hi Karen,

I want to set up table partitioning that has only two partitions: a "live" partition containing data for the current year; and an "archive" partition that holds all other historical data.  Each year I want to move the previous year's data from the "live" partition to the "archive" partition.  What is the simplest way to do this?  Can I simply modify the partition function with a new date?

Thanks for your time, knowledge and help!

May 7, 2014 10:44 AM

Kalen Delaney said:

Hi Todd

(Note: my name is Kalen, not Karen.)

Although it's unusual to have a sliding window with just two partitions, it's not unheard of. And yes, you can modify the partition function with a new date so that the first partition now includes all the previous live data, but that will cause data movement, which will cause lots of I/O and logging, so just be aware.

I suggest you read the whitepaper on partitioning, which goes through a full example.

May 7, 2014 2:36 PM

Todd said:


I'm so sorry for using the wrong name!

I've read numerous articles and each always uses multiple partitions.  If I have only one "archive" partition/filegroup, is there any way to avoid the I/O and logging when the current year changes and I want to move that data from the "current" partition/filegroup to the archive partition/filegroup?

Thanks for the information.


May 7, 2014 3:58 PM

Kalen Delaney said:

I am understanding that you have an archive partition and an active partition, and periodically you want to move the active into the archive. You are then combining data from two partitions into one, and there is no way to do that without actually moving data.

May 7, 2014 7:13 PM

Ashu said:


May 8, 2014 6:04 AM

Bert said:


I have a database with four partitioned (range right) tables sharing the same partition function.  I am making sure the right-most partition of each table contains no data, but still the split (creation of a new partition) takes a very long time (1-2 hours) during which the tables are locked.

I create a new file group containing one new database file that I set as next used before the split.

Any idea what could be the problem?



October 22, 2014 11:07 AM

Bert said:

It turns out the problem described in my previous message was not the splitting. It was the update of the check constraint on the partitioned tables that was done after the split. ADD CONSTRAINT did not have the NOCHECK option resulting in all existing rows being checked.

October 24, 2014 2:26 PM

Kalen Delaney said:

Thanks for following up on this Bert... I was going to suggest taking a look at the lock information while you were waiting, to see what kind of lock was being held, and what the blocked command actually was!

I'm curious though why you had to add a constraint to the partitioned table, since the boundary points act as a implicit constraint.



October 24, 2014 7:46 PM

Raymond P. said:

Hi Karen,

If I had mapped the partitions to get serviced by individual files within a filegroup, I am assuming that the SPLIT at the end of the table utilizes the same file that mapped to the last partition before the SPLIT as well. Is there a way to assign the new empty partition to an empty (added) file in the filegroup?


January 1, 2015 12:40 PM

Kalen Delaney said:

Hi Raymond

I'm not completely sure what you're asking, because you cannot map objects or partitions to a particular file, only to a filegroup.

When you perform an ALTER PARTITION FUNCTION to split and add a new partition boundary, you have to already have defined the filegroup where the next partition will go. You do this with the ALTER PARTITION SCHEME command:

ALTER PARTITION SCHEME partition_scheme_name

NEXT USED [ filegroup_name ] [ ; ]


Kalen (not Karen)

January 2, 2015 2:50 PM

Raymond P. said:

Thanks Kalen (sorry about that)! You are absolutely right. I did maintain a 1:1 relationship between the filegroup and file and totally asked the question incorrectly. But your answer was exactly what I was looking for.


January 4, 2015 2:50 AM

Marco HG said:

Hi Kallen is there a way to monitor the progress of a partition split with data movement? How can i calculate the space used for the transaction log and tempdb.


November 3, 2016 11:05 AM

saurabh said:

Very good artical to understand the spliting partition

November 6, 2016 12:13 PM

Prem said:

I have requirement in partitioning, In my environment daily we scheduled job to create partitioning which is daily partitioning for two tables.. So far 145 partition created I have a requirement like customer need only latest 90 days partition which means latest 90 days data. I need Scripts to do this step by step method( It could be very useful if we schedule job in weekly basis.). Could you please help on this. Thank you.

SQL version : 2016 Enterprise edition

August 2, 2017 5:19 AM

king said:



January 16, 2018 11:25 PM

Ethan said:

Great article.

One question, I have a large table where the partitions haven't been maintained, meaning the top partition has got the bulk of the data. I want to split this partition (its based on a date column), if I do





 SPLIT RANGE (20170199);


etc.. for each month

What I need to understand is, will SQL server start moving the data around in the top partition automatically, as soon as the next partition is created?

March 16, 2018 12:58 AM

dongdong8 said:



June 29, 2018 3:36 AM

linying123 said:


July 16, 2018 9:27 PM

dongdong8 said:



July 23, 2018 11:44 PM

obat diabetes said:

August 1, 2018 11:26 PM

obat bronkitis anak secara alami said:

August 7, 2018 7:54 PM

obat sinusitis said:

August 10, 2018 7:27 PM

chenjinyan said:


August 22, 2018 11:17 PM

obat usus buntu alami tanpa operasi said:

August 24, 2018 12:28 AM

obat benjolan di tubuh said:

August 30, 2018 7:44 PM

obat wasir said:

September 4, 2018 8:31 PM

obat katarak said:

September 16, 2018 5:50 PM

obat maag said:

September 17, 2018 7:15 PM

xiaojun said:

20180928 junda

September 27, 2018 10:58 PM

xiaozhengzheng said:

November 19, 2018 8:34 PM

yanmaneee said:">">

June 29, 2020 11:17 PM

Leave a Comment


This Blog


Favorite Non-technical Sites or Blogs

Privacy Statement