|
SQL SERVER 分區表的總結--分區表的維護和管理
在依據需求建立好分區表之后，就要實現對分區表的管理維護。主要內容就是兩點：
1. 利用滑動窗口方案(Sliding Window Scenario)，實現分區表與數據移動中間表的互切。
2. 分區表本身的結構變更管理。
當然以上兩點，都是理論上的點。我工作中主要是遇到兩種需求：
1. 過時分區數據的快速歸檔管理：即把某些不活躍分區的數據切到數據歸檔的表中去。
2. 分區表由于數據增長需要添加新分區來處理數據：即已分區的表它所有的數據都會處于活動狀態，需要新的分區來承載新增的數據。
這兩種需求將會在下面的代碼體現出來。分區表沿用我上一篇中的表Product,但是這里稱為Products。因為上一篇中的表，測試系統在用，我不能移動數據，就只好克隆出一張表。
創建一張表結構和主鍵聚集索引跟Products一模一樣的表Products_Tmp(用來做數據移轉的中間臨時表);這張表還必須跟要移出的分區位于同一個文件組(否則將不能應用Partition Switch進行快速切換)。
首先在新文件組上創建存檔表Products_Archive，用于存檔后面中間臨時表的數據。
USE [master]
GO
ALTER DATABASE [TEST]
ADD FILEGROUP [FG_TEST_Products_Archive]
GO
ALTER DATABASE [TEST]
ADD FILE ( NAME = N'FG_TEST_Products_Archive_data_1',
FILENAME = N'D:\Data\FG_TEST_Products_Archive_data_1.ndf' ,
SIZE = 50MB ,
FILEGROWTH = 10% )
TO FILEGROUP [FG_TEST_Products_Archive]
GO
USE TEST
GO
CREATE TABLE [dbo].[Products_Archive](
[ID] [int] NOT NULL,
[PName] [nvarchar](100) NULL,
[AddDate] [datetime2](3) NULL
)ON [FG_TEST_Products_Archive];
GO
接下來，就是移轉數據的操作了。封裝成存儲過程，方便調用。本來是想把導數據也寫在里面，可是考慮到生產環境數據量較大，會采用其它的導數據方案，就舍棄了。
 CREATE PROCEDURE usp_TransferPartitionData_ForArchive
@PartitonNumber INT --要移轉分區編號
AS
DECLARE @filegroup NVARCHAR(100)=N'', --要移轉分區所在文件組
@SQL NVARCHAR(4000)=N'', --創建中間臨時表的動態語句
@rangeValue INT; --要移轉分區邊界值上限
SET @rangeValue=(SELECT CAST(VALUE AS INT) FROM sys.partition_range_values WHERE boundary_id=@rangeValue);
--這里的賦值,使用我上篇中的自定義函數fn_GetFileForPartition.
SET @filegroup=(SELECT [FILEGROUP_NAME] FROM dbo.fn_GetFileForPartition(N'Sch_Product_ID',@rangeValue));
SET @SQL=N'CREATE TABLE [dbo].[Products_Tmp](
[ID] [int] NOT NULL,
[PName] [nvarchar](100) NULL,
[AddDate] [datetime2](3) NULL,
CONSTRAINT [PK_Products_Tmp] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)ON '+QUOTENAME(@filegroup);
IF OBJECT_ID('dbo.Products_Tmp') IS NOT NULL
RAISERROR('已經存在數據庫對象Products_Tmp!',16,1);
ELSE
BEGIN
--PRINT @SQL;
EXEC(@SQL);
--把指定分區中的數據切到Products_Tmp
ALTER TABLE dbo.Products
SWITCH PARTITION @PartitonNumber
TO dbo.Products_Tmp;
END
--因為數據量少又是堆表，直接使用INSERT INTO導入數據。
--如果是大數據量的話可能就會使用BULK+BCP或者SSIS來做。
--INSERT INTO [dbo].[Products_Archive]
--([ID],[PName],[AddDate])
-- SELECT [ID],[PName],[AddDate] FROM [dbo].[Products_Tmp];
--DROP TABLE [dbo].[Products_Tmp
GO
--移轉數據，并刪除Products_Tmp。
EXEC usp_TransferPartitionData_ForArchive 1;--指定移轉第一個分區
--因為數據量少又是堆表，直接使用INSERT INTO導入數據。
--如果是大數據量的話可能就會使用BULK+BCP或者SSIS來做。
INSERT INTO [dbo].[Products_Archive]
([ID],[PName],[AddDate])
SELECT [ID],[PName],[AddDate] FROM [dbo].[Products_Tmp];
DROP TABLE [dbo].[Products_Tmp];
現在舊數據移出去了，但是每天還有新數據進來，最后一個分區越來越大，所以我們要加入新文件組來承接這個分區。但是這里，就不新建文件組，而是使用在上面移轉數據時”空”出來的PRIMARY文件組。
首先分區既然空了，就要從分區表中移除它。然后再將其做為新文件組加進分區表。其實下面的代碼也可以整合到上面的SP里去的。
DECLARE @rangeValue INT, --要刪除分區邊界值的上限
@splitValue INT,--要分割給新分區用的邊界值下限
@filegroup NVARCHAR(100)=N'', --要合并的分區所屬的文件組
@SQL NVARCHAR(4000);--動態語句
--合并空分區，也就是刪除分區。
SET @rangeValue=80000;
ALTER PARTITION FUNCTION fn_Partition_Product_ID()
MERGE RANGE (@rangeValue);
--修改架構，添加文件組
SET @filegroup=(SELECT [FILEGROUP_NAME] FROM dbo.fn_GetFileForPartition(N'Sch_Product_ID',80000));
SET @SQL=N'ALTER PARTITION SCHEME Sch_Product_ID
NEXT USED '+QUOTENAME(@filegroup)+N';';
--PRINT @SQL;
EXEC(@SQL);
--增加新分區，來接受新數據
SET @splitValue=380000;
ALTER PARTITION FUNCTION fn_Partition_Product_ID()
SPLIT RANGE (@splitValue);
|
