介紹實作Partition Table。
在Day25_Partition簡介01有介紹Partition與其優缺點，這篇主要是介紹如何建立Partition Table。
建立Partition Table的步驟：
 建立PARTITION FUNCTION
 建立PARTITION SCHEME
 建立Table並套用PARTITION SCHEME
 新增資料並檢視資料分布

1.建立PARTITION FUNCTION

--1.建立PARTITION FUNCTION
USE Performance
GO
CREATE PARTITION FUNCTION ChineseYearPartitions (int)
AS RANGE RIGHT FOR VALUES ( 9901, 10001)
GO

ChineseYearPartitions會有三個PARTITION，如表一：

(表一：ChineseYearPartitions的PARTITION範圍)

2.建立Partitioning Scheme

--2.建立Partitioning Scheme
CREATE PARTITION SCHEME ChineseYearScheme
AS PARTITION ChineseYearPartitions
TO ([PRIMARY], [PRIMARY], [PRIMARY] )
GO

3.建立Table時套用PARTITION SCHEME:ChineseYearScheme

--3.建立Table時套用PARTITION SCHEME:ChineseYearScheme
CREATE TABLE ArchivedTable
(ChineseYM INT PRIMARY KEY,
tName varchar(50))
ON ChineseYearScheme (ChineseYM);
GO

4.新增資料

--4.新增資料，此時SQL SERVER會依照PARTITION FUNCTION ChineseYearPartitions配置資料到所屬的PARTITION分區
INSERT INTO ArchivedTable (ChineseYM, tName)
SELECT 9811,'tName1' UNION ALL
SELECT 9812,'tName2' UNION ALL
SELECT 9902,'tName1' UNION ALL
SELECT 9903,'tName3' UNION ALL
SELECT 9907,'tName2' UNION ALL
SELECT 10003,'tName1' UNION ALL
SELECT 10010,'tName3'
GO

5.檢查Partition的內容

--5.檢查Partition的內容
SELECT $PARTITION.ChineseYearPartitions(ChineseYM) AS [Partition Number], COUNT(*) AS total
FROM ArchivedTable
GROUP BY $PARTITION.ChineseYearPartitions(ChineseYM)
ORDER BY $PARTITION.ChineseYearPartitions(ChineseYM)
COMPUTE SUM (COUNT(*))

執行結果：

下一篇我將介紹Partition Table如何搭配Switch轉移資料。