SQL Server 2005 分区

 

一、SQL Server2005分区

在SQL Server2005中，数据库中的所有表和索引都视为已分区表和索引，即时这些表和索引只包含一个分区。

1.设计分区

（1）在对表或索引进行分区前，必须创建分区函数和分区方案。

（2）分区函数定义如何根据“分区依据列”的值将表或索引的行映射到一组分区。一张表最多可以有1000个分区。

（3）分区方案将把分区函数指定的每个分区映射到文件组。一个分区方案仅可以使用一个分区函数，一个分区函数可以参与多个分区方案。

（4）在创建表或索引的时候指定分区方案。

2.实现分区

（1）创建分区函数

语法：

CREATE PARTITION FUNCTION partition_function_name(input_parameter_type) AS RANGE [LEFT|RIGHT] FOR VALUES([boundary_value[,...n]])[;]

 

实例：

CREATE PARTITION FUNCTION myPartitionFunction(int) AS RANGE LEFT FOR VALUES(1,100,1000);

将按如下规则分为4个区：

分区1	Col1<=1
分区2	Col1>1 && Col1<=100
分区3	Col1>100 && Col1<=1000
分区4	Col1>1000

如果是AS RANGE RIGHT FOR VALUES(1,100,1000)，那么将按如下规则分为4个区：

分区1	Col1<1
分区2	Col1>=1 && Col1<100
分区3	Col1>=100 && Col1<1000
分区4	Col1>=1000

（2）创建分区方案

语法：

CREATE PARTITION SCHEME partition_scheme_name AS PARTION partition_function_name [ALL] TO({file_group_name|[PRIMARY]}[,...n])[;]

 

实例：

CREATE PARTITION SCHEME myPartitonScheme AS PARTITION myPartitionFunction TO (test1fg,test2fg,test3fg,test4fg);

文件组的数量需要与分区的数量一致。

（3）增加分区

ALTER PARTION SCHEME myPartitionScheme NEXT USED[test5fg];

ALTER PARTITION FUNCTION myPartitionFunction() SPLIT RANGE(500);

 

将按照(1,100,500,1000)进行分区。

（4）合并分区

CREATE PARTITION FUNCTION myPartitionFunction(int) AS RANGE LEFT FOR VALUES(1,100,1000);

ALTER PARTITION FUNCTION myPartitionFunction() MERGE RANGE(100);

 

将按照(1,1000)进行分区。

（5）创建分区表

CREATE TABLE PartitionTable(col1 int,col2 char(10)) ON myPartitionScheme(col1);

（6）创建分区索引

在非“分区依据列”上创建索引

Create index IX_col2 ON PartitionTable(col2) ON myPartitionScheme(col1);

在“分区依据列”上创建索引

Create index IX_col1 ON PartitionTable(col1);

3.查询分区信息

（1）使用$PARTITION函数。

（2）访问已分区表的分区子集中的所有行

select * from PartitionTable where $PARTITION.myPartitionFunction(col1)=2

（3）检查每个分区中有多少行

select $PARTITION.myPartitionFunction(col1),count(*) from PartitionTable GROUP BY $PARTITION.myPaititionFunction(col1);

（4）确定包含特定分区键值的行位于哪个分区中

select $PARTITION.myPartitionFunction(10);

二、案例分析

1.第一步

 

2.第二步

3.第三步

4.第四步

5.第五步

6.第六步

 

 

三、其他

1.横向分区和纵向分区

（1）横线分区

将行进行分区，形成多个行集。

比如：表由10列组成，总共100万行记录；分区1由10列组成，有60万行记录；分区2由10列组成，有40万行记录。

 

（2）纵向分区

将列进行分区，形成多个列集。

比如：表由10列组成，总共100万行记录；分区1由6列组成，有100万行记录；分区2由5列（多了一个主键列）组成，有100万行记录。

2.分区视图和可更新的分区视图

分别在7.0和2000版本中提出，具体使用方法和约束在此不做说明。