kl800.com省心范文网

sqlserver2005中的分区表(一):什么是分区表?为什么要用分区表?如何创建分

SQL Server 2005 中的分区表(一) :什么是分区表?为什 么要用分区表?如何创建分...

SQL Server 2005 中的分区表(一) :什么是分区表? 为什么要用分区表?如何创建分区表? 如果你的数据库中某一个表中的数据满足以下几个条件, 那么你就要考虑创建分区表了。 1、数据库中某个表中的数据很多。很多是什么概念? 一万条?两万条?还是十万条、一百万条?这个,我觉得是 仁者见仁、智者见智的问题。当然数据表中的数据多到查询 时明显感觉到数据很慢了,那么,你就可以考虑使用分区表 了。如果非要我说一个数值的话,我认为是 100 万条。 2、但是,数据多了并不是创建分区表的惟一条件,哪 怕你有一千万条记录,但是这一千万条记录都是常用的记录, 那么最好也不要使用分区表,说不定会得不偿失。只有你的 数据是分段的数据,那么才要考虑到是否需要使用分区表。 3、什么叫数据是分段的?这个说法虽然很不专业,但 很好理解。比如说,你的数据是以年为分隔的,对于今年的 数据而言,你常进行的操作是添加、修改、删除和查询,而 对于往年的数据而言,你几乎不需要操作,或者你的操作往 往只限于查询, 那么恭喜你, 你可以使用分区表。 换名话说, 你对数据的操作往往只涉及到一部分数据而不是所有数据

的话,那么你就可以考虑什么分区表了。 那么,什么是分区表呢? 简单一点说,分区表就是将一个大表分成若干个小表。 假设,你有一个销售记录表,记录着每个每个商场的销售情 况,那么你就可以把这个销售记录表按时间分成几个小表, 例如说 5 个小表吧。2009 年以前的记录使用一个表,2010 年的记录使用一个表,2011 年的记录使用一个表,2012 年 的记录使用一个表, 2012 年以后的记录使用一个表。 那么, 你想查询哪个年份的记录,就可以去相对应的表里查询,由 于每个表中的记录数少了,查询起来时间自然也会减少。 但将一个大表分成几个小表的处理方式,会给程序员增 加编程上的难度。以添加记录为例,以上 5 个表是独立的 5 个表,在不同时间添加记录的时候,程序员要使用不同的 SQL 语句,例如在 2011 年添加记录时,程序员要将记录添 加到 2011 年那个表里; 在 2012 年添加记录时, 程序员要将 记录添加到 2012 年的那个表里。这样,程序员的工作量会 增加,出错的可能性也会增加。 使用分区表就可以很好的解决以上问题。分区表可以从 物理上将一个大表分成几个小表,但是从逻辑上来看,还是 一个大表。 接着上面的例子,分区表可以将一个销售记录表分成五 个物理上的小表,但是对于程序员而言,他所面对的依然是

一个大表,无论是 2010 年添加记录还是 2012 年添加记录, 对于程序员而言是不需要考虑的,他只要将记录插入到销售 记录表——这个逻辑中的大表里就行了。SQL Server 会自 动地将它放在它应该呆在的那个物理上的小表里。 同样, 对于查询而言, 程序员也只需要设置好查询条件, OK,SQL Server 会自动将去相应的表里查询,不用管太多 事了。 这一切是不是很诱人? 的确,那么我们就可以开始动手创建分区表了。 第一、创建分区表的第一步,先创建数据库文件组,但 这一步可以省略,因为你可以直接使用 PRIMARY 文件。但 我个人认为,为了方便管理,还是可以先创建几个文件组, 这样可以将不同的小表放在不同的文件组里,既便于理解又 可以提高运行速度。创建文件组的方法很简单,打开 SQL Server Management Studio,找到分区表所在数据库,右键 单击,在弹出的菜单里选择“属性”。然后选择“文件组”选项, 再单击下面的“添加”按钮,如下图所示: 第二, 创建了文件组之后, 还要再创建几个数据库文件。 为什么要创建数据库文件,这很好理解,因为分区的小表必 须要放在硬盘上,而放在硬盘上的什么地方呢?当然是文件 里啦。 再说了, 文件组中没有文件, 文件组还要来有啥用呢? 还是在上图的那个界面,选择“文件”选项,然后添加几个文

件。在添加文件的时候要注意以下几点: 1、不要忘记将不同的文件放在文件组中。当然一个文 件组中也可以包含多个不同的文件。 2、如果可以的话,将不同的文件放在不同的硬盘分区 里,最好是放在不同的独立硬盘里。要知道 IQ 的速度往往 是影响 SQL Server 运行速度的重要条件之一。将不同的文 件放在不同的硬盘上,可以加快 SQL Server 的运行速度。 在本例中,为了方便起见,将所有数据库文件都放在了 同一个硬盘下,并且每个文件组中只有一个文件。如下图所 示。 第三、创建一个分区函数。这一步是必须的了,创建分 区函数的目的是告诉 SQL Server 以什么方式对分区表进行 分区。 这一步必须要什么 SQL 脚本来完成。 以上面的例子, 我们要将销售表按时间分成 5 个小表。假设划分的时间为: 第 1 个小表: 2010-1-1 以前的数据 (不包含 2010-1-1) 。 第 2 个小表: 2010-1-1 (包含 2010-1-1) 到 2010-12-31 之间的数据。 第 3 个小表: 2011-1-1 (包含 2011-1-1) 到 2011-12-31 之间的数据。 第 4 个小表: 2012-1-1 (包含 2012-1-1) 到 2012-12-31 之间的数据。 第 5 个小表:2013-1-1(包含 2013-1-1)之后的数据。

那么分区函数的代码如下所示: CREATE PARTITION FUNCTION partfunSale (datetime) AS RANGE RIGHT FOR VALUES ('20100101','20110101','20120101','20130101') 其中: 1、CREATE PARTITION FUNCTION 意思是创建一个 分区函数。 2、partfunSale 为分区函数名称。 3、 AS RANGE RIGHT 为设置分区范围的方式为 Right, 也就是右置方式。 4、FOR VALUES ('20100101','20110101','20120101','20130101')为按这几个 值来分区。 这里需要说明的一下,在 Values 中,'20100101'、 '20110101'、'20120101'、'20130101',这些都是分区的条 件。“ 20100101”代表 2010 年 1 月 1 日,在小于这个值的记 录, 都会分成一个小表中, 如表 1; 而小于或等于'20100101' 并且小于'20110101'的值,会放在另一个表中,如表 2。以 此类推,到最后,所有大小或等于'20130101'的值会放在另 一个表中,如表 5。 也许有人会问, 为什么值“ 20100101”会放在表 2 中, 而 不是表 1 中呢?这是由 AS RANGE RIGHT 中的 RIGHT 所

决定的, RIGHT 的意思是将等于这个值的数据放在右边的那 个表里, 也就是表 2 中。 如果您的 SQL 语句中使用的是 Left 而不是 RIGHT,那么就会放在左边的表中,也就是表 1 中。 第四、创建一个分区方案。分区方案的作用是将分区函 数生成的分区映射到文件组中去。分区函数的作用是告诉 SQL Server,如何将数据进行分区,而分区方案的作用则是 告诉 SQL Server 将已分区的数据放在哪个文件组中。分区 方案的代码如下所示: CREATE PARTITION SCHEME partschSale PARTITION partfunSale Sale2010, Sale2013) 其中: 1、CREATE PARTITION SCHEME 意思是创建一个分 区方案。 2、partschSale 为分区方案名称。 3、AS PARTITION partfunSale 说明该分区方案所使用 的数据划分条件 (也就是所使用的分区函数) 为 partfunSale。 4、TO 后面的内容是指 partfunSale 分区函数划分出来 的数据对应存放的文件组。 到此为止,分区函数和分区方案就创建完毕了。创建后 的分区函数和分区方案在数据库的“存储”中可以看到,如下 TO ( AS

Sale2009, Sale2012,

Sale2011,

图所示: 最后,创建分区表,创建方式和创建普遍表类似,如下 所示: CREATE TABLE Sale( NOT NULL, [Id] [int] IDENTITY(1,1)

[Name] [varchar](16) NOT NULL, ) ON

[SaleTime][datetime] NOT NULL partschSale([SaleTime]) 其中:

1、CREATE TABLE 意思是创建一个数据表。 2、Sale 为数据表名。 3、()中为表中的字段,这里的内容和创建普通数据表没 有什么区别,惟一需要注意的是不能再创建聚集索引了。道 理很简单,聚集索引可以将记录在物理上顺序存储的,而分 区表是将数据分别存储在不同的表中,这两个概念是冲突的, 所以,在创建分区表的时候就不能再创建聚集索引了。 4、ON partschSale()说明使用名为 partschSale 的分区 方案。 5、partschSale()括号中为用于分区条件的字段是 SaleTime。 OK, 一个物理上是分离的, 逻辑上是一体的分区表就创 建完毕了。查看该表的属性,可以看到该表已经属于分区表 了

在创建完分区表后,可以向分区表中直接插入数据,而不用 去管它这些数据放在哪个物理上的数据表中。接上篇文章, 我们在创建好的分区表中插入几条数据: 从以上代码中可以看出, 我们一共在数据表中插入了 13 条数据, 其中第 1 至 3 条数据是插入到第 1 个物理分区表中 的;第 4、5 条数据是插入到第 2 个物理分区表中的;第 6 至 8 条数据是插入到第 3 个物理分区表中的;第 9 至 11 条 数据是插入到第 4 个物理分区表中的;第 12、13 条数据是 插入到第 5 个物理分区表中的。 从 SQL 语句中可以看出,在向分区表中插入数据方法 和在普遍表中插入数据的方法是完全相同的,对于程序员而 言,不需要去理会这 13 条记录研究放在哪个数据表中。当 然,在查询数据时,也可以不用理会数据到底是存放在哪个 物理上的数据表中。如使用以下 SQL 语句进行查询: 查询的结果如下图所示: 从上面两个步骤中,根本就感觉不到数据是分别存放在 几个不同的物理表中,因为在逻辑上,这些数据都属于同一 个数据表。如果你非想知道哪条记录是放在哪个物理上的分 区表中,那么就必须使用到$PARTITION 函数,这个函数的 可以调用分区函数,并返回数据所在物理分区的编号。 说起来有点难懂,不过用起来很简单。$PARTITION 的

语法是: $PARTITION.分区函数名(表达式) 假设, 你想知道 2010 年 10 月 1 日的数据会放在哪个物 理分区表中,你就可以使用以下语句来查看。 select $PARTITION.partfunSale ('2010-10-1') 在以上语句中,partfunSale()为分区函数名,括号中的 表达式必须是日期型的数据或可以隐式转换成日期型的数 据,如果要问我为什么,那么就回想一个怎么定义分区函数 的吧(CREATE PARTITION FUNCTION partfunSale (datetime)) 。在定义 partfunSale()函数时,指定了参数为日 期型,所以括号中的表达式必须是日期型或可以隐式转换成 日期型的数据。以上代码的运行结果如下图所示: 在该图中可以看出,分区函数返回的结果为 2,也就是 说,2010 年 10 月 1 日的数据会放在第 2 个物理分区表中。 再进一步考虑,如果想具体知道每个物理分区表中存放 了哪些记录,也可以使用$PARTITION 函数。因为 $PARTITION 函数可以得到物理分区表的编号,那么只要将 $PARTITION.partfunSale(SaleTime)做为 where 的条件使 用即可,如以下代码所示: select * from Sale where $PARTITION.partfunSale(SaleTime)=1 select * from

Sale where $PARTITION.partfunSale(SaleTime)=2

select * from Sale where $PARTITION.partfunSale(SaleTime)=3 select * from

Sale where $PARTITION.partfunSale(SaleTime)=4 select * from Sale where $PARTITION.partfunSale(SaleTime)=5 以上代码的运行结果如下图所示: 从上图中我们可以看到每个分区表中的数据记录情况 ——和我们插入时设置的情况完全一致。同理可得,如果要 统计每个物理分区表中的记录数,可以使用如下代码: select $PARTITION.partfunSale(SaleTime) as 分区编 号,count(id) as 记录数 from Sale group by $PARTITION.partfunSale(SaleTime) 以上代码的运行结果如下图所示: 除了在插入数据时程序员不需要去考虑分区表的物理 情况之外,就是连修改数据也不需要考虑。SQL Server 会 自动将记录从一个分区表移到另一个分区表中,如以下代码 所示: --统计所有分区表中的记录总数 select

$PARTITION.partfunSale(SaleTime) as 分区编号,count(id) as 记录数 from Sale group by $PARTITION.partfunSale(SaleTime) 记录,将时间改为 2019 年 1 月 1 日 --修改编号为 1 的 update Sale set

SaleTime='2019-1-1' where id=1 中的记录总数 select

--重新统计所有分区表

$PARTITION.partfunSale(SaleTime) as 分区编号,count(id) as 记录数 from Sale group by $PARTITION.partfunSale(SaleTime) 在以上代码中,程序员将其中一条数据的时间改变了, 从分区函数中可以得知,这条记录应该从第一个分区表移到 第五个分区表中,如下图所示。而整个操作过程,程序员是 完全不需要干预的。

在设计数据库时,经常没有考虑到表分区的问题,往往在数 据表承重的负担越来越重时,才会考虑到分区方式,这时, 就涉及到如何将普通表转换成分区表的问题了。 那么,如何将一个普通表转换成一个分区表呢?说到底, 只要将该表创建一个聚集索引,并在聚集索引上使用分区方 案即可。 不过,这回说起来简单,做起来就复杂了一点。还是接 着上面的例子,我们先使用以下 SQL 语句将原有的 Sale 表 删除。 --删除原来的数据表 drop table Sale

然后使用以下 SQL 语句创建一个新的普通表,并在这 个表里插入一些数据。

--新建一个普通的数据表 Sale( --自动增长

CREATE TABLE

[Id] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](16) NOT NULL, CONSTRAINT --创建主键

[SaleTime] [datetime] NOT NULL, [PK_Sale] PRIMARY KEY CLUSTERED ( [Id] ASC ) )

--插入一些记录

insert Sale ([Name],[SaleTime]) values ('张三','2009-1-1') insert Sale ([Name],[SaleTime]) values ('李四','2009-2-1') insert Sale ([Name],[SaleTime]) values ('王五','2009-3-1') insert Sale ([Name],[SaleTime]) values ('钱六','2010-4-1') insert Sale ([Name],[SaleTime]) values ('赵七','2010-5-1') insert Sale ([Name],[SaleTime]) values ('张三','2011-6-1') insert Sale ([Name],[SaleTime]) values ('李四','2011-7-1') insert Sale ([Name],[SaleTime]) values ('王五','2011-8-1') insert Sale ([Name],[SaleTime]) values ('钱六','2012-9-1') insert Sale ([Name],[SaleTime]) values ('赵七','2012-10-1') insert Sale ([Name],[SaleTime]) values ('张三','2012-11-1') insert Sale ([Name],[SaleTime]) values ('李四','2013-12-1') insert Sale ([Name],[SaleTime]) values ('王五','2014-12-1') 使用以上代码创建的表是普通表,我们来看一下表的属 性,如下图所示。 在以上代码中,我们可以看出,这个表拥有一般普通表

的特性——有主键, 同时这个主键还是聚集索引。 前面说过, 分区表是以某个字段为分区条件,所以,除了这个字段以外 的其他字段,是不能创建聚集索引的。因此,要想将普通表 转换成分区表,就必须要先删除聚集索引,然后再创建一个 新的聚集索引,在该聚集索引中使用分区方案。 可惜的是,在 SQL Server 中,如果一个字段既是主键 又是聚集索引时,并不能仅仅删除聚集索引。因此,我们只 能将整个主键删除,然后重新创建一个主键,只是在创建主 键时,不将其设为聚集索引,如以下代码所示: --删掉主键 PK_Sale ALTER TABLE Sale DROP constraint --创建主键,但不设为聚集索引 ALTER

TABLE Sale ADD CONSTRAINT PK_Sale PRIMARY KEY NONCLUSTERED [PRIMARY] 在重新非聚集主键之后,就可以为表创建一个新的聚集 索引,并且在这个聚集索引中使用分区方案,如以下代码所 示: --创建一个新的聚集索引,在该聚集索引中使用分区方案 CREATE CLUSTERED INDEX CT_Sale ON Sale([SaleTime]) ON partschSale([SaleTime]) ( [ID] ASC ) ON

为表创建了一个使用分区方案的聚集索引之后,该表就 变成了一个分区表,查看其属性,如下图所示。

我们可以再一次使用以下代码来看看每个分区表中的 记录数。 --统计所有分区表中的记录总数 select

$PARTITION.partfunSale(SaleTime) as 分区编号,count(id) as 记录数 from Sale group by $PARTITION.partfunSale(SaleTime) 以上代码的运行结果如下所示,说明在将普通表转换成 分区表之后,数据不但没有丢失,而且还自动地放在了它应 在的分区表中了。

在前面我们介绍过如何创建和使用一个分区表,并举了一个 例子,将不 同年份的数据放在不同的物理分区表里。具体 的分区方式为: 第 1 个小表: 2010-1-1 以前的数据 (不包含 2010-1-1) 。 第 2 个小表: 2010-1-1 (包含 2010-1-1) 到 2010-12-31 之间的数据。 第 3 个小表: 2011-1-1 (包含 2011-1-1) 到 2011-12-31 之间的数据。 第 4 个小表: 2012-1-1 (包含 2012-1-1) 到 2012-12-31 之间的数据。 第 5 个小表:2013-1-1(包含 2013-1-1)之后的数据。 分区函数的代码如下所示:

CREATE PARTITION FUNCTION partfunSale (datetime) AS RANGE RIGHT FOR VALUES ('20100101','20110101','20120101','20130101') 假设我们在创建分区表之后发现,2010 年以前的数据 并不多,完全可以将它们与 2010 年的数据进行合并,放在 同一个分区里,也就是说,具体的分区方式改为: 第 1 个小表: 2011-1-1 以前的数据 (不包含 2011-1-1) 。 第 2 个小表: 2011-1-1 (包含 2011-1-1) 到 2011-12-31 之间的数据。 第 3 个小表: 2012-1-1 (包含 2012-1-1) 到 2012-12-31 之间的数据。 第 4 个小表:2013-1-1(包含 2013-1-1)之后的数据。 由于上面的需求更改了数据分区的条件,因此,我们必 须要修改分区函数, 因为分区函数的作用就是要来告诉 SQL Server 怎么存放数据的。 只要分区函数修改了, SQL Server 会自动将数据重新分配,按照新的分区函数指定的方式来存 储数据。 先假设我们还没有创建过分区表,要满足上面的条件, 我们必须要写出如下代码的创建分区函数的 SQL 语句 CREATE PARTITION FUNCTION partfunSale (datetime) AS RANGE RIGHT FOR VALUES ('20110101','20120101','20130101')

比较一个新的分区函数和老的分区函数,看看他们有什 么区别? 的确,我们很容易就可以发现,老的分区函数里多了一 个分界值——也就是'20100101'。 那么, 修改老的分区函数, 事实上就是将这分界值删除。简单一点说,删除(合并)一 个分区,事实上就是在分区函数中将多余的分界值删除。 删除分区函数中的分界值,也就是修改分区函数的方法 如下所示: ALTER PARTITION FUNCTION partfunSale() MERGE RANGE ('20100101') 其中: 1、 ALTER PARTITION FUNCTION 意思是修改分区函 数 2、partfunSale()为分区函数名 3、MERGE RANGE 意思是合并界限。事实上,合并界 限和删除分界值是一个意思。 我们可以在修改分区函数时先统计一下各物理分区中 的记录总数,在修改分区之后,再统计一下各物理分区中的 记录总数,看一下修改分区函数后的数据变化情况,代码如 下所示: --统计所有分区表中的记录总数 select

$PARTITION.partfunSale(SaleTime) as 分区编号,count(id)

as 记录数 from Sale group by $PARTITION.partfunSale(SaleTime) --原来的分区函数

是将 2010-1-1 之前的数据放在第 1 个分区表中, 将 2010-1-1 至 2011-1-1 之间的数据放在第 2 个分区表中 --现在需要

将 2011-1-1 之前的数据都放在第 1 个分区表中,也就是将 第 1 个分区表和第 2 个分区表中的数据合并 数 --修改分区函

ALTER PARTITION FUNCTION partfunSale() --统计所有分区表中的记

MERGE RANGE ('20100101') 录总数

select $PARTITION.partfunSale(SaleTime)

as 分区编号,count(id) as 记录数 from Sale group by $PARTITION.partfunSale(SaleTime) 运行结果如下图所示: 现在还有一个问题,就是通过修改分区函数合并数据之 后,数据都存放在哪里了?在修改之前,数据分别存放在文 件组 Sale2009 和 Sale2010 中, 修改之后, 数据放到哪里去 了呢? 事实上,在修改分区函数之后,SQL Server 也会自动 修改分区方案,将处于两个物理分区中的数据放在同一个物 理分区里了。可以通过查看分区方案的方式来查看数据具体 的存放位置。 查看分区方案的方式为:在 SQL Server Management Studio 中,选择数据库-->存储-->分区方案,右击分区

方案名,在弹出的菜单中选择“编写分区方案脚本 为”-->CREATE 到-->新查询编辑器窗口 然后在新查询编辑器窗口可以看到下图代码。 从上图中可以看出,分区方案将原来 Sale2010 文件组 中的数据合并到了 Sale2009 文件组中。

所谓天下大事,分久必合,合久必分,对于分区表而言也一 样。 前面我们介绍过如何删除 (合并) 分区表中的一个分区, 下面我们介绍一下如何为分区表添加一个分区。 为分区表添加一个分区,这种情况是时常会 发生的。 比如,最初在数据库设计时,只预计了存放 3 年的数据,可 是到了第 4 天怎么办?这样的话,我们就可以为分区表添加 一个分区,让它把新的数据放在新的分区里。再比如,最初 设计时,一个分区用于存放一年的数据,结果在使用的时候 才发现,一年的数据太多,想将一个分区中的数据分为两个 分区来存放。 遇到这种情况,就必须要为分区表添加一个分区了。 当然,我们也可以使用修改分区函数的方式来添加一个 分区,但是在修改分区函数时,我们必须要注意另一个问题 ——分区方案。为什么还要注意分区方案呢?我们回过头来 看一下前面是怎么定义分区函数和分区方案的,如以下代码 所示:

--添加分区函数

CREATE PARTITION FUNCTION AS RANGE RIGHT FOR

partfunSale (datetime)

VALUES ('20100101','20110101','20120101','20130101') --添加分区方案 partschSale ( Sale2009, CREATE PARTITION SCHEME AS PARTITION partfunSale Sale2010, Sale2011, Sale2012, TO

Sale2013) 从以上代码中可以看出,分区函数定义了用于分区的数 据边界,而分区函数指定了符合分区边界的数据存放在文件 组。因此,分区方案中指定的文件组个数应该是比分区函数 中指定的边界数大 1 的。如上例中,分区函数中指定的边界 数为 4,那么在分区方案中指定的文件组数就为 5。 如果,我们将分区函数中的边界数增加一个,那么分区 方案中的文件组数也就要相应地增加一个。因此,我们不能 简简单单地通过修改分区函数的方式来为分区表添加一个 分区。 那么,我们应该怎么做呢?是不是要先为分区方案添加 一个文件组? 这种想法是没有错的,想要为分区表添加一个分区,可 以通过以下两个步骤来实现: 1、为分区方案指定一个可以使用的文件组。 2、修改分区函数。

在为分区方案指定一个可用的文件组时,该分区方案并 没有立刻使用这个文件组,只是将文件组先备用着,等修改 了分区函数之后分区方案才会使用这个文件组(不要忘记了, 如果分区函数没有变,分区方案中的文件组个数就不能变) 。 为分区方案指定一个可用的文件组的代码如下所示: ALTER PARTITION SCHEME partschSale USED [Sale2010] 其中: 1、ALTER PARTITION SCHEME 意思是修改分区方案 2、partschSale 是分区方案名 3、NEXT USED 意思是下一个可使用的文件组 4、[Sale2010]是文件组名 为分区方案添加了下一个可使用的文件组之后,分区方 案并没有立刻使用这个文件组,此时我们可以通过查看分区 方案的源代码来证实。查看方法是:在 SQL Server Management Studio 中,选择数据库-->存储-->分区方 案,右击分区方案名,在弹出的菜单中选择“编写分区方案脚 本为”-->CREATE 到-->新查询编辑器窗口,如下图所 示: 为分区方案添加了下一个可使用的文件组之后,我们就 可以动手修改分区函数了,使用代码如下所示: ALTER PARTITION FUNCTION partfunSale() NEXT

SPLIT RANGE ('20100101') 其中: 1、ALTER PARTITION FUNCTION 意思是修改分区函 数 2、partfunSale()为分区函数名 3、SPLIT RANGE 意思是分割界限 4、'20100101' 是用于分割的界限值 当然,我们在修改分区函数前后都可以统计一下各物理 分区的数据记录情况,如以下代码所示: --统计所有分区表中的记录总数 select

$PARTITION.partfunSale(SaleTime) as 分区编号,count(id) as 记录数 from Sale group by $PARTITION.partfunSale(SaleTime) --原来的分区函数

是将 2010-1-1 之前的数据放在第 1 个分区表中, 将 2010-1-1 至 2011-1-1 之间的数据放在第 2 个分区表中 --现在需要

将 2011-1-1 之前的数据都放在第 1 个分区表中,也就是将 第 1 个分区表和第 2 个分区表中的数据合并 数 --修改分区函

ALTER PARTITION FUNCTION partfunSale() --统计所有分区表中的记录

SPLIT RANGE ('20100101') 总数

select $PARTITION.partfunSale(SaleTime) as

分区编号,count(id) as 记录数 from Sale group by $PARTITION.partfunSale(SaleTime)

以上代码的运行结果如下图所示: 从上图中可以看出,分区表中已经添加了一个分区,我 们也可以再一次查看分区方案的源代码,如下图所示,这个 时候分区方案也自动添加了一个文件组。

我的俄罗斯名叫作“不折腾不舒服斯基”,所以,不将分区表 好好折腾一下,我就是不舒服。 在前面,我们介绍过怎么样直接创建一个分区表,也介 绍过怎么将一个普通表转换成一个分区表。那么,这两种方 式创建的表有什么区别呢?现在,我又最新地创建了两个表: 第一个表名为 Sale,这个表使用的是《》中的方法创建 的,在创建完之后,还为该表添加了一个主键。 第二个表名 Sale1,这个表使用的是《》中的方法创建 的,也就是先创建了一个普通表,然后通过为普通表添加聚 集索引的方式将普通表转换成已分区表的方式。 通过以上方法都可以得到一个已分区表,但是,这两个 已分区表还是有点区别的,区别在哪里呢?我们分别查看一 下这两个表的索引和主键吧,如下图所示。 从上图可以看出,直接创建的分区表 Sale 的索引里, 只有一个名为 PK_Sale 的索引,这个索引是唯一的、非聚集 的索引,也就是在创建 PK_Sale 主键时 SQL Server 自动创 建的索引。而经普通表转换成分区表的 Sale1 的索引里,除

了在创建主键时由 SQL Server 自动创建的名为 PK_Sale1 的唯一的、非聚集的索引之外,还存在一个名为 CT_Sale1 的聚集索引。 对于表 Sale 来说,可以通过修改分区函数的方式来将 其转换成普通表,具体的修改方式请看《SQL Server 2005 中的分区表(四) :删除(合并)一个分区》 ,事实上,就是 将分区函数中的所有分区分界都删除,那么,这个分区表中 的所有数据就只能存在第一个分区表中了。在本例中,可以 使用以下代码来修改分区函数。 ALTER PARTITION FUNCTION partfunSale() MERGE RANGE ('20100101') FUNCTION partfunSale() ('20110101') partfunSale() ALTER PARTITION MERGE RANGE

ALTER PARTITION FUNCTION MERGE RANGE ('20120101')

ALTER PARTITION FUNCTION partfunSale() MERGE RANGE ('20130101') 事实上,这么操作之后,表 Sale 还是一个分区表,如 下图所示,只不过是只有一个分区的分区了,这和普遍表就 没有什么区别了。 对于通过创建分区索引的方法将普通表转换成的分区 表而言,除了上面的方法之外,还可以通过删除分区索引的 办法来将分区表转换成普通表。但必须要经过以下两个步骤:

1、删除分区索引 2、在原来的索引字段上重建一个索引。 先说删除分区索引吧,这一步很简单,你可以直接在 SQL Server Management Studio 上将分区索引删除,也可 以使用 SQL 语句删除,如本例中可以使用以下代码删除已 经创建的分区索引。 一开始,我还以为只要删除了分区索引,那么分区表就 会自动转换成普通表了,可是在删除索引之后,查看一下该 表的属性,结果还是已分区表,如下图所示。 不但如此,而且,还不能将原来的聚集的唯一索引(在 本例中为主键的那个索引)改成聚集索引,如下图所示。 如果要彻底解决这个问题,还必须要在原来创建分区索 引的字段上重新创建一下索引,只有重新创建过索引之后, SQL Server 才能将已分区表转换成普通表。在本例中可以 使用以下代码重新创建索引。 CREATE CLUSTERED INDEX CT_Sale1 ON Sale1([SaleTime]) ON [PRIMARY] Go

重建索引之后,分区表就变成了普通表,现在再查看一 下 Sale1 表的属性,我们可以看到原来的分区表已经变成了 普通表,如下图所示。 当然,以上两个步骤也可以合成一步完成,也就是在重 建索引的同时,将原索引删除。如以下代码所示:

CREATE CLUSTERED INDEX CT_Sale1 ON Sale1([SaleTime]) ON [PRIMARY] 按理说,在 SQL Server Management Studio 中的操作 和使用 SQL 语句的操作是一样的,可是我在 SQL Server Management Studio 中将聚集索引删除后再在该字段上重 新创建一个同名的索引,并重新生成和组织该索引,可是分 区表还是没有变成普通表,这就让我百思不得其解了。不过 呢,只要能用 SQL 语句达到目的,那我们就用它吧。 WITH ( DROP_EXISTING = ON)