在SQL Server 2000及的前版本中有分区视图选项但它有些局限性在真正实施前需要考虑事情太多在SQL Server 2005及后续版本中有个水平分区选项可以分到1000个分区数据位置是由SQL Server自动处理水平分区是将表中行分成给定数量分区过程每个分区中列数量是相同

　　在个水平分区表中有多个分区每个分区对应个文件组这样就产生了很多文件组因此性能也会有所提升包括I/O性能提升所有分区可以驻扎在个区别磁盘上另个好处是可以通过备份文件组单独备份个分区此外SQL Server数据库引擎可以智能判断哪个分区上存放了什么数据如果不止个分区被访问那么还可以借助多处理器实现并行数据检索这种设计也充分利用了分区表优势

　　在运行下面脚本的前先创建几个文件夹:

　　D:\PartitionDB\FG1

　　D:\PartitionDB\FG2

　　D:\PartitionDB\FG3

　　接下来运行下面脚本创建个新数据库在 3个文件组上创建 3个数据文件:

　　脚本1:创建个表使用 3个数据文件

USE Master
　　GO

　　CREATE DATABASE DBForPartitioning

　　ON PRIMARY

　　(NAME='DBForPartitioning_1',

　　FILENAME=

　　'D:\PartitionDB\FG1\DBForPartitioning_1.mdf',

　　SIZE=2,

　　MAXSIZE=100,

　　FILEGROWTH=1 ),

　　FILEGROUP FG2

　　(NAME = 'DBForPartitioning_2',

　　FILENAME =

　　'D:\PartitionDB\FG2\DBForPartitioning_2.ndf',

　　SIZE = 2,

　　MAXSIZE=100,

　　FILEGROWTH=1 ),

　　FILEGROUP FG3

　　(NAME = 'DBForPartitioning_3',

　　FILENAME =

　　'D:\PartitionDB\FG3\DBForPartitioning_3.ndf',

　　SIZE = 2,

　　MAXSIZE=100,

　　FILEGROWTH=1 )

　　GO


　　现在我们有个数据库DBForPartitioning在 3个文件组中创建了 3个数据文件可以使用下面脚本进行确认:

　　脚本2:确定文件组数量和DBForPartitioning数据库数据文件数量

Use DBFOrPartitioning 　　GO

　　-- Confirm Filegroups

　　SELECT name as [File Group Name]

　　FROM sys.filegroups

　　WHERE type = 'FG'

　　GO -- Confirm Datafiles

　　SELECT name as [DB File Name],physical_name as [DB File Path]

　　FROM sys.database_files

　　where type_desc = 'ROWS'

　　GO



　　

　　图 1 返回文件组和数据文件信息

　　规划

　　在SQL Server中要实现水平分区表有 3个主要步骤:

　　l 创建分区它将为分割分区中数据建立标准

　　l 创建分区方案将创建分区映射到文件组它和数据在磁盘上物理存储是相关

　　l 创建个表将其链接到分区方案也链接到分区这个时候将会使用到个分区列

　　在真正开始这 3个步骤的前我们先要对要进行水平分区表结构充分地了解在前面我们已经提到要对将要归档销售数据表进行水平分区假设我们分区归档表结构是SalesArchival(saleTime dateTime, item varchar(50))数据将要被送到分区列叫做分区列它将用在分区中作为分区键分区列很重要需要满足下面条件:

　　l 分区列总是只有个唯列或计算列或通过组合多个列持续计算列

　　l 任何数据类型分区列都可以用作索引键除了TIMESTAMP数据类型

　　创建分区

　　在我们例子中有20072008和2009年销售数据因此这里我们创建 3个分区表在分区中提供两个分区范围分区将会创建数据边界我们需要将2009年的前销售数据全部放到第个分区中2009年销售数据放在第 2个分区中2009年以后销售数据放在第 3个分区中

　　脚本3:创建分区

　　 Use DBForPartitioning 　　GO

　　CREATE PARTITION FUNCTION salesYearPartitions (datetime)

　　AS RANGE RIGHT FOR VALUES ( '2009-01-01', '2010-01-01')

　　GO



　　中提供分区键是分区表中主键分区范围也是基于这列在我们表中这列是saleTime其数据类型是DATETIME定义范围可能是RIGHT或LEFT我们这里使用范围是RIGHTRIGHT意味着<或>=LEFT意味着<=或>

　　在我们例子中在下面这些值上使用了RIGHT

　　范围RIGHT转换

　　使用saleTime记录目标分区

　　< 2009-01-01DBForPartitioning_1

　　>=2009-01-01 and < 2010-01-01DBForPartitioning_2

　　>=2010-01-01DBForPartitioning_3

　　如果我们使用范围LEFT分区将会是:

　　范围LEFT转换

　　使用saleTime记录目标分区

　　< =2009-01-01DBForPartitioning_1

　　>2009-01-01 and < =2010-01-01DBForPartitioning_2

　　>2010-01-01DBForPartitioning_3

　　通过上面两个例子你会发现使用日期作为分区键时数据存储位置没有太大差异

　　创建分区方案

　　为了得到优化文件结构我们已经为这个数据库创建了 3个文件组使用分区创建了 3个分区现在该链接文件组和分区了我们必须为分区定义物理存储在我们设计中每个分区都有独立文件组这个思路方法也让我们在载入归档数据和利用文件组备份做删除操作时性能得到提升

　　脚本4:创建分区方案

　　 Use DBForPartitioning 　　GO

　　CREATE PARTITION SCHEME Test_PartitionScheme

　　AS PARTITION salesYearPartitions

　　TO ([PRIMARY], FG2, FG3 )

　　GO



　　映射分区到文件组是非常灵活在个文件组可以存在多个分区个分区只能分配到个文件组上

　　创建分区表

　　现在我们可以使用分区方案和分区列创建水平分区表了为了使性能更优我们使用SaleTime列作为主键同时作为这个表簇索引

　　脚本5:使用水平分区创建表

Use DBFOrPartitioning 　　GO

　　CREATE TABLE SalesArchival

　　(SaleTime datetime PRIMARY KEY,

　　ItemName varchar(50))

　　ON Test_PartitionScheme (SaleTime);

　　GO



　　往表中插入数据验证我们需要分区表功能

　　脚本6:往水平分区表中插入样例数据

Use DBFOrPartitioning 　　GO

　　INSERT INTO SalesArchival (SaleTime, ItemName)

　　SELECT '2007-03-25','Item1' UNION ALL

　　SELECT '2008-10-01','Item2' UNION ALL

　　SELECT '2009-01-01','Item1' UNION ALL

　　SELECT '2009-08-09','Item3' UNION ALL

　　SELECT '2009-12-30','Item2' UNION ALL

　　SELECT '2010-01-01','Item1' UNION ALL

　　SELECT '2010-05-24','Item3'

　　GO



　　最后验证在区别分区上行

　　脚本7:验证水平分区表上数据分布

Use DBFOrPartitioning 　　GO

　　select partition_id, index_id, partition_number, Rows

　　FROM sys.partitions

　　WHERE OBJECT_NAME(OBJECT_ID)='SalesArchival'

　　GO



　　执行结果如下

　　

　　图 2 验证水平分区表上数据分布

　　我们已经为归档数据创建并验证了水平分区表现在可以以任何方式将数据装入表中了

　　在实施水平分区表时有些东西你得考虑首先是规划时要考虑事情:

　　l 在群集表中分区列应该是主键或群集键部分

　　l 默认情况下在分区表上创建索引也会使用相同分区方案和分区列

　　l 如果分区中数据不需要修改可以把分区标记为READ ONLY

　　l 在索引重建过程中整个表都会被锁定因此不能在单个分区上使用ONLINE选项进行索引重建

　　l 如果你需要更改分区键最好重新创建表然后重新装入数据和重建索引

　　l 分区列和分区键在数据类型、长度和精度方面都应该匹配

　　l 分区功能只能在企业版和开发版中可用

　　l 所有分区都必须驻扎在相同数据库中

　　l 可以在个分区基础上重建索引这样就可以每次重建索引时都重建所有索引

　　SQL Server 2008中些功能增强

　　l 在某个指定分区或整个分区上可以执行数据压缩

　　l 为分区间和分区列使用恰当date数据类型可以减少存储需求并能提升性能

　　可以在分区上实施锁设置