2011年11月4日 星期五

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
(表一:ChineseYearPartitionsPARTITION範圍)

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

沒有留言:

張貼留言