实例介绍
【实例截图】
【核心代码】SQL Server 2008 分区表实例
--本文演示了 SQL Server 2008 分区表实例: --1. 创建测试数据库 ; --2. 创建分区函数; --3. 创建分区架构; --4. 创建分区表; --5. 创建分区索引 ; --6. 分区切换 ; --7. 查询哪些表使用了分区表; -- 创建测试数据库 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 -- 创建分区函数 USE PartitionDataBase; GO CREATE PARTITION FUNCTION StaffNameRangePFN(varchar(100)) AS RANGE LEFT FOR VALUES ('H','M') GO -- 创建分区架构 CREATE PARTITION SCHEME StaffNamePScheme AS PARTITION StaffNameRangePFN TO ([PRIMARY], FileGroup_I_N, FileGroup_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 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] -- 从结果看出,已经重新分区过了 -- 重新改变布局 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] -- 创建聚集分区索引 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_Num go create table dbo.StaffName_Num ( StaffName varchar ( 100 ) not null ) on [FileGroup_0_9] go --注意这里建表不能为空或primary,因为我们建的分区表不在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 -- 查看StaffName表结果 select * from dbo.Staff where StaffName like '100' '%' --满足条件: --1. 普通表必须建立在分区表切换分区所在的文件组上。 --2. 普通表的表结构跟分区表的一致; --3. 普通表上的索引要跟分区表一致。 --4. 普通表必须是空表,不能有任何数据。 --5. 切换普通表数据到分区,除了满足上面的 4 个条件外, -- 还要加上一条:普通表必须加上和分区数据范围一致的 check 约束条件。 -- 查看结果 --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_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
标签: 实例 sql SQL Server Server 2
小贴士
感谢您为本站写下的评论,您的评论对其它用户来说具有重要的参考价值,所以请认真填写。
- 类似“顶”、“沙发”之类没有营养的文字,对勤劳贡献的楼主来说是令人沮丧的反馈信息。
- 相信您也不想看到一排文字/表情墙,所以请不要反馈意义不大的重复字符,也请尽量不要纯表情的回复。
- 提问之前请再仔细看一遍楼主的说明,或许是您遗漏了。
- 请勿到处挖坑绊人、招贴广告。既占空间让人厌烦,又没人会搭理,于人于己都无利。
关于好例子网
本站旨在为广大IT学习爱好者提供一个非营利性互相学习交流分享平台。本站所有资源都可以被免费获取学习研究。本站资源来自网友分享,对搜索内容的合法性不具有预见性、识别性、控制性,仅供学习研究,请务必在下载后24小时内给予删除,不得用于其他任何用途,否则后果自负。基于互联网的特殊性,平台无法对用户传输的作品、信息、内容的权属或合法性、安全性、合规性、真实性、科学性、完整权、有效性等进行实质审查;无论平台是否已进行审查,用户均应自行承担因其传输的作品、信息、内容而可能或已经产生的侵权或权属纠纷等法律责任。本站所有资源不代表本站的观点或立场,基于网友分享,根据中国法律《信息网络传播权保护条例》第二十二与二十三条之规定,若资源存在侵权或相关问题请联系本站客服人员,点此联系我们。关于更多版权及免责申明参见 版权及免责申明
网友评论
我要评论