SQL Server 2016表分区：新增空分区是否需重建表？

Great question—this is a common point of confusion when working with SQL Server partitioning, and you’re absolutely right: you do NOT need to rebuild the clustered index (or "rebuild the table") if there’s no data in the range corresponding to the new partition.

Let me break down why, and walk through the adjusted workflow:

Why Rebuilding Isn’t Necessary Here #

Rebuilding the clustered index exists to reallocate existing data into new or modified partitions. When you add a new partition for a range that currently has zero rows in the table, there’s nothing to move or reorganize. Once you update the partition function and scheme, SQL Server will automatically recognize the new range, and any future data inserted into that range will be routed to the new partition seamlessly—no extra work required.

Adjusted Step-by-Step Workflow (No Data in New Partition) #

You can safely skip the final "rebuild table" step. Here’s the streamlined process:

• Create the new file group (e.g., ALTER DATABASE YourDB ADD FILEGROUP FG_NewPartition;)
• Add a data file to the new file group (e.g., ALTER DATABASE YourDB ADD FILE (NAME = N'File_NewPartition', FILENAME = N'C:\Path\To\File.ndf') TO FILEGROUP FG_NewPartition;)
• Update the partition scheme to mark the new file group as next in line (e.g., ALTER PARTITION SCHEME PS_YourScheme NEXT USED FG_NewPartition;)
• Split the partition function to add the new range (e.g., ALTER PARTITION FUNCTION PF_YourFunction() SPLIT RANGE ('2025-01-01');)

That’s all you need to do—no rebuild required.

When You Would Need to Rebuild #

Just to add context for clarity: if the new partition range already contained data (say, you’re splitting an existing partition that has rows), then you’d need to rebuild the clustered index to move those rows into the new partition. But in your scenario—where the new range is completely empty—this step is redundant and wastes resources.

内容的提问来源于stack exchange，提问作者MariusD