《MS SQL Server分区表、分区索引详解》由会员分享，可在线阅读，更多相关《MS SQL Server分区表、分区索引详解（9页珍藏版）》请在金锄头文库上搜索。
1、MS SQL Server：分区表、分区索引 详解1. 分区表简介使用分区表的主要目的，是为了改善大型表以及具有各种访问模式的表的可伸缩性和可管理性。 大型表：数据量巨大的表。 访问模式：因目的不同，需访问的不同的数据行集，每种目的的访问可以称之为一种访问模式。分区一方面可以将数据分为更小、更易管理的部分，为提高性能起到一定的作用；另一方面，对于如果具有多个 CPU 的系统，分区可以是对表的操作通过并行的方式进行，这对于提升性能是非常有帮助的。注意：只能在 SQL Server Enterprise Edition 中创建分区函数。只有 SQL Server Enterprise Editio
2、n 支持分区。2. 创建分区表或分区索引的步骤可以分为以下步骤：1. 确定分区列和分区数2. 确定是否使用多个文件组3. 创建分区函数4. 创建分区架构（Schema）5. 创建分区表6. 创建分区索引下面详细描述的创建分区表、分区索引的步骤。2.1. 确定分区列和分区数在开始做分区操作之前，首先要确定待分区表的访问模式，该模式决定了什么列适合做分区键。例如，对于销售数据，一般会先根据日期把数据范围限定在一个范围内，然后在这个基础上做进一步的查询，这样，就可以把日期作为分区列。确定了分区列之后，需要进一步确定分区数，亦即分区表中需要包含多少数据，每个分区的数据应该限定在哪个范围。2.2. 确定
3、是否使用多个文件组为了有助于优化性能和维护，应该使用文件组分离数据。一般情况下，如果经常对分区的整个数据集操作，则文件组数最好与分区数相同，并且这些文件组通常应该位于不同的磁盘上，再配合多个 CPU，则 SQL Server 可以并行处理多个分区，从而大大缩短处理大量复杂报表和分析的总体时间。2.3. 创建分区函数分区函数用于定义分区的边界条件，创建分区函数的语法如下：CREATE PARTITION FUNCTION partition_function_name ( input_parameter_type )AS RANGE LEFT | RIGHT FOR VALUES ( bound
4、ary_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为使用
5、partition_function_name 的已分区表或索引的每个分区指定边界值。如果 boundary_value 为空，则分区函数使用 partition_function_name 将整个表或索引映射到单个分区。只能使用 CREATE TABLE 或 CREATE INDEX 语句中指定的一个分区列。boundary_value 是可以引用变量的常量表达式。这包括用户定义类型变量，或函数以及用户定义函数。它不能引用 Transact-SQL 表达式。boundary_value 必须与 input_parameter_type 中提供的数据类型相匹配或者可隐式转换为该数据类型，并且如
6、果该值的大小和小数位数与 input_parameter_type 中相应的值的大小和小数位数不匹配，则在隐式转换过程中该值不能被截断。注意：如果 boundary_value 包含 datetime 或 smalldatetime 文字值，则为这些文字值在计算时假设 us_english 是会话语言。不推荐使用此行为。要确保分区函数定义对于所有会话语言都具有预期的行为，建议使用对于所有语言设置都以相同方式进行解释的常量，例如 yyyymmdd 格式；或者将文字值显式转换为特定样式。有关详细信息，请参阅编写国际化 Transact-SQL 语句。若要确定服务器的语言会话，请运行 SELECT
7、LANGUAGE。 .n指定 boundary_value 提供的值的数目，不能超过 999。所创建的分区数等于 n + 1。不必按顺序列出各值。如果值未按顺序列出，则 Microsoft SQL Server 2005 数据库引擎将对它们进行排序，创建函数并返回一个警告，说明未按顺序提供值。如果 n 包括任何重复的值，则数据库引擎将返回错误。 LEFT | RIGHT指定当间隔值由 数据库引擎 按升序从左到右排序时，boundary_value ,.n 属于每个边界值间隔的哪一侧（左侧还是右侧）。如果未指定，则默认值为 LEFT。创建分区函数示例：CREATE PARTITION FUNCT
8、ION PF_Left(int)AS RANGE LEFTFOR VALUES(10, 20)GOCREATE PARTITION FUNCTION PF_Right(int)AS RANGE LEFTFOR VALUES(10, 20)GOPF_Left 和 PF_Right 分区函数的区分：分区函数 分区 1 分区 2 分区 3PF_Left 10 and 20PF_Right = 10 and = 202.4. 创建分区架构（Schema）创建分区函数后，必须将其与分区架构（Schema）相关联，以便将分区定向至特定的文件组。定义分区架构师，即使多个分区位于同一个文件组中，也必须为每个分
9、区指定一个文件组。创建分区架构的语法如下：GOCREATE PARTITION SCHEME partition_scheme_nameAS PARTITION partition_function_name ALL TO ( file_group_name | PRIMARY ,.n ) ; 参数： partition_scheme_name分区方案的名称。分区方案名称在数据库中必须是唯一的，并且符合标识符规则。 partition_function_name使用分区方案的分区函数的名称。分区函数所创建的分区将映射到在分区方案中指定的文件组。partition_function_name 必
10、须已经存在于数据库中。 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 开始，按文件组在 ,
11、.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 属性。如果在
12、 ALTER PARTITION FUNCTION 语句中创建了一个分区，则 NEXT USED 文件组将再接收一个分区。若要再创建一个未分配的文件组来拥有新的分区，请使用 ALTER PARTITION SCHEME。在 file_group_name 1,.n 中指定主文件组时，必须像在 PRIMARY 中那样分隔 PRIMARY，因为它是关键字。创建分区架构示例：CREATE PARTITION FUNCTION myRangePF1 (int)AS RANGE LEFT FOR VALUES (1, 100, 1000);GOCREATE PARTITION SCHEME myRang
13、ePS1AS PARTITION myRangePF1TO (test1fg, test2fg, test3fg, test4fg);GO2.5. 创建分区表定义了分区函数（逻辑结构）和分区架构（物理结构）后，既可以创建分区表来利用它们。分区表定义应使用的分区架构，而分区架构又定义其使用的分区函数。要将这三者结合起来，必须指定应用于分区函数的列 。范围分区始终只映射到表中的一列。CREATE TABLE 语法如下：CREATE TABLE database_name . schema_name . | schema_name . table_name( | ,.n ) ON partition
14、_scheme_name ( partition_column_name ) | filegroup| “default“ TEXTIMAGE_ON filegroup | “default“ ; 示例如下：CREATE TABLE myRangePT1(ID int not null,AGE int,PRIMARY KEY (ID) ON myRangePS1(myRangePF1)GO2.6. 创建分区索引索引对于提高查询性能非常有效，因此，一般应该考虑应该考虑为分区表建立索引，为分区表建立索引与为普通表建立索引的语法一直，但是，其行为与普通索引有所差异。默认情况下，分区表中创建的索引使用
15、与分区表相同分区架构和分区列，这样，索引将于表对齐。将表与其索引对齐，可以使管理工作更容易进行，对于滑动窗口方案尤其如此。若要启动分区切换，表的所有索引都必须对齐。在创建索引时，也可以指定不同的分区方案（Schema）或单独的文件组（FileGroup）来存储索引，这样 SQL Server 不会将索引与表对齐。在已分区的表上创建索引（分区索引）时，应该注意以下事项： 唯一索引建立唯一索引（聚集或者非聚集）时，分区列必须出现在索引列中。此限制将使 SQL Server只调查单个分区，并确保表中宠物的新键值。如果分区依据列不可能包含在唯一键中，则必须使用 DML 触发器，而不是强制实现唯一性。
16、非唯一索引对非唯一的聚集索引进行分区时，如果未在聚集键中明确指定分区依据列，默认情况下 SQL Server 将在聚集索引列中添加分区依据列。对非唯一的非聚集索引进行分区时，默认情况下 SQL Server 将分区依据列添加为索引的包含性列，以确保索引与基表对齐，若果索引中已经存在分区依据列，SQL Server 将不会像索引中添加分区依据列。3. 分区操作分区适用于可以缩放的大型表，所以随着时间和环境的变化，就会产生对分区的拆分、合并、移动的需求。3.1. 拆分与合并分区通过拆分或合并边界值更改分区函数。通过执行 ALTER PARTITION FUNCTION，可以将使用分区函数的任何表或索引的某个分区拆分为两个分区，也可以将两个分区合并为一个分区。注意：多个表或索引可以使用同一分区函数。ALTER PARTITION FUNCT
