SQL Server 2014创建分区表

背景：公司测试数据业务迅猛发展，导致部分数据表的数据量剧增，无法满足用户对查询效率、性能的要求。 分区的意义在于将大数据从物理上切割为几个相互独立的小部分，从而在查询时只取出其中一个或几个分区，减少影响的数据；另外，对置于不同文件组的分区，并行查询的性能也要高于对整个表的查询性能。 

在SQL Server 2005 中就已经包含了分区功能，而在SQL Server 2008 以及后续的版本中，分区功能得到了显著加强，使得我们不仅能够对表和索引做分区，而且允许对分区上锁，而不是之前的全表上锁。

创建分区表的步骤：

一、创建与删除分区函数；

二、创建与删除文件组；

三、创建与删除文件；

四、创建与删除分区架构；

五、查看分区是否成功；

一、创建与删除分区函数

1.1、创建分区函数

--创建分区函数
CREATE PARTITION FUNCTION F_Partition_Range(datetime)
AS RANGE LEFT FOR VALUES (
'20140131 23:59:59',   -- 2014 年 1 月
'20140228 23:59:59',   -- 2014 年 2 月
'20140331 23:59:59',   -- 2014 年 3 月
'20140430 23:59:59',    -- 2014 年 4 月
'20140531 23:59:59',   -- 2014 年 5 月
'20140630 23:59:59',   -- 2014 年 6 月
'20140731 23:59:59',   -- 2014 年 7 月
'20140831 23:59:59',   -- 2014 年 8 月
'20140930 23:59:59',   -- 2014 年 9 月
'20141031 23:59:59',   -- 2014 年 10 月
'20141130 23:59:59',   -- 2014 年 11 月
'20141231 23:59:59'    -- 2014 年 12 月*/
)

注：1、创建分区函数语法中，LEFT | RIGHT 关键字用于指定boundary_value [ ,...n ]中的每个boundary_value 属于每个边界值间隔的哪一侧（左侧还是右侧）。如果未指定，则默认值为 LEFT。

2、很明显，这个分区函数创建了12个分区，因为此时 n=12，所以实际分区总数是 n+1=13。 

 

1.2、如果要删除分区，则使用函数

--删除分区函数
DROP PARTITION FUNCTION F_Partition_Range;
GO

 

二、创建与删除文件组

2.1、创建文件组

--创建文件组
ALTER DATABASE mydbdata ADD FILEGROUP FG1_Data;
ALTER DATABASE mydbdata ADD FILEGROUP FG2_Data;
ALTER DATABASE mydbdata ADD FILEGROUP FG3_Data;
ALTER DATABASE mydbdata ADD FILEGROUP FG4_Data;
ALTER DATABASE mydbdata ADD FILEGROUP FG5_Data;
ALTER DATABASE mydbdata ADD FILEGROUP FG6_Data;
ALTER DATABASE mydbdata ADD FILEGROUP FG7_Data;
ALTER DATABASE mydbdata ADD FILEGROUP FG8_Data;
ALTER DATABASE mydbdata ADD FILEGROUP FG9_Data;
ALTER DATABASE mydbdata ADD FILEGROUP FG10_Data;
ALTER DATABASE mydbdata ADD FILEGROUP FG11_Data;
ALTER DATABASE mydbdata ADD FILEGROUP FG12_Data;

 

2.2 如果要查询已经创建的文件组

select * from sys.filegroups

2.3、如果要删除文件组，则使用如下代码

--删除文件组
ALTER DATABASE mydbdata REMOVE FILEGROUP FG1_Data;
ALTER DATABASE mydbdata REMOVE FILEGROUP FG2_Data;
ALTER DATABASE mydbdata REMOVE FILEGROUP FG3_Data;
ALTER DATABASE mydbdata REMOVE FILEGROUP FG4_Data;
ALTER DATABASE mydbdata REMOVE FILEGROUP FG5_Data;
ALTER DATABASE mydbdata REMOVE FILEGROUP FG6_Data;
ALTER DATABASE mydbdata REMOVE FILEGROUP FG7_Data;
ALTER DATABASE mydbdata REMOVE FILEGROUP FG8_Data;
ALTER DATABASE mydbdata REMOVE FILEGROUP FG9_Data;
ALTER DATABASE mydbdata REMOVE FILEGROUP FG10_Data;
ALTER DATABASE mydbdata REMOVE FILEGROUP FG11_Data;
ALTER DATABASE mydbdata REMOVE FILEGROUP FG12_Data;

 

三、创建与删除文件

3.2、创建文件

--创建文件
ALTER DATABASE mydbdata ADD FILE(NAME=N'FB1_Data', FILENAME=N'D:\mydbdata\FB1_Data.ndf', SIZE=30720KB, FILEGROWTH=10% ) 
TO FILEGROUP [FG1_Data];
ALTER DATABASE mydbdata ADD FILE(NAME=N'FB2_Data', FILENAME=N'D:\mydbdata\FB2_Data.ndf', SIZE=30720KB, FILEGROWTH=10% ) 
TO FILEGROUP [FG2_Data];
ALTER DATABASE mydbdata ADD FILE(NAME=N'FB3_Data', FILENAME=N'D:\mydbdata\FB3_Data.ndf', SIZE=30720KB, FILEGROWTH=10% ) 
TO FILEGROUP [FG3_Data];
ALTER DATABASE mydbdata ADD FILE(NAME=N'FB4_Data', FILENAME=N'D:\mydbdata\FB4_Data.ndf', SIZE=30720KB, FILEGROWTH=10% ) 
TO FILEGROUP [FG4_Data];


ALTER DATABASE mydbdata ADD FILE(NAME=N'FB5_Data', FILENAME=N'D:\mydbdata\FB5_Data.ndf', SIZE=30720KB, FILEGROWTH=10% ) 
TO FILEGROUP [FG5_Data];
ALTER DATABASE mydbdata ADD FILE(NAME=N'FB6_Data', FILENAME=N'D:\mydbdata\FB6_Data.ndf', SIZE=30720KB, FILEGROWTH=10% ) 
TO FILEGROUP [FG6_Data];
ALTER DATABASE mydbdata ADD FILE(NAME=N'FB7_Data', FILENAME=N'D:\mydbdata\FB7_Data.ndf', SIZE=30720KB, FILEGROWTH=10% ) 
TO FILEGROUP [FG7_Data];
ALTER DATABASE mydbdata ADD FILE(NAME=N'FB8_Data', FILENAME=N'D:\mydbdata\FB8_Data.ndf', SIZE=30720KB, FILEGROWTH=10% ) 
TO FILEGROUP [FG8_Data];


ALTER DATABASE mydbdata ADD FILE(NAME=N'FB9_Data', FILENAME=N'D:\mydbdata\FB9_Data.ndf', SIZE=30720KB, FILEGROWTH=10% ) 
TO FILEGROUP [FG9_Data];
ALTER DATABASE mydbdata ADD FILE(NAME=N'FB10_Data', FILENAME=N'D:\mydbdata\FB10_Data.ndf', SIZE=30720KB, FILEGROWTH=10% ) 
TO FILEGROUP [FG10_Data];
ALTER DATABASE mydbdata ADD FILE(NAME=N'FB11_Data', FILENAME=N'D:\mydbdata\FB11_Data.ndf', SIZE=30720KB, FILEGROWTH=10% ) 
TO FILEGROUP [FG11_Data];
ALTER DATABASE mydbdata ADD FILE(NAME=N'FB12_Data', FILENAME=N'D:\mydbdata\FB12_Data.ndf', SIZE=30720KB, FILEGROWTH=10% ) 
TO FILEGROUP [FG12_Data];

 

3.2、如果需要删除文件，则使用如下代码

--删除文件
select [name],[filename] from sysfiles
GO
dbcc shrinkfile ("FB11_Data", EMPTYFILE)
go
ALTER DATABASE mydbdata REMOVE FILE FB11_Data;
GO

 

四、创建与删除分区架构

4.1、创建分区架构

--创建分区架构
CREATE PARTITION SCHEME [S_Scheme_Data] 
AS
PARTITION F_Partition_Range
TO ([FG1_Data],[FG2_Data],[FG3_Data],[FG4_Data],[FG5_Data],
    [FG6_Data],[FG7_Data],[FG8_Data],[FG9_Data],[FG10_Data],
    [FG11_Data],[FG12_Data],[PRIMARY]);

注意：建议将主数据文件 [PRIMARY]  放在分区最后！

 

4.2 删除分区架构

DROP PARTITION SCHEME [S_Scheme_Data]; 

 

 五、查看分区是否成功

select convert(varchar(50), ps.name) as partition_scheme, p.partition_number,  
convert(varchar(10), ds2.name) as filegroup, 
 convert(varchar(19), isnull(v.value, ''), 120) as range_boundary,  
str(p.rows, 9) as rows from sys.indexes i 
 join sys.partition_schemes ps on i.data_space_id = ps.data_space_id 
 join sys.destination_data_spaces dds on ps.data_space_id = dds.partition_scheme_id  
join sys.data_spaces ds2 on dds.data_space_id = ds2.data_space_id  
join sys.partitions p on dds.destination_id = p.partition_number 
and p.object_id = i.object_id and p.index_id = i.index_id  
join sys.partition_functions pf on ps.function_id = pf.function_id 
 LEFT JOIN sys.Partition_Range_values v on pf.function_id = v.function_id 
and v.boundary_id = p.partition_number - pf.boundary_value_on_right  
WHERE i.object_id = object_id('PartitionedTable')  --  PartitionedTable 对应具体的分区表名称
and i.index_id in (0, 1)  
order by p.partition_number

分区表截图

参考文章：http://www.cnblogs.com/CareySon/archive/2011/12/30/2307766.html