更改現有分區表

我有一個已按標識符 (CustomerId) 分區的大表。我已經創建了新客戶，並且想要向表中添加新分區，並且想要確認執行此操作的正確方法。我的前任設置了這個並且不再可用，所以我無法確認。

目前有 60 個不同的 CustomerIds（比如說 1-60）的分區，每個分區都有一個文件組和文件。我已經為新客戶 (61-68) 創建了新的文件組和文件，並且需要對該表中已有的現有數據進行分區。因此，表中已有的數據（customerId 為 61-68）需要移動到單獨的分區中。

我嘗試使用分區嚮導，但找不到我想要的選項。據我所知，我需要執行類似：

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

我正在尋找有人為我指出正確的方向，以實現此類事情的最佳實踐。我是否將數據從表中取出，放入儲存在 Filegroup61 中的新表中，然後切換分區？不太確定如何解決這個問題。

拆分非空分區需要大約 4 倍於正常 DML 的日誌記錄。假設表和索引是對齊的，您可以通過創建具有原始邊界的分區臨時表來避免對大型表進行過多的日誌記錄。然後切換入問題分區，拆分原函式的空分區，重新分區暫存表，將修復的分區切換回。下面是一個範例腳本。如果您使用RANGE LEFT分區函式並指定您的實際文件組，則需要對此進行調整。

--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

引用自：https://dba.stackexchange.com/questions/146786