CODEWITHSUNDEEP
sql-serverdatabase-administrationsql-server-2019
Mehrdad Alemi
Mehrdad Alemi

Reputation: 129

Altering/Editing an already partitioned table

What steps to take to add additional partitions to the end of an already partitioned table in SQL Server?

Conditions:

Also, How to verify the partitions and rows are correctly mapped?

Upvotes: 0

Views: 4666

Answers (1)

Ben Thul
Ben Thul

Reputation: 32727

Addressing your questions in turn:

What steps to take to add additional partitions to the end of an already partitioned table in SQL Server?

Partitioned tables are built on partition schemes which themselves are built on partition functions. Partition functions explicitly specify partition boundaries which implicitly define the partitions. To add a new partition to the table, you need to alter the partition function to add a new partition boundary. The syntax for that is alter partition function... split. For example, let's say that you have an existing partition function on a datetime data type that defines monthly partitions.

CREATE PARTITION FUNCTION PF_Monthly(datetime)  
   AS RANGE RIGHT FOR VALUES (
      '2022-10-01',
      '2022-11-01',
      '2022-12-01',
      '2023-01-01'
   );

Pausing there and talking about the last two partitions in the current setup. The next-to-last partition is defined as 2022-12-01 <= x < 2023-01-01 while the last partition is defined as 2023-01-01 <= x. Which is to say that the next-to-last partition is bounded for the month of December 2022, the last partition is unbounded on the high side and includes data for January 2023 but also anything larger.

If you want to bound the last partition to just January 2023, you'll add a partition boundary to the function for the high side of that partition. There's a small catch in that you'll also need to alter the partition scheme to tell SQL where to put data, but that's a small thing.

ALTER PARTITION SCHEME PS_Monthly
   NEXT USED someFileGroup;
ALTER PARTITION FUNCTION PF_Monthly() 
   SPLIT RANGE ('2023-02-01');

At this point, what used to be your highest partition is now defined as 2023-01-01 <= x < 2023-02-01 and the highest partition is defined as 2023-02-01 <= x. I should note that adding a boundary to a partition function will affect all tables that use it. When I was using table partitioning at a previous job, I had a rule to have only one table using a given partition function (even if they were logically equivalent).

No DB downtime is acceptable (<10min)

The above exposition doesn't mention one important point - if there is data in either side of the new boundary, a new B-tree is going to be built for it (which is a size-of-data operation). There's more on that in the documentation. To keep that at a minimum, I like to keep two empty partitions at the end of the scheme. Using my above example, that would mean that I'd have added the January partition boundary in November. By doing it this way, you have some leeway in when the actual partition split happens (i.e. if it's a bit late, you're not accidentally incurring data movement). I'd also put in monitoring that's something along the lines of "if the highest partition boundary is less than 45 days away, alert". A slightly more sophisticated but more correct alert would be "if there is data in the second to last partition, send an alert".

Also, How to verify the partitions and rows are correctly mapped?

You can query the DMVs for this. I like using the script in this blog post. There's also the $PARTITION() function if you want to see which partition specific rows in your table belong to.

Upvotes: 3

Related Questions