[转] SQL Server2005 表分区三步曲

 

SQL Server 2005开始支持表分区，这种技术允许所有的表分区都保存在同一台服务器上。每一个表分区都和在某个文件组(filegroup)中的单个文件关联。同样的一个文件/文件组可以容纳多个分区表。在这种设计架构下，数据库引擎能够判定查询过程中应该访问哪个分区，而不用扫描整个表。如果查询需要的数据行分散在多个分区中，SQL Server使用多个处理器对多个分区进行并行查询。你可以为在创建表的时候就定义分区的索引。 对小索引的搜索或者扫描要比扫描整个表或者一张大表上的索引要快很多。因此，当对大表进行查询，表分区可以产生相当大的性能提升

通过分别检查同一条返回所有行的、简单SELECT语句在分区表和非分区表上的执行计划，返回的数据范围通过WHERE语句来指定。同一条语句在这两个不同的表上有不同的执行计划。对于分区表的查询显示出一个嵌套的循环和索引的扫描。从本质上来说，SQL Server将两个分区视为独立的表，因此使用一个嵌套循环将它们连接起来。对非分区的表的同一个查询则使用索引扫描来返回同样的列。当你使用同样的分区策略创建多个表，同时在查询中连接这些表，那么性能上的提升会更加明显

SQL Server数据库表分区操作过程由三个步骤组成：

1. 创建分区函数

2. 创建分区架构

3. 对表进行分区

 

步骤一：创建一个分区函数

   此分区函数用于定义你希望SQL Server如何对数据进行分区的参数值(how)。这个操作并不涉及任何表格，只是单纯的定义了一项技术来分割数据。

use db_Study
go
create partition function partition_fun(datetime)
as
range left for values( '2004-01-01', '2005-01-01')

 

注意:这里调用的"RANGE RIGHT"语句表明每个分区边界值是右界。类似的，如果使用"RANGE LEFT"语句,则上述第一个分区应该包括所有值小于或等于'2004-01-01'数据,以此类推.

 

步骤二：创建一个分区架构

   一旦给出描述如何分割数据的分区函数，接着就要创建一个分区架构，用来定义分区位置(where)。创建过程非常直截了当，只要将分区连接到指定的文件组就行了。

create partition scheme scheme_sch
as partition partition_fun
to([PRIMARY],db_Study_filegroup1,db_Study_filegroup2)

 

注意:这里将一个分区函数连接到了该分区架构，但并没有将分区架构连接到任何数据表。这就是可复用性起作用的地方了。无论有多少数据库表,我们都可以使用该分区架构(或仅仅是分区函数)。

步骤三：对一个表进行分区

   定义好一个分区架构后，就可以着手创建一个分区表了。只需要在表创建指令中添加一个"ON"语句，用来指定分区架构以及应用该架构的表列。因为分区架构已经识别了分区函数，所以不需要再指定分区函数了。

create table tb(ID int identity,dt datetime)
on scheme_sch(dt)
--为此表填充数据

declare @dt datetime
set @dt='2003-01-01'
while @dt<='2005-12-31'
   begin
     insert tb select @dt
     set @dt=dateadd(dd,1,@dt)
   end

 

---- 现在我们可以看一下我们刚才插入的行都分布在哪个Partition

select *,$partition.partition_fun(dt) from tb

--我们可以看一下我们现在tb表的数据存储在哪此partition中,以及在这些分区中数据量的分布

SELECT * FROM SYS.PARTITIONS WHERE OBJECT_ID = OBJECT_ID('tb')
/*
partition_id         object_id   index_id    partition_number hobt_id              rows
-------------------- ----------- ----------- ---------------- -------------------- --------------------
72057594038452224    437576597   0           1                72057594038452224    366
72057594038517760    437576597   0           2                72057594038517760    366
72057594038583296    437576597   0           3                72057594038583296    365

 

(3 個資料列受到影響)
*/

修改分区表的步骤：
1 添加一个文件组到数据库
2 修改分区Scheme
3 修改分区函数

 

--1 添加一个文件组到数据库
alter database db_Study
add filegroup db_Study_filegroup3
go
alter database db_Study
add file
(name='db_study_fg_data4',
filename='D:\study\db_study_fg_data4_1.ndf')
to filegroup db_Study_filegroup3
--2 修改分区Scheme
alter partition scheme scheme_sch
next used db_Study_filegroup3
go
--3 修改分区函数
alter partition function partition_fun()
split range('2006-01-01')

本文来自CSDN博客，出处：http://blog.csdn.net/wufeng4552/archive/2009/10/26/4728248.aspx