SQL Server 表分区之水平表分区

q9989

　　什么是表分区？
　　表分区分为水平表分区和垂直表分区，水平表分区就是将一个具有大量数据的表，进行拆分为具有相同表结构的若干个表；而垂直表分区就是把一个拥有多个字段的表，根据需要进行拆分列，然后根据某一个字段进行关联。
　　表分区分为以下五个步骤：
　　1、创建文件组
　　2、创建数据文件
　　3、创建分区函数
　　4、创建分区方案
　　5、创建分区表
　　水平表分区
　　创建文件组：
　　语法：
　　

　　
alter database <数据库名> add filegroup <文件组名>
　　

　　

alter database Test add filegroup GroupOne　　

　　

alter database Test add filegroup GroupTwo　　

　　

alter database Test add filegroup GroupThree　　

　　

alter database Test add filegroup GroupFour　　

　　

alter database Test add filegroup GroupFive　　

　　创建数据文件到指定文件组：
　　语法：
　　

　　

　　
alter database <数据库名称> add file <文件属性> to filegroup <文件组名称>
　　

　　
<文件属性>
　　
(
　　name=文件的逻辑名称,
　　filename=文件的物理名称,
　　size=文件初始大小,
　　filegrowth=文件自动增长量(数值或百分比),
　　maxsize=文件增长的最大值
　　
)
　　

　　

alter database Test add file　　
(
　　name
=N'OneFile',　　filename
=N'D:\DataDB\OneFile.mdf',　　size
=3MB,　　filegrowth
=10%,　　maxsize
=unlimited　　
)
　　
to filegroup GroupOne
　　

　　
alter database Test add file
　　
(
　　name=N'TwoFile',
　　filename=N'D:\DataDB\TwoFile.mdf',
　　size=3MB,
　　filegrowth=10%,
　　maxsize=unlimited   
　　
)
　　
to filegroup GroupTwo
　　

　　
alter database Test add file
　　
(
　　name=N'ThreeFile',
　　filename=N'D:\DataDB\ThreeFile.mdf',
　　size=3MB,
　　filegrowth=10%,
　　maxsize=unlimited   
　　
)
　　
to filegroup GroupThree
　　

　　
alter database Test add file
　　
(
　　name=N'FourFile',
　　filename=N'D:\DataDB\FourFile.mdf',
　　size=3MB,
　　filegrowth=10%,
　　maxsize=unlimited   
　　
)
　　
to filegroup GroupFour
　　

　　
alter database Test add file
　　
(
　　name=N'FiveFile',
　　filename=N'D:\DataDB\FiveFile.mdf',
　　size=3MB,
　　filegrowth=10%,
　　maxsize=unlimited   
　　
)
　　
to filegroup GroupFive
　　

　　创建分区函数：
　　创建一个分区函数，创建分区函数的目的是告诉 SQL Server 以什么方式对分区表进行分区。
　　语法：
　　

create partition function　　
Part_Fun(int)   
　　
as range [left/right]   
　　
for values ('1000','2000'，'3000'，'4000'，'5000')        
　　

　　

create partition function　　
Part_Fun(
int)　　

as range right　　
for values ('1000','2000','3000','4000','5000')        
　　

　　删除分区函数：
　　

　　
drop partition function <分区函数名>
　　

　　

　　
drop partition function Part_Fun
　　

　　PS：只有当分区函数没有应用到分区方案中时，指定的分区函数才能被删除。
　　创建分区方案：
　　分区方案的作用是将分区函数生成的分区映射到文件组中去。分区函数的作用是告诉SQL Server，如何将数据进行分区，而分区方案的作用则是告诉 SQL Server 将已分区的数据放在哪个文件组中。
　　语法：
　　

　　
create partition scheme        
　　
<分区方案名称>   
　　
as partition <分区函数名称>   
　　
to (文件组名称,，，，)   
　　

　　

create partition scheme　　
Part_Plan   
　　
as partition Part_Fun   
　　
to ('GroupOne','GroupTwo','GroupThree','GroupFour','GroupFive')
　　

　　一执行，结果报错了。

　　不对呀，我明明建立的是五个分区文件组，分区函数也是分为五份的啊。其实这里的意思应该就是后续数据的问题了，首先不可能保证这个表永远就 5000 条数据的，所以他在这里的意思就是后续数据存储的文件组。这里我把后续数据放在最后一个文件组里面。
　　

create partition scheme　　
Part_Plan   
　　
as partition Part_Fun   
　　
to ('GroupOne','GroupTwo','GroupThree','GroupFour','GroupFive','GroupFive')
　　

　　删除分区方案：
　　

　　
drop partition scheme<分区方案名称>
　　

　　

　　
drop partition scheme Part_Plan
　　

　　PS：当没有分区表引用该分区方案时，才能对其进行删除。
　　创建分区表：
　　语法：
　　

　　
create table <表名>   
　　
(
　　column1        int        not null  primary key nonclustered,   
　　column2        int        not null   
　　
) on <分区方案名>(分区列名)        
　　

　　

create table US_Info　　
(
　　ID        
int        not null    primary key>Name    nvarchar(32)    null,　　CreateTime   
nvarchar(32)    null　　
)
on Part_Plan(ID)　　

　　PS：如果在表中创建有主键、唯一索引、聚集索引，则分区依据列必须为该列之一。即分区依据列必须建立在主键、唯一索引、聚集索引之上。
　　创建分区索引：
　　语法：
　　

　　
create [ unique [ clustered | nonclustered ] ]  
　　
index <索引名称>   
　　
on <表名>(列名)        
　　
on <分区方案名>(分区依据列名)   
　　

　　

create nonclustered　　
index Part_Non_Name   
　　
on US_Info(Name)        
　　
on Part_Plan(ID)   
　　

　　在表 US_Info 中插入5000条数据：
　　

declare @I    int　　
set @I=1
　　
while(@I<=5000)
　　
begin
　　insert into US_Info(Name,CreateTime)
　　values('名称'+convert(nvarchar,@I),Convert(nvarchar,GETDATE(),121))
　　set @I=@I+1
　　
end
　　

　　
select * from US_Info
　　

　　查询指定值位于数据表哪个分区中：
　　

　　

　　
select $partition.Part_Fun('3050')   
　　

　　查询分区表中，每个分区存在的数据的行数：
　　

　　

　　
select $partition.Part_Fun(ID) as Part_Num,count(1) as R_Count
　　
from US_Info
　　
group by  $partition.Part_Fun(ID)
　　

　　查询指定分区中的数据：
　　

　　

　　
select * from US_Info
　　
where $partition.Part_Fun(ID)=3
　　

　　拆分分区：
　　在分区函数中新增一个边界值，即可将 1 个分区拆分为 2 个。
　　

　　
alter partition function Part_Fun()
　　
split range(N'2500')  
　　

　　一执行，报错了，拆分不了，因为前面我们已经用分区函数指定了分区和文件组，那就要先添加一个文件组。
　　为分区方案指定下一个文件组：
　　

　　
alter database Test add filegroup GroupSix
　　

　　

　　
alter database Test add file
　　
(
　　name=N'SixFile',
　　filename=N'D:\DataDB\SixFile.mdf',
　　size=3MB,
　　filegrowth=10%,
　　maxsize=unlimited   
　　
)
　　
to filegroup GroupSix
　　

　　

　　

　　
alter partition scheme Part_Plan  
　　
next used GroupSix   
　　

　　然后再来对分区进行拆分：
　　

　　
alter partition function Part_Fun()   
　　
split range        
　　
(N'2500')  
　　

　　合并分区：
　　与拆分分区相反，去除一个边界值即可。
　　

　　
alter partition function Part_Fun()     
　　
merge range        
　　
(N'2500')  
　　

　　复制分区表中的数据到普通表：
　　复制分区表中的数据到普通表需要满足以下条件：
　　数据表的结构必须相同，即字段数量、字段类型等，字段与字段之间必须对应。
　　两个表必须位于同一文件组，所以创建普通表的时候就需要指定文件组。
　　

create table US_Info_back　　
(

　　ID        int        not null    primary key>　　Name    nvarchar(32)    null,
　　CreateTime    nvarchar(32)    null
　　
)on GroupThree   
　　

　　将分区表中的数据复制到普通表：
　　

　　

　　
alter table US_Info
　　
switch partition 3
　　
to US_Info_back
　　

　　
select * from US_Info_back
　　

　　将普通表中的数据复制到分区表：
　　

　　

　　
alter table US_Info_back   
　　
switch to US_Info   
　　
partition 6        
　　

　　PS：将普通表中的数据复制到分区表时，需要先删除分区表的索引。
　　将普通表转换为分区表：
　　当数据库已经存在数据的时候，就不能像上面那样直接建立分区表了，只能将普通表转换为分区表，只需在该普通表上创建一个聚集索引，并在该聚集索引中使用分区方案即可。
　　如果是已经存在的聚集索引，那么需要删除然后重新建立，并使用分区方案。
　　现在我有一个现成的表 UserInfo，因为它存在一个主键，而建立主键时，系统会自动为主键列添加聚集索引，因为这个聚集索引没法删除，所以我现在要先删除这个主键，然后重新建立一个主键，并设置为非聚集索引，然后为主键创建一个聚集索引（会覆盖非聚集索引），并使用分区方案指定分区列即可。
　　

　　
exec sp_helpconstraint UserInfo      
　　

　　

　　
alter table UserInfo drop constraint PK__UserInfo__5A2040BBA6D6767A
　　

　　

　　
alter table UserInfo add constraint PK__UserInfo__5A2040BBA6D6767A primary key nonclustered (U_Id)
　　

　　

　　

　　

　　
create clustered index CLU_StuNo
　　
on UserInfo(U_Id)  
　　
on Part_Plan(U_Id)        
　　

　　为这个表也插入5000条数据，看看效果：
　　

declare @I    int　　
select @I=U_Id from UserInfo order by U_Id desc
　　
while(@I<=5000)
　　
begin
　　insert into UserInfo(U_No,U_Name,U_Pwd)
　　values('demo'+convert(nvarchar,@I),'demo'+convert(nvarchar,@I),'40D1C69C7B86064EA140C13CE8ED0E15')
　　set @I=@I+1
　　
end
　　

　　
select * from UserInfo
　　
go
　　

　　查看分区表中，每个分区存在的数据的行数：
　　

　　
select $partition.Part_Fun(U_Id) as Part_Num,count(1) as R_Count
　　
from UserInfo
　　
group by  $partition.Part_Fun(U_Id)
　　
order by Part_Num
　　

　　查看数据库分区信息 SQL（复制来的）：
　　

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 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
　　
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://www.cnblogs.com/knowledgesea/p/3696912.html
　　http://blog.csdn.net/lgb934/article/details/8662956