SQL2005表分区

为提高大型表的访问速度,完善其可伸缩性和可管理性。MS SQL2005引进了Oracle数据库中的表分区功能（默认情况下，每个 SQL Server 2005 表都进行了分区，即每个表中至少包含一个分区）。

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

 

Ⅰ. 操作如下:

---1. 添加文件组及文件（一般用于独立服务器或有创建子数据库文件、 文件组权限的服务器，其它可跳过）

**根据需要可添加合适数量的分区及设置表分区文件所在磁盘

alter database TEST_DB add filegroup [test_fg1]

alter database TEST_DB add filegroup [test_fg2]

alter database TEST_DB add filegroup [test_fg3]

alter database TEST_DB add file

(name='test_fg1',filename='C:/test_fg1.ndf',maxsize=UNLIMITED,filegrowth=10%) to filegroup test_fg1

alter database TEST_DB add file

(name='test_fg2',filename='C:/test_fg2.ndf',maxsize=UNLIMITED,filegrowth=10%) to filegroup test_fg2

alter database TEST_DB add file

(name='test_fg3',filename='C:/test_fg3.ndf',maxsize=UNLIMITED,filegrowth=10%) to filegroup test_fg3

 

---2. 创建分区函数

**分区函数用于定义分区的边界条件

create partition function customer_partFunc(int)

as range left for values(100,200)

 

range left 和range right 函数的区分：

	分区1	分区2	分区3
left	<= 100	> 100 and <= 200	> 200
right	< 100	>= 100 and < 200	>= 200

 

 

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

CREATE PARTITION SCHEME partition_scheme_name

AS PARTITION partition_function_name

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

[ ; ]

文件组必须已经存在于数据库中, [ PRIMARY ]为默认文件组。

 

create partition scheme customer_scheme

as partition customer_partFunc

to (test_fg1,test_fg2,test_fg3) 

 

---如果（跳过了第一步）没有子文件组及子文件则使用 [PRIMARY]

create partition scheme customer_scheme

as partition customer_partFunc

ALL TO ([PRIMARY])

 

---4. 新建表,并指定分区主字段

Create table test_tab

(

ID int identity(1,1) Primary key,

....

) ON customer_scheme (ID)

 

经过以上四步完成表分区，但有时候我们可能是在开始建表时并没有创建分区,而为了创建表分区我们的操作可以如下:,

--前三步的操作相同,在第四步时执行以下两步操作完成分区

--删除主键

alter table test_tab drop constraint PK_test_tab

 

--进行分区

alter table test_tab with nocheck add

constraint PK_test_tab primary key clustered

(id) on customer_scheme(id) 

 

Ⅱ.查看是否成功及分区后的数据分布:

--各分区的相关信息

SELECT * FROM SYS.PARTITIONS WHERE OBJECT_ID = OBJECT_ID('test_tab')

 

--查询相关数据分布

--ID 为上面用于分区的列名

SELECT $partition.customer_partFunc(ID) AS [Partition Number],

MIN(ID) AS [Min StaffName],

MAX(ID) AS [Max StaffName],

COUNT(ID) AS [Rows In Partition]

FROM dbo.test_tab AS o

GROUP BY $partition.customer_partFunc(ID)

ORDER BY [Partition Number]

 

--查询单个分区

select * from test_tab where $partition.customer_partFunc(ID)=2 

 

 

--其它信息

select * from SYS.PARTITION_SCHEMES   --数据库中所有分区方案的信息，包括对应的分区函数的ID。

select * from SYS.PARTITION_FUNCTIONS  --数据库中所有分区函数的信息，包括分区数等信息。

select * from SYS.PARTITION_RANGE_VALUES --每个分区范围的信息，可以和SYS.PARTITION_FUNCTIONS联查。

select * from SYS.filegroups             --文件组的相关信息

select * from SYS.database_files         --数据库文件的相关信息

 

Ⅲ.  增加、删除、切换分区（Split、Merge、Switch  Partition）

1．  增加分区

--将上面示例中的三个分区增加为四个分区

--指定下一个分区应用文件组.(如:PRIMARY)

ALTER PARTITION SCHEME [customer_scheme]

NEXT USED [PRIMARY]

 

--指定分区分界点为

ALTER PARTITION FUNCTION customer_partFunc()

SPLIT RANGE (300)

         

分区1	分区2	分区3	分区4
<= 100	> 100 and <= 200	>200 and <=300	>300

 

2．  删除分区

--删除上面增加的一个分区

--指定合并后分区应用文件组

ALTER PARTITION SCHEME [MyPartitionSchema]

NEXT USED [used test_fg3]

 

--指定分区分界点为

ALTER PARTITION FUNCTION MyPartitionFunction ()

MERGE RANGE (300)

         

分区1	分区2	分区3
<= 100	> 100 and <= 200	>200

 

3．  切换分区(移入，移出)

SQL Server 2005 分区表分区切换的三种形式(前提条件有: 1. 普通表必须建立在分区表切换分区所在的文件组上。 2. 普通表的表结构跟分区表的一致； 3. 普通表上的索引要跟分区表一致。 4. 普通表必须是空表，不能有任何数据。)：

 

3.1 切换分区表的一个分区到普通数据表中

     ALTER TABLE [test_tab] SWITCH PARTITION 1 TO [new_test_tab]

 

3.2切换普通表数据到分区表的一个分区中

     ALTER TABLE [new_test_tab] SWITCH TO [test_tab] PARTITION 1

 

 

3.3 切换分区表的分区到另一分区表

     ALTER TABLE [test_tab] SWITCH PARTITION 1 TO [new_test_tab] PARTITION 1