本文共 6917 字,大约阅读时间需要 23 分钟。
本文演示了 SQL Server 2008 分区表实例;
1. 创建测试数据库 ;
2. 创建分区函数;
3. 创建分区架构;
4. 创建分区表;
5. 创建分区索引 ;
6. 分区切换 ;
7. 查询哪些表使用了分区表;
-- 作成者 leno
-- 日期: 2009-06-06 23:50:01.700
-- 创建测试数据库
USE master
GO IF OBJECT_ID(N'PartitionDataDB') IS NOT NULLDROP DATABASE PartitionDataDBGOCREATE DATABASE PartitionDataDB
ON PRIMARY(NAME = N'File_A_H',FILENAME ='D:/Microsoft/Microsoft SQL Server 2008/MSSQL10.MSSQLSERVER/MSSQL/DATA/PartitionDataDB_AH.mdf'),FILEGROUP FileGroup_I_N(NAME = N'File_I_N',FILENAME ='D:/Microsoft/Microsoft SQL Server 2008/MSSQL10.MSSQLSERVER/MSSQL/DATA/PartitionDataDB_IN.mdf'),FILEGROUP FileGroup_M_Z(NAME = N'File_M_Z',FILENAME ='D:/Microsoft/Microsoft SQL Server 2008/MSSQL10.MSSQLSERVER/MSSQL/DATA/PartitionDataDB_MZ.mdf')GO-- 创建分区函数
USE PartitionDataBase;
GOCREATE PARTITION FUNCTION StaffNameRangePFN(varchar(100))ASRANGE LEFT FOR VALUES ('H','M')GO-- 创建分区架构
CREATE PARTITION SCHEME StaffNamePScheme
ASPARTITION StaffNameRangePFNTO ([PRIMARY], FileGroup_I_N, FileGorup_M_Z)GO-- 创建分区表
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 oGROUP BY $partition.StaffNameRangePFN(StaffName)ORDER BY [Partition Number]/*
1 Abigail Guy 364
2 Hannah Lynn 2413 Maggie Yvette 395*/
-- 插入测试数据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 oGROUP BY $partition.StaffNameRangePFN(StaffName)ORDER BY [Partition Number]/*
1 Abigail Guy 13642 Hannah Lynn 2413 Maggie Yvette 395*/
-- 从结果看出,已经重新分区过了
-- 重新改变布局
Use master
GOALTER DATABASE PartitionDataDB ADD FILEGROUP FileGroup_0_9GOALTER DATABASE PartitionDataDBADD FILE (NAME = N'File_0_9',FileName = 'D:/Microsoft/Microsoft SQL Server 2008/MSSQL10.MSSQLSERVER/MSSQL/DATA/PartitionDataBase_09.mdf')TO FILEGROUP FileGroup_0_9GOUse PartitionDataBaseGOALTER PARTITION SCHEME StaffNamePSchemeNEXT USED FileGroup_0_9;GOALTER 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 oGROUP BY $partition.StaffNameRangePFN(StaffName)ORDER BY [Partition Number]/*
1 10 Green Milton Drive 999 Rocky Hague Freeway 1000
2 Abigail Guy 3643 Hannah Lynn 2414 Maggie Yvette 395*/
-- 创建聚集分区索引
CREATE CLUSTERED index IXC_Staff_StaffName on dbo.Staff ( StaffName )
go- - 分区切换
6.1切换分区表的一个分区到普通数据表中: Partition to Table
首先建立普通数据表 dbo.StaffName_Num ,该用来存放表StaffName 数字STAFF的数据
-- 创建表
if OBJECT_ID('dbo.StaffName_Num') is not null
drop table dbo.StaffName_Numgo create table dbo.StaffName_Num( StaffName varchar ( 100 ) not null ) on [FileGroup_0_9]go--注意这里建表不能为空或prmary,因为我们建的分区表不在primary文件组
--如果这样会出现
--消息 4939,级别 16,状态 1,第 1 行
ALTER TABLE SWITCH 语句失败。索引'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--这里的索引也要建立一样的,
--不然也会出现这样的提示
--消息 4913,级别 16,状态 1,第 1 行
ALTER TABLE SWITCH 语句失败。表 'PartitionDataDB.dbo.Staff' 具有聚集索引 'IXC_Staff_StaffName',而表 'PartitionDataDB.dbo.StaffName_Num' 没有聚集索引。-- 开始切换,将数字开头的StaffName切换到刚才建立的表
alter table dbo.Staff switch partition 1 to dbo.StaffName_Num
-- 查看StaffName_Num表结果
select top 1 * from dbo.StaffName_Num
/*
10 Green Milton Drive
100 East New Way100 North White First Street100 West Green First St.102 East White Nobel St.*/
-- 查看StaffName表结果
select * from dbo.Staff where StaffName like '100'+'%'
/*
NULL
*/
满足条件: 1. 普通表必须建立在分区表切换分区所在的文件组上。 2. 普通表的表结构跟分区表的一致; 3. 普通表上的索引要跟分区表一致。 4. 普通表必须是空表,不能有任何数据。
-- 查看结果
6.1 切换普通表数据到分区表的一个分区中: Table to Partition
上面我们已经把分区表 切换 了,现在我们再切换回来:
alter table dbo.StaffName_Num switch to dbo.Staff partition 1
但是,此时有错误发生:
消息 4982,级别 16,状态 1,第 1 行
ALTER TABLE SWITCH 语句失败。源表 'PartitionDataDB.dbo.StaffName_Num' 的检查约束所允许的值对于目标表 'PartitionDataDB.dbo.Staff' 中分区 1 定义的范围是不允许的。这就奇怪了,能把数据从分区切换进来却切换不出去。出错信息中提示我们是普通表的 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) )切换普通表数据到分区,除了满足上面的 4 个条件外,还要加上一条:普通表必须加上和分区数据范围一致的 check 约束条件。
6.3 切换分区表的分区到另一分区表: Partition to Partition
这里已无再建分区表了,,
实际上,分区表分区切换并没有真正去移动数据,而是 SQL Server 在系统底层改变了表的元数据。因此分区表分区切换是高效、快速、灵活的。利用分区表的分区切换功能,我们可以快速加载数据到分区表。卸载分区数据到普通表,然后 truncate 普通表,以实现快速删除分区表数据。快速归档不活跃数据到历史表。
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_rightWHEN 1 THEN ' RIGHT '
ELSE ' LEFT ' END AS Range, p.rows AS RowsFROM sys.partitions AS pJOIN sys.indexes AS iON i. object_id = p. object_idAND i.index_id = p.index_idJOIN sys.data_spaces AS dsON ds.data_space_id = i.data_space_idJOIN sys.partition_schemes AS psON ps.data_space_id = ds.data_space_idJOIN sys.partition_functions AS pfON pf.function_id = ps.function_idJOIN sys.destination_data_spaces AS dds2ON dds2.partition_scheme_id = ps.data_space_id AND dds2.destination_id = p.partition_numberJOIN sys.filegroups AS fgON fg.data_space_id = dds2.data_space_idLEFT JOIN sys.partition_range_values AS prv_leftON ps.function_id = prv_left.function_idAND prv_left.boundary_id = p.partition_number - 1LEFT JOIN sys.partition_range_values AS prv_rightON ps.function_id = prv_right.function_idAND prv_right.boundary_id = p.partition_number WHEREOBJECTPROPERTY (p. object_id , ' ISMSShipped ' ) = 0UNION ALL-- non-partitioned table/indexes
SELECTOBJECT_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 RowsFROM sys.partitions AS pJOIN sys.indexes AS iON i. object_id = p. object_idAND i.index_id = p.index_idJOIN sys.data_spaces AS dsON ds.data_space_id = i.data_space_idJOIN sys.filegroups AS fgON fg.data_space_id = i.data_space_idWHEREOBJECTPROPERTY (p. object_id , ' ISMSShipped ' ) = 0ORDER BY ObjectName, IndexID, PartitionNumber;详细的分区表与索引可以阅读:
参考:
转载地址:http://ygfmi.baihongyu.com/