SQL Server 2005 创建分区表

背景:随着公司的业务迅猛发展,导致部分数据表的数据量剧增(T_ZK_C0075ALog、T_ZK_C007ALog),无法满足用户对查询效率、性能的要求。

意义:在于将大数据从物理上切割为几个相互独立的小部分,从而在查询时只取出其中一个或几个分区,减少影响的数据;另外,对置于不同文件组的分区,并行查询的性能也要高于对整个表的查询性能。

事实上,在SQL Server 2005 中就已经包含了分区功能,甚至在2005之前,还存在一个叫做“Partitioned Views”的功能,能通过将同样结构的表Union在一个View中,实现类似现在分区表的效果。而在SQL Server 2008 中,分区功能得到了显著加强,使得我们不仅能够对表和索引做分区,而且允许对分区上锁,而不是之前的全表上锁。


相关知识参考:

1、对于SQL SERVER中分区表的理解和相关理论知识,请参考文章:

T-SQL查询进阶--理解SQL SERVER中的分区表

理解SQL SERVER中的分区表(提供SQL语句)

2、对于SQL Server数据库中,关于文件的存放方式,以及文件和文件组,请参考文章:

SQL Server中数据库文件的存放方式,文件和文件组

3、对于创建百万或千万级的测试数据表,建议采用 递归CTE插入。请参考文章:

SQL Server 2012中快速插入批量数据的示例及疑惑

SQL2008使用CTE递归查询批量插入500万数据


创建分区表的步骤:

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

二、创建与删除文件组;

三、创建与删除文件;

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

五、查看分区是否成功;


一、创建与删除分区函数

1.1创建分区函数

SQL Server 2005 创建分区表
CREATE PARTITION FUNCTION F_Partition_tzkc0075aLog_Range(datetime)
AS RANGE LEFT FOR VALUES (
'20130131 23:59:59.997',   -- 2013 年 1 月
'20130228 23:59:59.997',   -- 2013 年 2 月
'20130331 23:59:59.997',   -- 2013 年 3 月
'20130430 23:59:59.997',    -- 2013 年 4 月
'20130531 23:59:59.997',   -- 2013 年 5 月
'20130630 23:59:59.997',   -- 2013 年 6 月
'20130731 23:59:59.997',   -- 2013 年 7 月
'20130831 23:59:59.997',   -- 2013 年 8 月
'20130930 23:59:59.997',   -- 2013 年 9 月
'20131031 23:59:59.997',   -- 2013 年 10 月
'20131130 23:59:59.997',   -- 2013 年 11 月
'20131231 23:59:59.997'    -- 2013 年 12 月*/
)
SQL Server 2005 创建分区表

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

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

1.2删除分区函数

DROP PARTITION FUNCTION F_Partition_tzkc0075aLog_Range;
GO

二、创建与删除文件组

2.1、创建文件组

SQL Server 2005 创建分区表
ALTER DATABASE DB_ZK3 ADD FILEGROUP FG1_TZKC0075Log;
ALTER DATABASE DB_ZK3 ADD FILEGROUP FG2_TZKC0075Log;
ALTER DATABASE DB_ZK3 ADD FILEGROUP FG3_TZKC0075Log;
ALTER DATABASE DB_ZK3 ADD FILEGROUP FG4_TZKC0075Log;

ALTER DATABASE DB_ZK3 ADD FILEGROUP FG5_TZKC0075Log;
ALTER DATABASE DB_ZK3 ADD FILEGROUP FG6_TZKC0075Log;
ALTER DATABASE DB_ZK3 ADD FILEGROUP FG7_TZKC0075Log;
ALTER DATABASE DB_ZK3 ADD FILEGROUP FG8_TZKC0075Log;

ALTER DATABASE DB_ZK3 ADD FILEGROUP FG9_TZKC0075Log;
ALTER DATABASE DB_ZK3 ADD FILEGROUP FG10_TZKC0075Log;
ALTER DATABASE DB_ZK3 ADD FILEGROUP FG11_TZKC0075Log;
ALTER DATABASE DB_ZK3 ADD FILEGROUP FG12_TZKC0075Log;
SQL Server 2005 创建分区表

2.2、删除文件组

SQL Server 2005 创建分区表
ALTER DATABASE DB_ZK3 REMOVE FILEGROUP FG1_TZKC0075Log;
ALTER DATABASE DB_ZK3 REMOVE FILEGROUP FG2_TZKC0075Log;
ALTER DATABASE DB_ZK3 REMOVE FILEGROUP FG3_TZKC0075Log;
ALTER DATABASE DB_ZK3 REMOVE FILEGROUP FG4_TZKC0075Log;

ALTER DATABASE DB_ZK3 REMOVE FILEGROUP FG5_TZKC0075Log;
ALTER DATABASE DB_ZK3 REMOVE FILEGROUP FG6_TZKC0075Log;
ALTER DATABASE DB_ZK3 REMOVE FILEGROUP FG7_TZKC0075Log;
ALTER DATABASE DB_ZK3 REMOVE FILEGROUP FG8_TZKC0075Log;

ALTER DATABASE DB_ZK3 REMOVE FILEGROUP FG9_TZKC0075Log;
ALTER DATABASE DB_ZK3 REMOVE FILEGROUP FG10_TZKC0075Log;
ALTER DATABASE DB_ZK3 REMOVE FILEGROUP FG11_TZKC0075Log;
ALTER DATABASE DB_ZK3 REMOVE FILEGROUP FG12_TZKC0075Log;
SQL Server 2005 创建分区表

2.3、查询文件组

select name,type_desc,physical_name,state_desc,size,growth 
from sys.database_files

三、创建与删除文件

3.1、删除文件

ALTER DATABASE DB_ZK3 REMOVE FILE F4_TZKC0075Log;

3.2、创建文件

SQL Server 2005 创建分区表
ALTER DATABASE DB_ZK3 ADD FILE(NAME=N'FB1_TZKC0075Log', FILENAME=N'D:\DB_ZK3\FB1_TZKC0075Log.ndf', SIZE=30720KB, FILEGROWTH=10% ) 
TO FILEGROUP [FG1_TZKC0075Log];
ALTER DATABASE DB_ZK3 ADD FILE(NAME=N'FB2_TZKC0075Log', FILENAME=N'D:\DB_ZK3\FB2_TZKC0075Log.ndf', SIZE=30720KB, FILEGROWTH=10% ) 
TO FILEGROUP [FG2_TZKC0075Log];
ALTER DATABASE DB_ZK3 ADD FILE(NAME=N'FB3_TZKC0075Log', FILENAME=N'D:\DB_ZK3\FB3_TZKC0075Log.ndf', SIZE=30720KB, FILEGROWTH=10% ) 
TO FILEGROUP [FG3_TZKC0075Log];
ALTER DATABASE DB_ZK3 ADD FILE(NAME=N'FB4_TZKC0075Log', FILENAME=N'D:\DB_ZK3\FB4_TZKC0075Log.ndf', SIZE=30720KB, FILEGROWTH=10% ) 
TO FILEGROUP [FG4_TZKC0075Log];

ALTER DATABASE DB_ZK3 ADD FILE(NAME=N'FB5_TZKC0075Log', FILENAME=N'D:\DB_ZK3\FB5_TZKC0075Log.ndf', SIZE=30720KB, FILEGROWTH=10% ) 
TO FILEGROUP [FG5_TZKC0075Log];
ALTER DATABASE DB_ZK3 ADD FILE(NAME=N'FB6_TZKC0075Log', FILENAME=N'D:\DB_ZK3\FB6_TZKC0075Log.ndf', SIZE=30720KB, FILEGROWTH=10% ) 
TO FILEGROUP [FG6_TZKC0075Log];
ALTER DATABASE DB_ZK3 ADD FILE(NAME=N'FB7_TZKC0075Log', FILENAME=N'D:\DB_ZK3\FB7_TZKC0075Log.ndf', SIZE=30720KB, FILEGROWTH=10% ) 
TO FILEGROUP [FG7_TZKC0075Log];
ALTER DATABASE DB_ZK3 ADD FILE(NAME=N'FB8_TZKC0075Log', FILENAME=N'D:\DB_ZK3\FB8_TZKC0075Log.ndf', SIZE=30720KB, FILEGROWTH=10% ) 
TO FILEGROUP [FG8_TZKC0075Log];

ALTER DATABASE DB_ZK3 ADD FILE(NAME=N'FB9_TZKC0075Log', FILENAME=N'D:\DB_ZK3\FB9_TZKC0075Log.ndf', SIZE=30720KB, FILEGROWTH=10% ) 
TO FILEGROUP [FG9_TZKC0075Log];
ALTER DATABASE DB_ZK3 ADD FILE(NAME=N'FB10_TZKC0075Log', FILENAME=N'D:\DB_ZK3\FB10_TZKC0075Log.ndf', SIZE=30720KB, FILEGROWTH=10% ) 
TO FILEGROUP [FG10_TZKC0075Log];
ALTER DATABASE DB_ZK3 ADD FILE(NAME=N'FB11_TZKC0075Log', FILENAME=N'D:\DB_ZK3\FB11_TZKC0075Log.ndf', SIZE=30720KB, FILEGROWTH=10% ) 
TO FILEGROUP [FG11_TZKC0075Log];
ALTER DATABASE DB_ZK3 ADD FILE(NAME=N'FB12_TZKC0075Log', FILENAME=N'D:\DB_ZK3\FB12_TZKC0075Log.ndf', SIZE=30720KB, FILEGROWTH=10% ) 
TO FILEGROUP [FG12_TZKC0075Log];
SQL Server 2005 创建分区表

四、创建与删除分区架构

4.1、创建分区架构

SQL Server 2005 创建分区表
CREATE PARTITION SCHEME [S_Scheme_TZKC0075Log] 
AS
PARTITION F_Partition_tzkc0075aLog_Range
TO ([FG1_TZKC0075Log],[FG2_TZKC0075Log],[FG3_TZKC0075Log],[FG4_TZKC0075Log],[FG5_TZKC0075Log],
    [FG6_TZKC0075Log],[FG7_TZKC0075Log],[FG8_TZKC0075Log],[FG9_TZKC0075Log],[FG10_TZKC0075Log],
    [FG11_TZKC0075Lot],[FG12_TZKC0075Log],[PRIMARY]);
SQL Server 2005 创建分区表

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

4.2 删除分区架构

DROP PARTITION SCHEME [S_Scheme_TZKC0075Log]; 

五、查看分区是否成功

SQL Server 2005 创建分区表
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
SQL Server 2005 创建分区表




本文转自钢钢博客园博客,原文链接:http://www.cnblogs.com/xugang/archive/2013/04/28/3049440.html,如需转载请自行联系原作者

上一篇:中科院文思海辉IBM共建大数据研究生人才培养体系 瞄准中高端人才


下一篇:深入浅出SQL Server 2008 分区函数和分区表