Partition02建立PartitionTable

介紹實作Partition Table。

在Partition01簡介有介紹Partition與其優缺點，這篇主要是介紹如何建立Partition Table。

建立Partition Table的步驟：

l   建立PARTITION FUNCTION

l   建立PARTITION SCHEME

l   建立Table並套用PARTITION SCHEME

l   新增資料並檢視資料分布

--1.建立PARTITION FUNCTION

USE Performance

GO

CREATE PARTITION FUNCTION ChineseYearPartitions (int)

AS RANGE RIGHT FOR VALUES ( 9901, 10001)

GO

ChineseYearPartitions會有三個PARTITION，如表一：

Partition1<9901

9901<= Partition2< 10001

Partition3>=10001

(表一：ChineseYearPartitions的PARTITION範圍)

--2.建立Partitioning Scheme

CREATE PARTITION SCHEME ChineseYearScheme

AS PARTITION ChineseYearPartitions

TO ([PRIMARY], [PRIMARY], [PRIMARY] )

GO

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

CREATE TABLE ArchivedTable

(ChineseYM INT PRIMARY KEY,

tName varchar(50))

ON ChineseYearScheme (ChineseYM);

GO

--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的內容

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轉移資料。