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.

up vote 2 down vote accepted

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
  • Thanks. This should do the trick. Although m, after discussing this with the IT manager, I've decided to split this into separate tables with a partitioned view instead as partitioning is the final feature we use that relies on Enterprise edition. Removing the dependency means we can move to standard :) – TaylorN Aug 16 '16 at 19:31
  • @TaylorN, beginning with SQL Server 2016 SP1, table partitioning is in the Standard Edition SKU. – Dan Guzman Aug 14 '17 at 14:58

For the future, best practices for partitioned tables suggests that you need to maintain empty partitions on both left and right sides of the table to allow you to switch data in instead of splitting partitions that already contain data.

From this link, here is a quote from @GailShaw (Microsoft Certified Master) on this very topic

http://www.sqlservercentral.com/Forums/Topic1646905-391-1.aspx

Unfortunately, if you are splitting the partition and existing data belongs in the new partition, you're just going to have to accept that it will take time and you'll need to ensure that there's enough log space for SQL to move the data.
Alternately, you could create a new table with the partitions as you want them and copy the data over. It'll also take time, but you can do it in batches to reduce the log impact. Either way, the data will have to move, there's no way to create partitions and not have the data that qualifies for the new partition be automatically moved.

Gail Shaw Microsoft Certified Master: SQL Server

  • Thanks Scott. There is a partition on both left and right. There's one on PRIMARY that has a NULL value in sys.partition_range_values, and another that has 0. I assume that everything < 0 and > 60 (my highest range value) would be included in those two ranges? – TaylorN Aug 15 '16 at 9:34
  • 1
    @TaylorN, you already have existing data for the new boundaries so the empty partition consideration is not relevant. Splitting a non-empty partition requires about 4x logging as normal DML. You might be able to use SPLIT if your table is not large but should otherwise use a staging table. One method is to switch only the 60 partition into a staging table, partition the staging table with the new boundaries, add new boundaries to the original function, and finally SWITCH the staging table partitions back in. This technique assumes aligned indexes. – Dan Guzman Aug 15 '16 at 12:23
  • @DanGuzman, thanks for that. I was thinking that I may have to do something like that, as there is about 80mil rows in the last partition. I may have to switch that out, split it up then switch them back in. Do you have any suggestions on how to do this correctly? i.e. 1. Create staging table 2. Copy data and create indexes 3. Split data in staging table 4. Add new ranges to existing function 5. Switch partitions out of staging table back into original table ? – TaylorN Aug 15 '16 at 19:53
  • 1
    @TaylorN, too long for a comment so I posted an answer with a sample script. – Dan Guzman Aug 16 '16 at 12:48

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.