[SQL] 利用精靈實作 Partition Table

利用 SSMS 或 T-SQL 把 Big Table 水平分割為多個 Partition，放置在不同的 Filegroup 內，讓 Big Table 更容易管理和維護。

建立 Partition Table 過程

1. 建立 Filegroup 和對應檔案（mdf、ndf），用來放置 Partition，也可以把全部的 Partition 放在一個 Primary Filegroup 內
2. 建立 Partition Funcation，指定 Partition Column 和 Boundary Value，將資料表、索引資料，對應至 Partition
3. 建立 Partition Schema，並將 Partition 對應至 Filegroup
4. 建立、修改資料表或索引

注意事項

1. SQL Server 2005 開始有 Partition Table 功能，但分割精靈是從 SQL Server 2008 才有，Enterprise、Developer 和 Evaluation 版本上才可以使用 Partition Table
2. Partiton Funcation 和 Partition Schema 只能用在單一 DB 中
3. Partition Funcation 和 User Define Function 是不一樣的物件
4. Partition Column 內如果包含 NULL 資料，指定 Range Left  情況下，資料會配置至最左邊的 Partition；Range Right 情況下，最左邊的 Partition 會保持空白，NULL 資料會配置在第二個 Partition

利用 AdventureWorks2012 Sales.SalesOrderHeader Table 來實作 Partition Table。

• AdventureWorks2012 => Sales.SalesOrderHeader => 儲存體 => 建立資料分割。

• 建立資料分割精靈歡迎畫面

• 選擇 Partition Column，會依據此欄位來切割 Partition，此欄位只能是單一欄位或是計算欄位（Computed Column）。

• 建立 Partition Function

• 建立 Partition Schema

• 選擇範圍是［左界限］還是［右界限］，並點選［設定界限 button］；［設定界限 button］只有在 Partition Column 資料型態為 date、datetime、smalldatetime、datetime2 或 datetimeoffset 才可以使用它

• SalesOrderHeader 的資料是從 2005 - 2008 年，因此設定開始日期和結束日期為 2005/1/1 和 2009/1/1 且是根據每年來切 partition

• 選擇 Partition 要放在哪個 Filegroup 內，再點選［估計儲存體］

• 點選［估計儲存體］後，［列數］、［需要空間］和［可用空間］的資訊就會出現，且會多出現一個 partition（綠色圈資料）。

• 綠色圈 Partition 沒有選取 Filegroup 的話，會出現下面錯誤訊息

• 綠色圈 Partition 設定好 Filegroup 後才能執行下一步

左界線（Range Left）和右介面（Range Right），主要功能是定義界限值（Boundary Value）是包含在哪一邊的 Partition，以本例來說，Boundary Value 為 2005/1/1、2006/1/1、2007/1/1、2008/1/1和 2009/1/1 這五個值，因為是 Range Right，所以 Partition 上的資料範圍為

1. {min - 2004/12/31 23:59:59.997}
2. {2005/1/1 00:00:00.000 - 2005/12/31 23:59:59.997}
3. {2006/1/1 00:00:00.000 - 2006/12/31 23:59:59.997}
4. {2007/1/1 00:00:00.000 - 2007/12/31 23:59:59.997}
5. {2008/1/1 00:00:00.000 - 2008/12/31 23:59:59.997}
6. {2009/1/1 00:00:00.000 - max}

共六個 Partition，這也是綠色圈 Partition 會被精靈自動產生的原因。

• 選擇上述設定的 Script 檔案要如何呈現，故意選擇［建立指令碼］，來看看 SQL Server 會如何執行建立 Partition

• 設定一覽表

• 產生 Partition Table Script

• 產生的 Script 會直接出現在 SSMS 內，語法如下，在 SSMS 內執行該語法就會建立 Partition Table

USE [AdventureWorks2012]
GO
BEGIN TRANSACTION
CREATE PARTITION FUNCTION [demoPF](datetime) AS RANGE RIGHT FOR VALUES (N'2005-01-01T00:00:00', N'2006-01-01T00:00:00', N'2007-01-01T00:00:00', N'2008-01-01T00:00:00', N'2009-01-01T00:00:00')

CREATE PARTITION SCHEME [demoPS] AS PARTITION [demoPF] TO ([FG2], [FG2], [FG1], [FG1], [PRIMARY], [PRIMARY])

ALTER TABLE [Sales].[SalesOrderDetail] DROP CONSTRAINT [FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID]

ALTER TABLE [Sales].[SalesOrderHeaderSalesReason] DROP CONSTRAINT [FK_SalesOrderHeaderSalesReason_SalesOrderHeader_SalesOrderID]

ALTER TABLE [Sales].[SalesOrderHeader] DROP CONSTRAINT [PK_SalesOrderHeader_SalesOrderID]

ALTER TABLE [Sales].[SalesOrderHeader] ADD  CONSTRAINT [PK_SalesOrderHeader_SalesOrderID] PRIMARY KEY NONCLUSTERED 
(
 [SalesOrderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE CLUSTERED INDEX [ClusteredIndex_on_demoPS_635068046487017900] ON [Sales].[SalesOrderHeader]
(
 [OrderDate]
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [demoPS]([OrderDate])

DROP INDEX [ClusteredIndex_on_demoPS_635068046487017900] ON [Sales].[SalesOrderHeader]

ALTER TABLE [Sales].[SalesOrderDetail]  WITH CHECK ADD  CONSTRAINT [FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID] FOREIGN KEY([SalesOrderID])
REFERENCES [Sales].[SalesOrderHeader] ([SalesOrderID])
ON DELETE CASCADE
ALTER TABLE [Sales].[SalesOrderDetail] CHECK CONSTRAINT [FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID]

ALTER TABLE [Sales].[SalesOrderHeaderSalesReason]  WITH CHECK ADD  CONSTRAINT [FK_SalesOrderHeaderSalesReason_SalesOrderHeader_SalesOrderID] FOREIGN KEY([SalesOrderID])
REFERENCES [Sales].[SalesOrderHeader] ([SalesOrderID])
ON DELETE CASCADE
ALTER TABLE [Sales].[SalesOrderHeaderSalesReason] CHECK CONSTRAINT [FK_SalesOrderHeaderSalesReason_SalesOrderHeader_SalesOrderID]

COMMIT TRANSACTION

• 利用 sys.partitions 來確定 Partition 是否有建立

SELECT 
  p.partition_number ,
  p.rows ,
  p.index_id
FROM sys.partitions AS p
  JOIN sys.tables AS t ON  p.object_id = t.object_id
WHERE p.partition_id IS NOT NULL
    AND t.name = 'SalesOrderHeader'

參考資料
• 建立資料分割資料表及索引 - 精靈步驟說明
• 資料分割資料表及索引 - 完整說明
• 資料分割資料表和索引上的查詢處理增強功能
• Partitioned Table and Index Strategies Using SQL Server 2008
• Data Warehouse Query Performance
• ALTER PARTITION FUNCTION、ALTER PARTITION SCHEME、ALTER TABLE - Switch 參數
• sys.partitions、sys.partition_schemes、sys.partition_functions、sys.partition_range_values
• 論壇問題討論
• [SQL SERVER][TSQL] 查詢 Partition Table 相關資訊
• Super SQL Server 討論