Altering a partition function

Question

Altering a partition function

stricknyn

SSCertifiable

Points: 6560
More actions
July 28, 2023 at 6:48 pm

#4247126

Hello everyone,
Was looking for some feedback before I make a DB object change. So, I have a partition function that basically needs to be changed to use a completely different set of dates. Just the partition function needs to change. The scheme, table, indexes, etc. all stay the same.
Is doing this as simple as just running an alter partition statement with the changed dates? Or does something else need to be done?
Thanks for any assistance provided!
Strick

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jonathan AC Roberts SSCoach Points: 18347 More actions · Answer 1

Modifying a partition function in SQL Server is not quite as simple as altering a partition statement with the changed dates. Once a partition function is created and associated with a partition scheme, and if the partition scheme is being used by tables or indexes, you cannot directly modify the partition function. You would need to create a new partition function with the new dates, and then switch the data to the new partition.

Here are some steps to illustrate the process. Please be aware that these are general steps and they might need to be adapted to your specific scenario:

Create the new partition function: Define the new partition function with the different set of dates.

CREATE PARTITION FUNCTION new_partition_function (date)
AS RANGE RIGHT FOR VALUES ('2023-01-01', '2023-02-01', '2023-03-01', '2023-04-01', '2023-05-01');

Create a new partition scheme: Create a new partition scheme that uses the new partition function.

CREATE PARTITION SCHEME new_partition_scheme
AS PARTITION new_partition_function
ALL TO ([PRIMARY]);

Create a new table: You need to create a new table identical to the original one but using the new partition scheme.

CREATE TABLE new_table_name ( ... )
ON new_partition_scheme (date_column);

Switch partitions: You would need to switch out each partition from the original table to the new one. This is done by using the ALTER TABLE...SWITCH command. This command has to be repeated for each partition.

ALTER TABLE original_table
SWITCH PARTITION 1
TO new_table_name PARTITION 1;

Recreate the indexes: Once the data is switched to the new table, you will need to recreate any indexes that were on the original table. Make sure they are created on the correct partition.

Drop the original table: Once all data is switched and the indexes are recreated, the original table can be dropped.

Rename the new table: Finally, you can rename the new table to the original table's name.

Update any views or procedures: If there are any views or stored procedures referencing the old table, they will need to be updated to reference the new table.

Note: This is a high-risk operation, especially if the table is large or if it's a production environment. It's strongly recommended to backup your data before starting this operation. You should also thoroughly test this process in a development or staging environment before applying it to production.

frederico_fonseca SSCoach Points: 16154 More actions · Answer 2

if the range dates of the partitions (old and new tables) are not a match a switch is not possible.

if the partition column is the same date and the intention is just to change the range of each partition you may also be able to use a combination of range merge and range split to get the desired result. use with care as this means data movement (and tlog) and can be slow.

alternative is to create a new table with the desired partition ranges and then insert from the source table onto the new table - a (new) range at the time

This reply was modified 1 year, 3 months ago by frederico_fonseca.

stricknyn SSCertifiable Points: 6560 More actions · Answer 3

Thanks everyone for your responses. It sounds like (based on a few of your responses), I'll need to:

recreate everything from scratch;
1. aAnew partition scheme
2. New partition function
3. New table all based new partition scheme
4. Then etl the data from the old table to the new table
5. Then create the indexes based on the new table
6. then drop the old table
7. Rename to table to the old one.

This excludes the switch stuff since according to frederico, a switch is not possible since the partitions are completely different. Let me know if anything sounds off.

Thanks for your help!

Strick

frederico_fonseca SSCoach Points: 16154 More actions · Answer 4

Looks ok

things to consider if table is big (100's of GB) - ETL data based on partitions and once some source partitions are fully copied to new table truncate the source partition to save space on database.

if table is small in size (100GB or less) using partition merge/split may be easier to implement as long as the partition column is the same.