史丹利好熱

 

首先要確認資料庫的版本，SQL Server 2005/2008/2012/2014/2016 且 Enterprise Edition。

1.在資料庫建立Partition Table用到的檔案群組及檔案，這個假設一個檔案群組用一個檔案。

alter database [TSQL2014] add filegroup [PartFG01];
alter database [TSQL2014] add filegroup [PartFG02];
alter database [TSQL2014] add filegroup [PartFG03];
alter database [TSQL2014] add filegroup [PartFG04];
alter database [TSQL2014] add filegroup [PartFG05];
alter database [TSQL2014] add filegroup [PartFG06];
alter database [TSQL2014] add filegroup [PartFG07];
alter database [TSQL2014] add filegroup [PartFG08];
alter database [TSQL2014] add filegroup [PartFG09];
alter database [TSQL2014] add filegroup [PartFG10];
alter database [TSQL2014] add filegroup [PartFG11];
alter database [TSQL2014] add filegroup [PartFG12];
			
alter database [TSQL2014] add file ( name=[PartFGFile01],filename='T:\SQL\PartFGFile01.ndf',size=10MB) TO FileGroup[PartFG01]
alter database [TSQL2014] add file ( name=[PartFGFile02],filename='T:\SQL\PartFGFile02.ndf',size=10MB) TO FileGroup[PartFG02]
alter database [TSQL2014] add file ( name=[PartFGFile03],filename='T:\SQL\PartFGFile03.ndf',size=10MB) TO FileGroup[PartFG03]
alter database [TSQL2014] add file ( name=[PartFGFile04],filename='T:\SQL\PartFGFile04.ndf',size=10MB) TO FileGroup[PartFG04]
alter database [TSQL2014] add file ( name=[PartFGFile05],filename='T:\SQL\PartFGFile05.ndf',size=10MB) TO FileGroup[PartFG05]
alter database [TSQL2014] add file ( name=[PartFGFile06],filename='T:\SQL\PartFGFile06.ndf',size=10MB) TO FileGroup[PartFG06]
alter database [TSQL2014] add file ( name=[PartFGFile07],filename='T:\SQL\PartFGFile07.ndf',size=10MB) TO FileGroup[PartFG07]
alter database [TSQL2014] add file ( name=[PartFGFile08],filename='T:\SQL\PartFGFile08.ndf',size=10MB) TO FileGroup[PartFG08]
alter database [TSQL2014] add file ( name=[PartFGFile09],filename='T:\SQL\PartFGFile09.ndf',size=10MB) TO FileGroup[PartFG09]
alter database [TSQL2014] add file ( name=[PartFGFile10],filename='T:\SQL\PartFGFile10.ndf',size=10MB) TO FileGroup[PartFG10]
alter database [TSQL2014] add file ( name=[PartFGFile11],filename='T:\SQL\PartFGFile11.ndf',size=10MB) TO FileGroup[PartFG11]
alter database [TSQL2014] add file ( name=[PartFGFile12],filename='T:\SQL\PartFGFile12.ndf',size=10MB) TO FileGroup[PartFG12]

2.建立Partition Function (假設希望每個月放1個分割):

CREATE PARTITION FUNCTION PFMonth (date)
AS RANGE RIGHT FOR VALUES ('2012-02-01','2012-03-01','2012-04-01','2012-05-01','2012-06-01','2012-07-01',
                           '2012-08-01','2012-09-01','2012-10-01','2012-11-01','2012-12-01','2013-01-01');

分割編號	1	2	3	4	5	6	7	8	9	10	11	12	13
交易資料(月)	01	02	03	04	05	06	07	08	09	10	11	12	...

RANGE RIGHT表示邊界值歸哪一個的鄰近分割(預設是左邊)，以第一個邊界2012-02-01來看，因為設定右邊，所以2012-02-01歸分割2而不歸分割1。

< 2012-02-01 : 分割1

>= 2012-02-01  and  <2012-03-01 : 分割2 

..

3.建立Partiton Schema (12個Range需要13個群組，可以重複輪迴)

CREATE PARTITION SCHEME PSchemaMonth
as partition PFMonth
TO ([PartFG01],[PartFG02],[PartFG03],[PartFG04],[PartFG05],[PartFG06],
    [PartFG07],[PartFG08],[PartFG09],[PartFG10],[PartFG11],[PartFG12],[PartFG01]);

4.建立測試資料表(這邊取名TestPartitionTable)，注意在最後一行: On PschemaMonth(C1) 表示檔案群組(File Group)依Partiton Schema並輸入C1欄位來決定。

CREATE TABLE TestPartitionTable(
    [C1] Date NOT NULL,
    [C2] varchar(8) NOT NULL
 CONSTRAINT [PK_TestPartitionTable] PRIMARY KEY CLUSTERED 
(
    [C1] ASC,
	[C2]
)
) ON PSchemaMonth(C1)

5.寫入每天1筆的測試資料

DECLARE @I   INT = 0;
DECLARE @CNT INT = 365;
DECLARE @DATE DATE = '2012-01-01';
WHILE (@I <= @CNT)
BEGIN
  INSERT INTO TestPartitionTable VALUES(DATEADD(DAY,@I,@DATE),'') 
  SET @I = @I + 1 ;
END 

6.查詢Partition使用狀況 (參考五餅二魚工作室)

DECLARE @tablename VARCHAR(20) = 'TestPartitionTable';
SELECT
	OBJECT_NAME(p.object_id) AS TableName,
	p.partition_number AS PartitionNumber,
	prv_left.value AS LowerBoundary,
	prv_right.value AS UpperBoundary,
	ps.name AS PartitionScheme,
	pf.name AS PartitionFunction,
	fg.name AS FileGroupName,
	CAST(p.used_page_count * 8.0 / 1024 AS NUMERIC(18, 2)) AS UsedPages_MB,
	p.row_count AS Rows
FROM sys.dm_db_partition_stats p
INNER JOIN sys.indexes i
	ON i.object_id = p.object_id
	AND i.index_id = p.index_id
INNER JOIN sys.partition_schemes ps
	ON ps.data_space_id = i.data_space_id
INNER JOIN sys.partition_functions pf
	ON ps.function_id = pf.function_id
INNER JOIN sys.destination_data_spaces dds
	ON dds.partition_scheme_id = ps.data_space_id
	AND dds.destination_id = p.partition_number
INNER JOIN sys.filegroups fg
	ON fg.data_space_id = dds.data_space_id
LEFT JOIN sys.partition_range_values prv_right
	ON prv_right.function_id = ps.function_id
	AND prv_right.boundary_id = p.partition_number
LEFT JOIN sys.partition_range_values prv_left
	ON prv_left.function_id = ps.function_id
	AND prv_left.boundary_id = p.partition_number - 1
WHERE p.object_id = OBJECT_ID(@tablename)
AND p.index_id < 2

1天1筆的交易資料完成了！準備好Partiton Table 測試環境了，接下來回到正題，資料封存!

 

假設今天要清除2012-01的交易資料，也就是把Partition Number = 1的31筆資料清理。

(這邊不用DML Delete 刪除，因為會擴大鎖定，分批刪除，又怕執行時間太久)

1.第一步要準備空的資料表結構(這邊取名ArchivePartitionTable)，這邊要注意，結構必須與原資料表相同(包含PK、甚至是constraint)。

  還有一個就是空的資料表建立時檔案群組必須設定在與預備清除指定分割的群組相同(1月份是PartFG01)

DROP TABLE ArchivePartitionTable
CREATE TABLE ArchivePartitionTable(
    [C1] DATE  NOT NULL,
    [C2] varchar(8) NOT NULL
 CONSTRAINT [PK_ArchivePartitionTable] PRIMARY KEY CLUSTERED 
(
	[C1],[C2]
)
) ON [PartFG01]

2.執行SWITCH PARTITION，將資料分割1切到封存資料表中。順道觀察執行前後原大型資料表在資料筆數上的增減。

DECLARE @CNT INT = 0;
SELECT  @CNT = COUNT(*) FROM TestPartitionTable;
PRINT @CNT

ALTER TABLE TestPartitionTable SWITCH PARTITION 1 TO ArchivePartitionTable ;

SELECT @CNT = COUNT(*) FROM TestPartitionTable;
PRINT @CNT

刪除了1月份31筆，剛剛嘗試測更大的資料量，100萬筆也是一瞬間，感覺是一種邏輯定義轉移，就像Truncate。

 

3.觀察封存資料表 ArchivePartitionTable (資料也轉過來了)

SELECT * FROM ArchivePartitionTable

4.如果此時使用者頑皮的新增1筆 2012-01-01的資料，資料還是會回到分割1中，這邊需要視商業上的規則來調適。

INSERT INTO TestPartitionTable VALUES ('2012-01-01','')

小結:

• 如果有採Partiton Table的資料表改用Switch Partition方式雖然需要多準備一個空的結構，但，很快。
• 如果是用"月"來分檔案群組，檔案群組編序與分割編號很重要，都用相同檔案群組也是一種選擇。
• 大量匯入是不是也可以改用這種作法?(下次來試看看)

 

補充: 簡易查看Partiton內資料筆數的語法:

SELECT
	PARTITION = $PARTITION.PFMonth(C1),
	rows = COUNT(*),
	minval = MIN(C1),
	maxval = MAX(C1)
FROM TestPartitionTable
GROUP BY $PARTITION.PFMonth(C1)
ORDER BY PARTITION

 

 

參考:

使用資料分割切換有效傳送資料

[SQL]Partition Table 的實作筆記(五餅二魚工作室)

Alter Partition Function

ALTER PARTITION SCHEME

[SQL SERVER][Memo]打造動態Partition Table(RiCo技術農場)