SQL SERVER 分區表的總結–分區表的維護和管理

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