iT邦幫忙

DAY 26
5

SQL SERVER 2008效能監控與最佳化系列 第 26

Day26_Partition02建立Partition Table

介紹實作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轉移資料。


上一篇
Day25_Partition簡介01
下一篇
Day27_Partition03:PartitionTable搭配Switch轉移資料
系列文
SQL SERVER 2008效能監控與最佳化30

尚未有邦友留言

立即登入留言