SQL Server 2008 分区表实例.docx

-本文演示了 SQL Server 2008 分区表实例:--1.??创建测试数据库；--2.??创建分区函数;--3.??创建分区架构;--4.??创建分区表;--5.??创建分区索引 ;--6.??分区切换 ;--7.??查询哪些表使用了分区表；?-- 创建测试数据库USE masterGO?IF OBJECT_ID(NPartitionDataBase) IS NOT NULLDROP DATABASE PartitionDataBaseGOCREATE DATABASE PartitionDataBaseON PRIMARY(NAME = NFile_A_H,FILENAME =D:\Data\PartitionDataBase_AH.mdf),FILEGROUP FileGroup_I_N(NAME = NFile_I_N,FILENAME =D:\Data\PartitionDataBase_IN.mdf),FILEGROUP FileGroup_M_Z(NAME = NFile_M_Z,FILENAME =D:\Data\PartitionDataBase_MZ.mdf)GO-- 创建分区函数USE PartitionDataBase;GOCREATE PARTITION FUNCTION StaffNameRangePFN(varchar(100))ASRANGE LEFT FOR VALUES (H,M)GO-- 创建分区架构CREATE PARTITION SCHEME StaffNamePSchemeASPARTITION StaffNameRangePFNTO ([PRIMARY], FileGroup_I_N, FileGroup_M_Z)GO?-- 创建分区表CREATE TABLE [dbo].[Staff]?([StaffName] [varchar](100) NOT NULL)ON StaffNamePScheme ([StaffName])GO?-- 插入测试数据1INSERT 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]-- 插入测试数据2INSERT 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]--???从结果看出,已经重新分区过了-- 重新改变布局Use masterGOALTER DATABASE PartitionDataBase ADD FILEGROUP FileGroup_0_9GOALTER DATABASE PartitionDataBaseADD FILE(NAME = NFile_0_9,FileName = D:\Data\PartitionDataBase.mdf)TO FILEGROUP FileGroup_0_9GOUse PartitionDataBaseGOALTER PARTITION SCHEME StaffNamePSchemeNEXT USED FileGroup_0_9;GOALTER PARTITION FUNCTION StaffNameRangePFN()SPLIT RANGE (A);GO?-- 查