SQL Server 2005 创建分区表(转)
背景：随着公司的业务迅猛发展，导致部分数据表的数据量剧增(T_ZK_C0075ALog、T_ZK_C007ALog)，无法满足用户对查询效率、性能的要求。
意义：在于将大数据从物理上切割为几个相互独立的小部分，从而在查询时只取出其中一个或几个分区，减少影响的数据；另外，对置于不同文件组的分区，并行查询的性能也要高于对整个表的查询性能。
事
实上，在SQL Server 2005 中就已经包含了分区功能，甚至在2005之前，还存在一个叫做“Partitioned
Views”的功能，能通过将同样结构的表Union在一个View中，实现类似现在分区表的效果。而在SQL Server 2008
中，分区功能得到了显著加强，使得我们不仅能够对表和索引做分区，而且允许对分区上锁，而不是之前的全表上锁。
相关知识参考：
1、对于SQL SERVER中分区表的理解和相关理论知识，请参考文章：
2、对于SQL Server数据库中，关于文件的存放方式，以及文件和文件组，请参考文章：
3、对于创建百万或千万级的测试数据表，建议采用 递归CTE插入。请参考文章：
SQL Server 2012中快速插入批量数据的示例及疑惑
创建分区表的步骤：
一、创建与删除分区函数；
二、创建与删除文件组；
三、创建与删除文件；
四、创建与删除分区架构；
五、查看分区是否成功；
一、创建与删除分区函数
1.1、创建分区函数
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 月*/ )
2、很明显，这个分区函数创建了12个分区，因为此时 n=12，所以实际分区总数是 n+1=13。
1.2、删除分区函数
DROP PARTITION FUNCTION F_Partition_tzkc0075aLog_Range; GO
二、创建与删除文件组
2.1、创建文件组
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;
2.2、删除文件组
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;
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、创建文件
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];
4.1、创建分区架构
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]);
4.2 删除分区架构
DROP PARTITION SCHEME [S_Scheme_TZKC0075Log];
五、查看分区是否成功
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
