|
SQL SERVER 分區(qū)表的總結(jié)--分區(qū)表的維護和管理
在依據(jù)需求建立好分區(qū)表之后，就要實現(xiàn)對分區(qū)表的管理維護。主要內(nèi)容就是兩點：
1. 利用滑動窗口方案(Sliding Window Scenario)，實現(xiàn)分區(qū)表與數(shù)據(jù)移動中間表的互切。
2. 分區(qū)表本身的結(jié)構(gòu)變更管理。
當然以上兩點，都是理論上的點。我工作中主要是遇到兩種需求：
1. 過時分區(qū)數(shù)據(jù)的快速歸檔管理：即把某些不活躍分區(qū)的數(shù)據(jù)切到數(shù)據(jù)歸檔的表中去。
2. 分區(qū)表由于數(shù)據(jù)增長需要添加新分區(qū)來處理數(shù)據(jù)：即已分區(qū)的表它所有的數(shù)據(jù)都會處于活動狀態(tài)，需要新的分區(qū)來承載新增的數(shù)據(jù)。
這兩種需求將會在下面的代碼體現(xiàn)出來。分區(qū)表沿用我上一篇中的表Product,但是這里稱為Products。因為上一篇中的表，測試系統(tǒng)在用，我不能移動數(shù)據(jù)，就只好克隆出一張表。
創(chuàng)建一張表結(jié)構(gòu)和主鍵聚集索引跟Products一模一樣的表Products_Tmp(用來做數(shù)據(jù)移轉(zhuǎn)的中間臨時表);這張表還必須跟要移出的分區(qū)位于同一個文件組(否則將不能應用Partition Switch進行快速切換)。
首先在新文件組上創(chuàng)建存檔表Products_Archive，用于存檔后面中間臨時表的數(shù)據(jù)。
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
接下來，就是移轉(zhuǎn)數(shù)據(jù)的操作了。封裝成存儲過程，方便調(diào)用。本來是想把導數(shù)據(jù)也寫在里面，可是考慮到生產(chǎn)環(huán)境數(shù)據(jù)量較大，會采用其它的導數(shù)據(jù)方案，就舍棄了。
 CREATE PROCEDURE usp_TransferPartitionData_ForArchive
@PartitonNumber INT --要移轉(zhuǎn)分區(qū)編號
AS
DECLARE @filegroup NVARCHAR(100)=N'', --要移轉(zhuǎn)分區(qū)所在文件組
@SQL NVARCHAR(4000)=N'', --創(chuàng)建中間臨時表的動態(tài)語句
@rangeValue INT; --要移轉(zhuǎn)分區(qū)邊界值上限
SET @rangeValue=(SELECT CAST(VALUE AS INT) FROM sys.partition_range_values WHERE boundary_id=@rangeValue);
--這里的賦值,使用我上篇中的自定義函數(shù)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('已經(jīng)存在數(shù)據(jù)庫對象Products_Tmp!',16,1);
ELSE
BEGIN
--PRINT @SQL;
EXEC(@SQL);
--把指定分區(qū)中的數(shù)據(jù)切到Products_Tmp
ALTER TABLE dbo.Products
SWITCH PARTITION @PartitonNumber
TO dbo.Products_Tmp;
END
--因為數(shù)據(jù)量少又是堆表，直接使用INSERT INTO導入數(shù)據(jù)。
--如果是大數(shù)據(jù)量的話可能就會使用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
--移轉(zhuǎn)數(shù)據(jù)，并刪除Products_Tmp。
EXEC usp_TransferPartitionData_ForArchive 1;--指定移轉(zhuǎn)第一個分區(qū)
--因為數(shù)據(jù)量少又是堆表，直接使用INSERT INTO導入數(shù)據(jù)。
--如果是大數(shù)據(jù)量的話可能就會使用BULK+BCP或者SSIS來做。
INSERT INTO [dbo].[Products_Archive]
([ID],[PName],[AddDate])
SELECT [ID],[PName],[AddDate] FROM [dbo].[Products_Tmp];
DROP TABLE [dbo].[Products_Tmp];
現(xiàn)在舊數(shù)據(jù)移出去了，但是每天還有新數(shù)據(jù)進來，最后一個分區(qū)越來越大，所以我們要加入新文件組來承接這個分區(qū)。但是這里，就不新建文件組，而是使用在上面移轉(zhuǎn)數(shù)據(jù)時”空”出來的PRIMARY文件組。
首先分區(qū)既然空了，就要從分區(qū)表中移除它。然后再將其做為新文件組加進分區(qū)表。其實下面的代碼也可以整合到上面的SP里去的。
DECLARE @rangeValue INT, --要刪除分區(qū)邊界值的上限
@splitValue INT,--要分割給新分區(qū)用的邊界值下限
@filegroup NVARCHAR(100)=N'', --要合并的分區(qū)所屬的文件組
@SQL NVARCHAR(4000);--動態(tài)語句
--合并空分區(qū)，也就是刪除分區(qū)。
SET @rangeValue=80000;
ALTER PARTITION FUNCTION fn_Partition_Product_ID()
MERGE RANGE (@rangeValue);
--修改架構(gòu)，添加文件組
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);
--增加新分區(qū)，來接受新數(shù)據(jù)
SET @splitValue=380000;
ALTER PARTITION FUNCTION fn_Partition_Product_ID()
SPLIT RANGE (@splitValue);
|
