为提高大型表的访问速度,完善其可伸缩性和可管理性。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