SQL Server – How to Alter Existing Partitioned Table?

partitioningsql serversql-server-2008-r2

I have a large table that is already partitioned by an identifier (CustomerId). I have created new customers, and want to add new partitions to the table, and want to confirm the correct way to do this. My predecessor set this up and is no longer available so I cannot confirm.

There are currently partitions for 60 distinct CustomerIds (let's say 1-60), and a filegroup and file for each. I have created new filegroups and files for the new customers (61-68) and need to partition the existing data already in this table. So, the data that's already in the table, with customerId 61-68 needs to be moved into separate partitions.

I tried to use the partition wizard, but couldn't find an option for what I wanted. As far as I can tell, I need to run something like:

ALTER PARTITION SCHEME PS_Details  NEXT USED Filegroup61
ALTER PARTITION FUNCTION PF_Details ()  SPLIT RANGE (61)

I'm looking for someone to point me in the right direction as to best practice for this sort of thing. Do I take the data out of the table, into a new table stored in the Filegroup61 then switch the partition in? Not really sure how to approach this.

Best Answer

Splitting a non-empty partition requires about 4x logging as normal DML. Assuming the table and indexes are aligned, you can avoid excessive logging with large tables by creating a partitioned staging table with the original boundaries. Then switch in the problem partition, split the empty partitions of the original function, repartition the staging table, and switch the remediated partitions back in. Below is an example script. You'll need to tweak this if you use a RANGE LEFT partition function and specify your actual filegroups.

--create staging partition function and scheme
--with same boundaries and filegroups as original
CREATE PARTITION FUNCTION PF_CustomerId_Staging (int)
    AS RANGE RIGHT FOR VALUES();
ALTER PARTITION FUNCTION PF_CustomerId_Staging()
    SPLIT RANGE(NULL);
DECLARE @CustomerId int = 0;
WHILE @CustomerId <= 60
BEGIN
    ALTER PARTITION FUNCTION PF_CustomerId_Staging()
        SPLIT RANGE(@CustomerId);
    SET @CustomerId += 1;
END;
CREATE PARTITION SCHEME PS_CustomerId_Staging
    AS PARTITION PF_CustomerId_Staging ALL TO ([PRIMARY]);
GO

--create staging table with same schema
CREATE TABLE dbo.CustomerData_Staging(
      CustomerId int NOT NULL
    , CustomerDataId int IDENTITY NOT NULL
    , CustomerData int NOT NULL
    , CONSTRAINT PK_CustomerData_Staging PRIMARY KEY CLUSTERED (CustomerId, CustomerDataId)
        ON PS_CustomerId_Staging(CustomerId)
    );
CREATE INDEX idx_CustomerData_Staging_CustomerDataId ON dbo.CustomerData_Staging(CustomerData) ON PS_CustomerId_Staging(CustomerId);
GO

--switch partition with multiple customers to staging table
ALTER TABLE dbo.CustomerData
SWITCH PARTITION $PARTITION.PF_CustomerId(60)
TO dbo.CustomerData_Staging PARTITION $PARTITION.PF_CustomerId_Staging(60);

--split original partition function (empty partitions)
DECLARE @CustomerId int = 61;
WHILE @CustomerId <= 68
BEGIN
    ALTER PARTITION SCHEME PS_CustomerId NEXT USED [PRIMARY];
    ALTER PARTITION FUNCTION PF_CustomerId()
        SPLIT RANGE(@CustomerId);
    SET @CustomerId += 1;
END;
GO

--repartition staging table and indexes with same function/scheme
CREATE UNIQUE CLUSTERED INDEX PK_CustomerData_Staging ON dbo.CustomerData_Staging(CustomerId, CustomerDataId)
    WITH (DROP_EXISTING=ON)
    ON PS_CustomerId(CustomerId);
CREATE INDEX idx_CustomerData_Staging_CustomerDataId ON dbo.CustomerData_Staging(CustomerData)
    WITH (DROP_EXISTING=ON)
    ON PS_CustomerId(CustomerId);

--switch remediated partitions back into main table
DECLARE @CustomerId int = 60;
WHILE @CustomerId <= 68
BEGIN
    ALTER TABLE dbo.CustomerData_Staging
        SWITCH PARTITION $PARTITION.PF_CustomerId(@CustomerId)
        TO dbo.CustomerData PARTITION $PARTITION.PF_CustomerId(@CustomerId);
    SET @CustomerId += 1;
END;
GO

--drop staging objects
DROP TABLE dbo.CustomerData_Staging;
DROP PARTITION SCHEME PS_CustomerId_Staging;
DROP PARTITION FUNCTION PF_CustomerId_Staging;
GO
Related Question