--本文演示了 SQL Server 2008 分區表實(shí)例:
--1. 創(chuàng )建測試數據庫 ；
--2. 創(chuàng )建分區函數;
--3. 創(chuàng )建分區架構;
--4. 創(chuàng )建分區表;
--5. 創(chuàng )建分區索引 ;
--6. 分區切換 ;
--7. 查詢(xún)哪些表使用了分區表；
-- 創(chuàng )建測試數據庫
USE master
GO
IF OBJECT_ID(N'PartitionDataBase') IS NOT NULL
DROP DATABASE PartitionDataBase
GO
CREATE DATABASE PartitionDataBase
ON PRIMARY
(
NAME = N'File_A_H',
FILENAME ='D:\Data\PartitionDataBase_AH.mdf'
),
FILEGROUP FileGroup_I_N
(
NAME = N'File_I_N',
FILENAME ='D:\Data\PartitionDataBase_IN.mdf'
),
FILEGROUP FileGroup_M_Z
(
NAME = N'File_M_Z',
FILENAME ='D:\Data\PartitionDataBase_MZ.mdf'
)
GO
-- 創(chuàng )建分區函數
USE PartitionDataBase;
GO
CREATE PARTITION FUNCTION StaffNameRangePFN(varchar(100))
AS
RANGE LEFT FOR VALUES ('H','M')
GO
-- 創(chuàng )建分區架構
CREATE PARTITION SCHEME StaffNamePScheme
AS
PARTITION StaffNameRangePFN
TO ([PRIMARY], FileGroup_I_N, FileGroup_M_Z)
GO
-- 創(chuàng )建分區表
CREATE TABLE [dbo].[Staff]
(
[StaffName] [varchar](100) NOT NULL
)
ON StaffNamePScheme ([StaffName])
GO
-- 插入測試數據1
INSERT INTO [dbo].[Staff]
SELECT FirstName FROM AdventureWorks.Person.Contact
-- 查看結果
SELECT
$partition.StaffNameRangePFN(StaffName) AS [Partition Number],
MIN(StaffName) AS [Min StaffName],
MAX(StaffName) AS [Max StaffName],
COUNT(StaffName) AS [Rows In Partition]
FROM dbo.staff AS o
GROUP BY $partition.StaffNameRangePFN(StaffName)
ORDER BY [Partition Number]
-- 插入測試數據2
INSERT INTO [dbo].[Staff]
SELECT AddressLine1 FROM AdventureWorks.Person.Address
-- 查看結果
SELECT
$partition.StaffNameRangePFN(StaffName) AS [Partition Number],
MIN(StaffName) AS [Min StaffName],
MAX(StaffName) AS [Max StaffName],
COUNT(StaffName) AS [Rows In Partition]
FROM dbo.staff AS o
GROUP BY $partition.StaffNameRangePFN(StaffName)
ORDER BY [Partition Number]
-- 從結果看出,已經(jīng)重新分區過(guò)了
-- 重新改變布局
Use master
GO
ALTER DATABASE PartitionDataBase ADD FILEGROUP FileGroup_0_9
GO
ALTER DATABASE PartitionDataBase
ADD FILE
(
NAME = N'File_0_9',FileName = 'D:\Data\PartitionDataBase.mdf'
)
TO FILEGROUP FileGroup_0_9
GO
Use PartitionDataBase
GO
ALTER PARTITION SCHEME StaffNamePScheme
NEXT USED FileGroup_0_9;
GO
ALTER PARTITION FUNCTION StaffNameRangePFN()
SPLIT RANGE ('A');
GO
-- 查看結果
SELECT
$partition.StaffNameRangePFN(StaffName) AS [Partition Number],
MIN(StaffName) AS [Min StaffName],
MAX(StaffName) AS [Max StaffName],
COUNT(StaffName) AS [Rows In Partition]
FROM dbo.staff AS o
GROUP BY $partition.StaffNameRangePFN(StaffName)
ORDER BY [Partition Number]
-- 創(chuàng )建聚集分區索引
CREATE CLUSTERED index IXC_Staff_StaffName on dbo.Staff ( StaffName )
go
--- 分區切換
-- 6.1切換分區表的一個(gè)分區到普通數據表中： Partition to Table
--首先建立普通數據表 dbo.StaffName_Num ，該用來(lái)存放表StaffName 數字STAFF的數據
-- 創(chuàng )建表
if OBJECT_ID('dbo.StaffName_Num') is not null
drop table dbo.StaffName_Num
go
create table dbo.StaffName_Num
(
StaffName varchar ( 100 ) not null
)
on [FileGroup_0_9]
go
--注意這里建表不能為空或primary,因為我們建的分區表不在primary文件組
--如果這樣會(huì )出現
--消息 4939，級別 16，狀態(tài) 1，第 1 行
--ALTER TABLE SWITCH 語(yǔ)句失敗。索引'PartitionDataDB.dbo.StaffName_Num.IXC_StaffName_Num_StaffName_Num' 在文件組'PRIMARY' 中，而 索引'PartitionDataDB.dbo.Staff.IXC_Staff_StaffName' 的分區 1 在文件組'FileGroup_0_9' 中。
create clustered index IXC_StaffName_Num_StaffName_Num on dbo.StaffName_Num ( StaffName )
go
--這里的索引也要建立一樣的,
--不然也會(huì )出現這樣的提示
--消息 4913，級別 16，狀態(tài) 1，第 1 行
--ALTER TABLE SWITCH 語(yǔ)句失敗。表 'PartitionDataDB.dbo.Staff' 具有聚集索引 'IXC_Staff_StaffName'，而表 'PartitionDataDB.dbo.StaffName_Num' 沒(méi)有聚集索引。
-- 開(kāi)始切換,將數字開(kāi)頭的StaffName切換到剛才建立的表
alter table dbo.Staff switch partition 1 to dbo.StaffName_Num
-- 查看StaffName_Num表結果
select top 1 * from dbo.StaffName_Num
-- 查看StaffName表結果
select * from dbo.Staff where StaffName like '100'+'%'
--滿(mǎn)足條件：
--1. 普通表必須建立在分區表切換分區所在的文件組上。
--2. 普通表的表結構跟分區表的一致；
--3. 普通表上的索引要跟分區表一致。
--4. 普通表必須是空表，不能有任何數據。
--5. 切換普通表數據到分區，除了滿(mǎn)足上面的 4 個(gè)條件外，
-- 還要加上一條：普通表必須加上和分區數據范圍一致的 check 約束條件。
-- 查看結果
--6.1 切換普通表數據到分區表的一個(gè)分區中： Table to Partition
--上面我們已經(jīng)把分區表 切換 了，現在我們再切換回來(lái)：
alter table dbo.StaffName_Num switch to dbo.Staff partition 1
--但是，此時(shí)有錯誤發(fā)生：
--消息 4982，級別 16，狀態(tài) 1，第 1 行
--ALTER TABLE SWITCH 語(yǔ)句失敗。源表 'PartitionDataDB.dbo.StaffName_Num' 的
--檢查約束所允許的值對于目標表 'PartitionDataDB.dbo.Staff' 中分區 1
--定義的范圍是不允許的。
--這就奇怪了，
--能把數據從分區切換進(jìn)來(lái)卻切換不出去。
--出錯信息中提示我們是普通表的 check constraint 跟
--分區表不一致。
--于是在普通表上建立 check constraint ：
alter table dbo.StaffName_Num add constraint CK_Orders1998_StaffName
check ( left(StaffName,1) in (1,2,3,4,5,6,7,8,9) )
--切換普通表數據到分區，除了滿(mǎn)足上面的 4 個(gè)條件外，
--還要加上一條：普通表必須加上和分區數據范圍一致的 check 約束條件。
--6.3 切換分區表的分區到另一分區表： Partition to Partition
--這里已無(wú)再建分區表了,,
--實(shí)際上，分區表分區切換并沒(méi)有真正去移動(dòng)數據，
--而是 SQL Server 在系統底層改變了表的元數據。
--因此分區表分區切換是高效、快速、靈活的。
--利用分區表的分區切換功能，
--我們可以快速加載數據到分區表。
--卸載分區數據到普通表，
--然后 truncate 普通表，
--以實(shí)現快速刪除分區表數據。
--快速歸檔不活躍數據到歷史表。
7.查看哪些表使用了分區表
SELECT OBJECT_NAME (p. object_id ) AS ObjectName,
i.name AS IndexName,
p.index_id AS IndexID,
ds.name AS PartitionScheme,
p.partition_number AS PartitionNumber,
fg.name AS FileGroupName,
prv_left.value AS LowerBoundaryValue,
prv_right.value AS UpperBoundaryValue,
CASE pf.boundary_value_on_right
WHEN 1 THEN ' RIGHT '
ELSE ' LEFT ' END AS Range,
p.rows AS Rows
FROM sys.partitions AS p
JOIN sys.indexes AS i
ON i. object_id = p. object_id
AND i.index_id = p.index_id
JOIN sys.data_spaces AS ds
ON ds.data_space_id = i.data_space_id
JOIN sys.partition_schemes AS ps
ON ps.data_space_id = ds.data_space_id
JOIN sys.partition_functions AS pf
ON pf.function_id = ps.function_id
JOIN sys.destination_data_spaces AS dds2
ON dds2.partition_scheme_id = ps.data_space_id
AND dds2.destination_id = p.partition_number
JOIN sys.filegroups AS fg
ON fg.data_space_id = dds2.data_space_id
LEFT JOIN sys.partition_range_values AS prv_left
ON ps.function_id = prv_left.function_id
AND prv_left.boundary_id = p.partition_number - 1
LEFT JOIN sys.partition_range_values AS prv_right
ON ps.function_id = prv_right.function_id
AND prv_right.boundary_id = p.partition_number
WHERE
OBJECTPROPERTY (p. object_id , ' ISMSShipped ' ) = 0
UNION ALL
-- non-partitioned table/indexes
SELECT
OBJECT_NAME (p. object_id ) AS ObjectName,
i.name AS IndexName,
p.index_id AS IndexID,
NULL AS PartitionScheme,
p.partition_number AS PartitionNumber,
fg.name AS FileGroupName,
NULL AS LowerBoundaryValue,
NULL AS UpperBoundaryValue,
NULL AS Boundary,
p.rows AS Rows
FROM sys.partitions AS p
JOIN sys.indexes AS i
ON i. object_id = p. object_id
AND i.index_id = p.index_id
JOIN sys.data_spaces AS ds
ON ds.data_space_id = i.data_space_id
JOIN sys.filegroups AS fg
ON fg.data_space_id = i.data_space_id
WHERE
OBJECTPROPERTY (p. object_id , ' ISMSShipped ' ) = 0
ORDER BY
ObjectName,
IndexID,
PartitionNumber;
------------------------------------------------------------------------------
--詳細的分區表與索引可以閱讀:
--http://msdn.microsoft.com/zh-cn/library/ms345146(SQL.90).aspx
--參考:
--http://www.cnblogs.com/goodspeed/archive/2005/11/07/270759.html
