sqlserver2005+创建分区表+分区索引+注意事宜+分区索引

、分区索引

1. 分区表简介

使用分区表的主要目的，是为了改善大型表以及具有各种访问模式的表的可伸缩性和可管理性。

大型表：数据量巨大的表。⌝

⌝访问模式：因目的不同，需访问的不同的数据行集，每种目的的访问可以称之为一种访问模式。

分区一方面可以将数据分为更小、更易管理的部分，为提高性能起到一定的作用；另一方面，对于如果具有多个CPU的系统，分区可以是对表的操作通过并行的方式进行，这对于提升性能是非常有帮助的。

注意：只能在SQL Server Enterprise Edition 中创建分区函数。只有SQL Server Enterprise Edition 支

持分区。

2. 创建分区表或分区索引的步骤

可以分为以下步骤：

1. 确定分区列和分区数

2. 确定是否使用多个文件组

3. 创建分区函数

4. 创建分区架构（Schema）

5. 创建分区表

6. 创建分区索引

下面详细描述的创建分区表、分区索引的步骤。

2.1. 确定分区列和分区数

在开始做分区操作之前，首先要确定待分区表的访问模式，该模式决定了什么列适合做分区键。例如，对于销售数据，一般会先根据日期把数据范围限定在一个范围内，然后在这个基础上做进一步的查询，这样，

就可以把日期作为分区列。

确定了分区列之后，需要进一步确定分区数，亦即分区表中需要包含多少数据，每个分区的数据应该限定

在哪个范围。

2.2. 确定是否使用多个文件组

为了有助于优化性能和维护，应该使用文件组分离数据。一般情况下，如果经常对分区的整个数据集操作，则文件组数最好与分区数相同，并且这些文件组通常应该位于不同的磁盘上，再配合多个CPU，则SQL Server 可以并行处理多个分区，从而大大缩短处理大量复杂报表和分析的总体时间。

2.3. 创建分区函数

分区函数用于定义分区的边界条件，创建分区函数的语法如下：

CREATE PARTITION FUNCTION partition_function_name ( input_parameter_type )

AS RANGE [ LEFT | RIGHT ]

FOR VALUES ( [ boundary_value [ ,...n ] ] )

[ ; ]

参数说明：

⌝partition_function_name

是分区函数的名称。分区函数名称在数据库内必须唯一，并且符合标识符的规则。

⌝ input_parameter_type

是用于分区的列的数据类型。当用作分区列时，除text、ntext、image、xml、timestamp、varchar(max)、nvarchar(max)、varbinary(max)、别名数据类型或CLR 用户定义数据类型外，所有数据类型均有效。

实际列（也称为分区列）是在CREATE TABLE 或CREATE INDEX 语句中指定的。

boundary_value⌝

为使用partition_function_name 的已分区表或索引的每个分区指定边界值。如果boundary_value 为空，则分区函数使用partition_function_name 将整个表或索引映射到单个分区。只能使用CREATE TABLE

或CREATE INDEX 语句中指定的一个分区列。

boundary_value 是可以引用变量的常量表达式。这包括用户定义类型变量，或函数以及用户定义函数。它不能引用Transact-SQL 表达式。boundary_value 必须与input_parameter_type 中提供的数据类型相匹配或者可隐式转换为该数据类型，并且如果该值的大小和小数位数与input_parameter_type 中相应的值的大小和小数位数不匹配，则在隐式转换过程中该值不能被截断。

注意：

如果boundary_value 包含datetime 或smalldatetime 文字值，则为这些文字值在计算时假设

us_english 是会话语言。不推荐使用此行为。要确保分区函数定义对于所有会话语言都具有预期的行为，建议使用对于所有语言设置都以相同方式进行解释的常量，例如yyyymmdd 格式；或者将文字值显式转换为特定样式。有关详细信息，请参阅编写国际化Transact-SQL 语句。若要确定服务器的语言会话，请

运行SELECT @@LANGUAGE。

...n⌝

指定boundary_value 提供的值的数目，不能超过999。所创建的分区数等于n + 1。不必按顺序列出各值。如果值未按顺序列出，则Microsoft SQL Server 2005 数据库引擎将对它们进行排序，创建函数并返回一个警告，说明未按顺序提供值。如果n 包括任何重复的值，则数据库引擎将返回错误。

LEFT |⌝ RIGHT

指定当间隔值由数据库引擎按升序从左到右排序时，boundary_value [ ,...n ] 属于每个边界值间隔的哪一侧（左侧还是右侧）。如果未指定，则默认值为LEFT。

创建分区函数示例：

CREATE PARTITION FUNCTION PF_Left(int)

AS RANGE LEFT

FOR VALUES(10, 20)

GO

CREATE PARTITION FUNCTION PF_Right(int)

AS RANGE LEFT

FOR VALUES(10, 20)

GO

PF_Left 和PF_Right 分区函数的区分：

分区函数分区1 分区2 分区3

PF_Left <= 10 > 10 and <= 20 > 20

PF_Right < 10 >= 10 and < 20 >= 20

2.4. 创建分区架构（Schema）

创建分区函数后，必须将其与分区架构（Schema）相关联，以便将分区定向至特定的文件组。定义分区架构师，即使多个分区位于同一个文件组中，也必须为每个分区指定一个文件组。

创建分区架构的语法如下：

GOCREATE PARTITION SCHEME partition_scheme_name

AS PARTITION partition_function_name

[ ALL ] TO ( { file_group_name | [ PRIMARY ] } [ ,...n ] )

[ ; ]

参数：

⌝ partition_scheme_name

分区方案的名称。分区方案名称在数据库中必须是唯一的，并且符合标识符规则。

⌝ partition_function_name

使用分区方案的分区函数的名称。分区函数所创建的分区将映射到在分区方案中指定的文件组。

partition_function_name 必须已经存在于数据库中。

ALL⌝

指定所有分区都映射到在file_group_name 中提供的文件组，或映射到主文件组（如果指定了[PRIMARY]。如果指定了ALL，则只能指定一个file_group_name。

⌝file_group_name | [ PRIMARY ] [ ,...n]

指定用来持有由partition_function_name 指定的分区的文件组的名称。file_group_name 必须已经存在于

数据库中。

如果指定了[PRIMARY]，则分区将存储于主文件组中。如果指定了ALL，则只能指定一个

file_group_name。分区分配到文件组的顺序是从分区1 开始，按文件组在[,...n] 中列出的顺序进行分配。在[,...n] 中，可以多次指定同一个file_group_name。如果n 不足以拥有在partition_function_name 中指定的分区数，则CREATE PARTITION SCHEME 将失败，并返回错误。

如果partition_function_name 生成的分区数少于文件组数，则第一个未分配的文件组将标记为NEXT USED，并且出现显示命名NEXT USED 文件组的信息。如果指定了ALL，则单独的file_group_name 将为该partition_function_name 保持它的NEXT USED 属性。如果在ALTER PARTITION FUNCTION 语句中创建了一个分区，则NEXT USED 文件组将再接收一个分区。若要再创建一个未分配的文件组来拥

有新的分区，请使用ALTER PARTITION SCHEME。

在file_group_name[ 1,...n] 中指定主文件组时，必须像在[PRIMARY] 中那样分隔PRIMARY，因为它