Big Ocean In Columbus: (ZT)SQL Server 表分区实战系列（文章索引）

From http://www.cnblogs.com/gaizai/archive/2011/05/31/2064931.html

SQL Server 表分区实战系列（文章索引）

前段时间在忙数据库的表分区，经常会去上网找资料，但是在找到都是测试表分区的文章，没有实战经验的，所以在我把表分区运用到实际项目中的时候遇到了很多问题。

比如：如何确认分区字段？分区字段与聚集索引的区别与联系？如何存储分区索引？MSDN说交换分区是以秒计算，但执行40G交换分区超时？如何解决分区不断增长的问题？自动化交换分区的陷阱？

这些问题都只能自己在实战中摸索答案，后来我写了几篇关于这些问题的博文，希望对那些需要实战帮助的童鞋有一点提示和帮助。希望大家拍砖。

实战说明

某生产数据库大小已经有800G了，每天进库数据量大概有150W条记录（数据空间大概为7G），而服务器现在已经没有太多的磁盘空间了，面对这样的问题，我决定对这个数据库的一个大表做表分区，每个分区的ndf文件为40G，一个分区存储1千万条记录。总的记录数保持在1.2亿的数据量。

当需要新的空间来存储新的数据的时候，我们就通过交换表分区来快速删除一个分区的数据，并使用这个分区来存放新进库的数据。

如果每次都人工来执行交换分区的话就太麻烦了，所以我对这个如何进行交换分区删除数据来清理磁盘空间做成自动化。

在执行自动化的作业却也发现了很多问题，在分区文件达到40G的情况，执行交换分区的时间会很长（在正常进数据的情况下执行作业），而对8G的数据文件进行交换分区时速度非常快，只能先禁用掉MSSQLSERVER网络协议中的TCP/IP的协议；重启SQLServer服务；执行Job进行交换分区；

系列文章索引

Step1：SQL Server 合并（删除）分区解惑

Step2：SQL Server 2005 分区模板与实例

Step3：SQL Server 动态生成分区脚本

Step4：SQL Server 2005 自动化删除表分区设计方案

Step5：SQL Server 表分区注意事项

总结

我会继续了解和深入表分区的一些知识，并继续写一些关于表分区实际应用的文章。但是一个人的能力和视野是比较有限，所以大家如果有更好的分区实战经验的话，欢迎拿出来分享和交流。看到一些好的博文也欢迎把地址贴出来。

SQL Server 合并（删除）分区解惑

一、准备

在SQL Server 2005版本之后就有了表分区的概念与应用，在分区操作里面有一个叫做合并分区的功能，也被称为删除分区。分区所处的文件组和文件是不会被删除的，只会对数据进行转移合并。合并分区时需要注意所带来的IO问题。

合并分区常见情景：发现某个分区的数据很少，为了方便管理可以考虑合并分区。需要进行统计、四则运算的时候也可以考虑合并分区，这种情形下并没有对比合并与分区之间的性能，如果某位童鞋有兴趣和环境的话可以提供这方面的数据。

查看分区方案的方式：在SQL Server Management Studio中，选择数据库-->存储-->分区方案，右击分区方案名，在弹出的菜单中选择“编写分区方案脚本为”-->CREATE到-->新查询编辑器窗口。

二、测试

(一) 环境：使用RANGE RIGHT作为分区函数的关键字，它表示这个值开始是属于右边界的。

RANGE RIGHT FOR VALUES ('20100101','20110101','20120101','20130101')

文件组	分区	取值范围
[FG1]	1	[过去某年, 2010/01/01)
[FG2]	2	[2010/01/01, 2011/01/01)
[FG3]	3	[2011/01/01, 2012/01/01)
[FG4]	4	[2012/01/01, 2013/01/01)
[PRIMARY]	5	[2013/01/01, 未来某年)

1. 测试一：删除分区函数的第一个值

当使用MERGE RANGE ('20100101')，合并后分区就相当于：

RANGE RIGHT FOR VALUES ('20110101','20120101','20130101')

文件组	分区	取值范围
[FG?]	1	[过去某年, 2011/01/01)
[FG3]	2	[2011/01/01, 2012/01/01)
[FG4]	3	[2012/01/01, 2013/01/01)
[PRIMARY]	4	[2013/01/01, 未来某年)

问题：是[FG1]合并到[FG2]，还是[FG2]合并到[FG1]呢？

测试结果：[FG2]合并到[FG1]

2. 测试二：删除分区函数的第二个值

当使用MERGE RANGE ('20110101')，合并后分区就相当于：

RANGE RIGHT FOR VALUES ('20100101','20120101','20130101')

文件组	分区	取值范围
[FG1]	1	[过去某年, 2010/01/01)
[FG?]	2	[2010/01/01, 2012/01/01)
[FG4]	3	[2012/01/01, 2013/01/01)
[PRIMARY]	4	[2013/01/01, 未来某年)

问题：是[FG2]合并到[FG3]，还是[FG3]合并到[FG2]呢？

测试结果：[FG3]合并到[FG2]

3. 测试三：删除分区函数的最后一个值

当使用MERGE RANGE ('20130101')，合并后分区就相当于：

RANGE RIGHT FOR VALUES ('20100101','20110101','20120101')

文件组	分区	取值范围
[FG1]	1	[过去某年, 2010/01/01)
[FG2]	2	[2010/01/01, 2011/01/01)
[FG3]	3	[2011/01/01, 2012/01/01)
[?]	4	[2012/01/01, 未来某年)

问题：是[FG4]合并到[PRIMARY]，还是[PRIMARY]合并到[FG4]呢？

测试结果：[PRIMARY]合并到[FG4]

(二) 环境：使用RANGE LEFT作为分区函数的关键字，它表示这个值开始是属于左边界的。

RANGE LEFT FOR VALUES ('20100101','20110101','20120101','20130101')

文件组	分区	取值范围
[FG1]	1	(过去某年, 2010/01/01]
[FG2]	2	(2010/01/01, 2011/01/01]
[FG3]	3	(2011/01/01, 2012/01/01]
[FG4]	4	(2012/01/01, 2013/01/01]
[PRIMARY]	5	(2013/01/01, 未来某年]

1. 测试四：删除分区函数的第一个值

当使用MERGE RANGE ('20100101')，合并后分区就相当于：

RANGE LEFT FOR VALUES ('20110101','20120101','20130101')

文件组	分区	取值范围
[FG?]	1	(过去某年, 2011/01/01]
[FG3]	2	(2011/01/01, 2012/01/01]
[FG4]	3	(2012/01/01, 2013/01/01]
[PRIMARY]	4	(2013/01/01, 未来某年]

问题：是[FG1]合并到[FG2]，还是[FG2]合并到[FG1]呢？

测试结果：[FG1]合并到[FG2]

2. 测试五：删除分区函数的第二个值

当使用MERGE RANGE ('20110101')，合并后分区就相当于：

RANGE LEFT FOR VALUES ('20100101','20120101','20130101')

文件组	分区	取值范围
[FG1]	1	(过去某年, 2010/01/01]
[FG?]	2	(2010/01/01, 2012/01/01]
[FG4]	3	(2012/01/01, 2013/01/01]
[PRIMARY]	4	(2013/01/01, 未来某年]

问题：是[FG2]合并到[FG3]，还是[FG3]合并到[FG2]呢？

测试结果：[FG2]合并到[FG3]

3. 测试六：删除分区函数的最后一个值

当使用MERGE RANGE ('20130101')，合并后分区就相当于：

RANGE LEFT FOR VALUES ('20100101','20110101','20120101')

文件组	分区	取值范围
[FG1]	1	(过去某年, 2010/01/01]
[FG2]	2	(2010/01/01, 2011/01/01]
[FG3]	3	(2011/01/01, 2012/01/01]
[?]	4	(2012/01/01, 未来某年]

问题：是[FG4]合并到[PRIMARY]，还是[PRIMARY]合并到[FG4]呢？

测试结果：[FG4]合并到[PRIMARY]

三、结论

下图中形象的表述了边界值（boundary_value）所处的分区，符号“()”表示不包含，“[]”表示包含，一个圆圈带叉图标表示需要删除这个边界值，当使用MERGE RANGE的时候，数据库就需要删除一个分区的数据，并把数据合并到另外一个分区中。你能从下图看出结论吗？

（图1：分区函数为右边界）

（图2：分区函数为左边界）

结论：删除的这个边界值（boundary_value）属于哪个分区，那么就会删除这个分区，再向邻近的分区合并。邻近的意思是以这个边界值为临界点的两个分区。图1与图2就很好的解释了这个结论。

SQL Server 2005 分区模板与实例

一、场景

这一段时间使用SQL Server 2005 对几个系统进行表分区，这几个系统都有一些特点，比如数据库某张表持续增长，给数据库带来了很大的压力。

现在假如提供一台新的服务器，那么我们应该如何规划这个数据库呢？应该如何进行最小宕机时间的数据库转移呢？如果规划数据库呢？

二、环境准备

要搭建一个好的系统，首先要从硬件和操作系统出发，好的设置和好的规划是高性能的前提，下面我就来说说自己的一些看法，欢迎大家提出异议；

1) 对磁盘做RAID0（比如3*300G），必要时可以考虑RAID5、RAID10；

2) 使用两张千兆网卡，一张用于外网，一张用于内网（这也需要千兆路由器的配合）；

3) 逻辑分区C为系统分区（50G），逻辑分区D为程序安装分区（50G），逻辑分区E为数据库文件逻辑分区；

4) 安装Microsoft Windows Server 2003, Enterprise Edition SP2（x64）操作系统；

5) D盘格式化的时候使用默认分配单元大小，E盘格式为64k分配单元；

6) 安装Microsoft SQL Server 2005（x64）数据库；

7) 在我们网上邻居-本地连接-属性-Microsoft网络的文件和打印机共享-最大化网络应用程序数据吞吐量（勾选上）；

8) 运行-gpedit.msc-Windows设置-安全设置-本地策略-用户权限分配-内存中锁定页面-设置用户组（比如Administrators）；

9) 运行-services.msc，设置启动类型为手动，并且停止除了SQL Server (MSSQLSERVER)之外的SQL Server服务，除非你对某些服务需要启动，比如作业、全文索引；

10) 设置虚拟内存大小，我通常设置为4096MB-8192MB；

三、前期工作

在进行分区之前，我们首先要分析这个表的数据量（行数）有多少？这个表的存储空间（物理存储）有多少？需要确定分区文件多大为合理？还需要确认我们按照表中哪个字段进行分区？后期的维护是否需要对分区进行管理（比如交换分区进行数据归档等）？

假设我们决定以自增ID作为分区字段（其实应该叫分区数值类型），我们就可以使用上面的行数和存储空间来计算我们的分区边界值了，因为我们确认了分区文件的大小。比如我们表A记录为：1.5亿，占用空间为：700G，如果我们可以接受的文件大小为10G（这个要根据如果需要做交换分区和一些存储空间、硬盘等信息确认的），那么我们的分区值可以这样计算：1.5亿/（700G/10G）≈200W，也就是：200W，400W，600W等等；

分区文件在创建的时候就应该初始化为包含分区边界值数据大小，比如上面的分区文件可以设置为10G，这样就不用重新分配空间了。也可以使用定量增长，比如2048MB。

在设置自增ID为分区字段，那么通常我们会让ID成为聚集索引，而且设置填充因子为100%，这样我们的数据页就不会有空白了。

如果后期的维护需要对分区进行管理，比如交换分区进行数据归档，交换分区是需要索引对齐的，而索引对齐有两种：索引对齐；按存储位置对齐的表。

索引对齐：假如你想让数据与索引分开到不同的文件，可以使用两个不同的分区方案，但是使用同一个分区函数，这样就把索引分开了。（如图1）

存储位置对齐：创建非聚集索引的时候设置【数据空间规范】，两个索引对象可以使用相同的分区架构，并且具有相同分区键的所有数据行最后将位于同一个文件组中。这就叫存储位置对齐。（数据和索引在同一个文件中）（如图2）

（图1）

（图2）

四、分区步骤

下面提供了创建分区的代码，其中包括模板还有例子（Ext），这里最主要是注意一些命名规范，希望对大家有用：

步骤1：为MyDataBase数据库创建2个文件组，如果你不想用PRIMARY作为分区，你可以创建多一个文件组，文件组=分区值个数+1；

--1.创建文件组

ALTER DATABASE [数据库名]

ADD FILEGROUP [FG_表名_字段名_分区编号]

--Ext

ALTER DATABASE [MyDataBase]

ADD FILEGROUP [FG_User_Id_1]

ALTER DATABASE [MyDataBase]

ADD FILEGROUP [FG_User_Id_2]

步骤2：为MyDataBase数据库创建2个文件，文件数>=文件组数，一个文件不能属于两个不同的分组中，一个分组可以包含多个文件，注意初始化大小（根据需求）和增长大小（百分比和字节数）；

--2.创建文件

ALTER DATABASE [数据库名]

ADD FILE

(NAME = N'FG_表名_字段名_分区编号_data',FILENAME = N'E:\DataBase\FG_表名_字段名_分区编号_data.ndf',SIZE = 30MB, FILEGROWTH = 10% )

TO FILEGROUP [FG_表名_字段名_分区编号];

ALTER DATABASE [数据库名]

ADD FILE

(NAME = N'FG_表名_字段名_分区编号_data',FILENAME = N'E:\DataBase\FG_表名_字段名_分区编号_data.ndf',SIZE = 30720KB , FILEGROWTH = 10240KB )

TO FILEGROUP [FG_表名_字段名_分区编号];

--Ext

ALTER DATABASE [MyDataBase]

ADD FILE

(NAME = N'FG_User_Id_1_data',FILENAME = N'E:\DataBase\FG_User_Id_1_data.ndf',SIZE = 30MB, FILEGROWTH = 10% )

TO FILEGROUP [FG_User_Id_1];

ALTER DATABASE [MyDataBase]

ADD FILE

(NAME = N'FG_User_Id_2_data',FILENAME = N'E:\DataBase\FG_User_Id_2_data.ndf',SIZE = 30MB , FILEGROWTH = 10MB )

TO FILEGROUP [FG_User_Id_2];

步骤3：为MyDataBase数据库创建分区函数，分区值需要根据需求而变化，前面已经做了示范了，这里使用了右分区，关于边界值的理解可以参考：SQL Server 合并（删除）分区解惑；

--3.创建分区函数

CREATE PARTITION FUNCTION

Fun_表名_字段名(数据类型) AS

RANGE RIGHT

FOR VALUES(边界值列表)

--Ext

CREATE PARTITION FUNCTION

Fun_User_Id(INT) AS

RANGE RIGHT

FOR VALUES(100000000,200000000)

步骤4：为MyDataBase数据库创建分区方案，因为前面只创建了2个文件组，所以这里使用了PRIMARY默认的文件组来保存边界值之外的数据，如果你想创建多一个文件组也可以，如下面的Ext1与Ext2；

--4.创建分区方案

CREATE PARTITION SCHEME

Sch_表名_字段名AS

PARTITION Fun_表名_字段名

TO(文件组列表)

--Ext1

CREATE PARTITION SCHEME

Sch_User_Id AS

PARTITION Fun_User_Id

TO([FG_User_Id_1],[FG_User_Id_2],[FG_User_Id_3])

--Ext2

CREATE PARTITION SCHEME

Sch_User_Id AS

PARTITION Fun_User_Id

TO([FG_User_Id_1],[FG_User_Id_2],[PRIMARY])

步骤5：为MyDataBase数据库创建一个名为User的表，这个表有3个字段，Id是自增标识，并在Id字段中创建聚集索引，填充因子为100%，使用上面创建的Sch_User_Id分区方案，创建有不同的创建方式，如Ext1、Ext2、Ext3；

--5.创建表

--Ext1

CREATE TABLE [dbo].[User](

[Id] [int] IDENTITY(1,1) NOT NULL,

[UserName] [nvarchar](256) COLLATE Chinese_PRC_CI_AS NULL,

[Age] [int] NULL CONSTRAINT [DF_User_Age] DEFAULT ((0)),

CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED

(

[Id] ASC

)WITH( PAD_INDEX = ON, FILLFACTOR = 100, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Sch_User_Id](Id)

) ON [Sch_User_Id]([Id])

--Ext2

CREATE TABLE [dbo].[User](

[Id] [int] IDENTITY(1,1) NOT NULL,

[UserName] [nvarchar](256) COLLATE Chinese_PRC_CI_AS NULL,

[Age] [int] NULL CONSTRAINT [DF_User_Age] DEFAULT ((0)),

) ON [Sch_User_Id]([Id])

CREATE CLUSTERED INDEX [IX_User_Id] ON dbo.[User]

(

[Id]

) WITH( PAD_INDEX = ON, FILLFACTOR = 100, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Sch_User_Id](Id)

--Ext3

ALTER TABLE dbo.[User] ADD CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED

(

) WITH( PAD_INDEX = ON, FILLFACTOR = 100, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Sch_User_Id](Id)

步骤6：为User表创建测试数据，这里我就模拟从一个存在的OldUser表中导入数据到分区User表，这里需要注意SET IDENTITY_INSERT 表ON 这个选项；

--6.导入数据

SET IDENTITY_INSERT 表ON

INSERT INTO dbo.表

( [Id]

,[UserName]

,[Age])

SELECT

[Id]

,[UserName]

,[Age]

FROM dbo.[OldUser](nolock) WHERE 条件

SET IDENTITY_INSERT 表OFF

--Ext

SET IDENTITY_INSERT [User] ON

INSERT INTO dbo.[User]

( [Id]

,[UserName]

,[Age])

SELECT

[Id]

,[UserName]

,[Age]

FROM dbo.[OldUser](nolock) WHERE Id <= 1 and Id > 100000000

SET IDENTITY_INSERT [User] OFF

步骤7：当需要查询分区User表记录所处的分区情况时，可以使用下面的SQL；

--7.分区函数的记录数

SELECT $PARTITION.分区函数(字段) AS Partition_num,

MIN(Id) AS Min_value,MAX(Id) AS Max_value,COUNT(1) AS Record_num

FROM dbo.[User]

GROUP BY $PARTITION.分区函数(字段)

ORDER BY $PARTITION.分区函数(字段);

--Ext

SELECT $PARTITION.Fun_User_Id(Id) AS Partition_num,

MIN(Id) AS Min_value,MAX(Id) AS Max_value,COUNT(1) AS Record_num

FROM dbo.[User]

GROUP BY $PARTITION.Fun_User_Id(Id)

ORDER BY $PARTITION.Fun_User_Id(Id);

步骤8：其实到这里实例应该结束了吧？在网上看到的所有关于分区的文章中貌似都是在这里结束了，但是还有一点我需要指出：如果创建存储位置对齐的索引呢？也许通过上面的图2你已经了解了什么是存储位置对齐，如果还不清楚可以查看：SQL Server 2005 中的分区表和索引，其实很简单，如Ext所示，但是主要是理解它的原理和作用；

--8.创建非聚集索引

CREATE NONCLUSTERED INDEX IX_表_字段ON dbo.表

(

字段

) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Sch_User_Id]([Id])

--Ext

CREATE NONCLUSTERED INDEX IX_User_UserName ON dbo.[User]

(

UserName

) WITH( PAD_INDEX = ON, FILLFACTOR = 80, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Sch_User_Id]([Id])

步骤9：还不想结束？呵呵，这个包含性索引的创建就当是买8送1吧；

--9.创建包含性索引

CREATE NONCLUSTERED INDEX [IX_User_UA_Include] ON dbo.[User]

(

UserName,

Age

)

INCLUDE ([Id])

WITH( PAD_INDEX = ON, FILLFACTOR = 80, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Sch_User_Id]([Id])

五、注意

上面的代码中我们把文件与文件组是一一对应起来的，如果我们想更小话文件的话，我们可以在文件组下面创建多个文件，并且设置文件的最大值（MAXSIZE），这样就会把数据分配到不同的物理文件上，但是有一点需要注意，那就是它是一个个的使用文件的，当一个用完了才会使用下一个的。

日志文件也可以像上面的做法来做，这样收缩日志的时候比较方便？删除日志文件比较方便？

有一点我们可能会混淆，那就是既然可以在一个文件组里面创建多个文件，那么这个跟我们按照Id的自增来分布数据是不是等效的？这是有不同的，因为从创建分区方案的时候我们就发现文件组和分区边界值是对应的，所以一段分区值这些数据是分配到以文件组为单位的存储单元中，并不是文件。

补充一下，那就是在文件组下面创建的文件只能按照设置的最大值（MAXSIZE）来区分数据，并不能按照值来区分，这也算一个不同点吧。

六、后记

如果这些表是写的多，读的少：类似记录日志，我们还有一些方案可以进行处理，比如SQL Server 2008的行压缩、页压缩等；比如MySQL的IASM数据引擎；或者是使用MySQL的master/slave负载均衡。

七、参考文献

SQL Server 2005 中的分区表和索引

SQL Server 动态生成分区脚本

一、前言

前段时间使用表分区比较多，虽然已经写了SQL Server 合并（删除）分区解惑、SQL Server 2005 分区模板与实例，但是在实践中一直感觉修改SQL脚本的时间比较多，一直想抽个时间来把分区脚本进行动态化，今天终于付之于行动了。需要说明的一点，下面的脚本并不能满足所有情况，用户可以根据自己的需要进行相应的调整，应该可以满足你的需求的。

在SQL Server 2005中只能通过SQL脚本来创建表分区，而在SQL Server 2008的SSMS中已经提供了操作界面进行表分区，但是也不能把操作生成SQL脚本，所以，下面的SQL的应用场景包括SQL Server 2005、SQL Server 2008。

二、分解

下面就是生成分区的脚本了，在执行之前，你需要填写数据库名称、表名、分区表字段、需要的分区数、保存分区文件的路径、分区初始化大小、分区文件的增量、分区边界值；

这里的分区边界值是按照int类型进行增量计算的，比如你想以每100W进行范围分区的话，那你只要设置@FunValue为100W；如果你的分区边界值是其它类型值或者是不等范围的分区，那么你只要修改这个变量为字符串，并对分区函数的生成代码进行相应修改就可以满足你的需求了。

通常情况下，我们会以一个表Id(int)，并且是自增作为分区字段，这样就很容易区分历史数据了，而且对分区的操作隔离也是最明显的。

--生成分区脚本

DECLARE @DataBaseName NVARCHAR(50)--数据库名称

DECLARE @TableName NVARCHAR(50)--表名称

DECLARE @ColumnName NVARCHAR(50)--字段名称

DECLARE @PartNumber INT--需要分多少个区

DECLARE @Location NVARCHAR(50)--保存分区文件的路径

DECLARE @Size NVARCHAR(50)--分区初始化大小

DECLARE @FileGrowth NVARCHAR(50)--分区文件增量

DECLARE @FunValue INT--分区分段值

DECLARE @i INT

DECLARE @PartNumberStr NVARCHAR(50)

DECLARE @sql NVARCHAR(max)

--设置下面变量

SET @DataBaseName = 'MyDataBase'

SET @TableName = 'User'

SET @ColumnName = 'Id'

SET @PartNumber = 4

SET @Location = 'E:\DataBase\'

SET @Size = '30MB'

SET @FileGrowth = '10%'

SET @FunValue = 10000000

--1.创建文件组

SET @i = 1

PRINT '--1.创建文件组'

WHILE @i <= @PartNumber

BEGIN

SET @PartNumberStr = RIGHT('0' + CONVERT(NVARCHAR,@i),2)

SET @sql = 'ALTER DATABASE ['+@DataBaseName +']

ADD FILEGROUP [FG_'+@TableName+'_'+@ColumnName+'_'+@PartNumberStr+']'

PRINT @sql + CHAR(13)

SET @i=@i+1

END

--2.创建文件

SET @i = 1

PRINT CHAR(13)+'--2.创建文件'

WHILE @i <= @PartNumber

BEGIN

SET @PartNumberStr = RIGHT('0' + CONVERT(NVARCHAR,@i),2)

SET @sql = 'ALTER DATABASE ['+@DataBaseName +']

ADD FILE

(NAME = N''FG_'+@TableName+'_'+@ColumnName+'_'+@PartNumberStr+'_data'',FILENAME = N'''+@Location+'FG_'+@TableName+'_'+@ColumnName+'_'+@PartNumberStr+'_data.ndf'',SIZE = '+@Size+', FILEGROWTH = '+@FileGrowth+' )

TO FILEGROUP [FG_'+@TableName+'_'+@ColumnName+'_'+@PartNumberStr+'];'

PRINT @sql + CHAR(13)

SET @i=@i+1

END

--3.创建分区函数

PRINT CHAR(13)+'--3.创建分区函数'

DECLARE @FunValueStr NVARCHAR(MAX)

SET @i = 1

SET @FunValueStr = ''

WHILE @i < @PartNumber

BEGIN

SET @FunValueStr = @FunValueStr + convert(NVARCHAR(50),(@i*@FunValue)) + ','

SET @i=@i+1

END

SET @FunValueStr = substring(@FunValueStr,1,len(@FunValueStr)-1)

SET @sql = 'CREATE PARTITION FUNCTION

Fun_'+@TableName+'_'+@ColumnName+'(INT) AS

RANGE RIGHT

FOR VALUES('+@FunValueStr+')'

PRINT @sql + CHAR(13)

--4.创建分区方案

PRINT CHAR(13)+'--4.创建分区方案'

DECLARE @FileGroupStr NVARCHAR(MAX)

SET @i = 1

SET @FileGroupStr = ''

WHILE @i <= @PartNumber

BEGIN

SET @PartNumberStr = RIGHT('0' + CONVERT(NVARCHAR,@i),2)

SET @FileGroupStr = @FileGroupStr + '[FG_'+@TableName+'_'+@ColumnName+'_'+@PartNumberStr+'],'

SET @i=@i+1

END

SET @FileGroupStr = substring(@FileGroupStr,1,len(@FileGroupStr)-1)

SET @sql = 'CREATE PARTITION SCHEME

Sch_'+@TableName+'_'+@ColumnName+' AS

PARTITION Fun_'+@TableName+'_'+@ColumnName+'

TO('+@FileGroupStr+')'

PRINT @sql + CHAR(13)

--5.分区函数的记录数

PRINT CHAR(13)+'--5.分区函数的记录数'

SET @sql = 'SELECT $PARTITION.Fun_'+@TableName+'_'+@ColumnName+'('+@ColumnName+') AS Partition_num,

MIN('+@ColumnName+') AS Min_value,MAX('+@ColumnName+') AS Max_value,COUNT(1) AS Record_num

FROM dbo.'+@TableName+'

GROUP BY $PARTITION.Fun_'+@TableName+'_'+@ColumnName+'('+@ColumnName+')

ORDER BY $PARTITION.Fun_'+@TableName+'_'+@ColumnName+'('+@ColumnName+');'

PRINT @sql + CHAR(13)

生成的脚本如下：

--1.创建文件组

ALTER DATABASE [MyDataBase]

ADD FILEGROUP [FG_User_Id_01]

ALTER DATABASE [MyDataBase]

ADD FILEGROUP [FG_User_Id_02]

ALTER DATABASE [MyDataBase]

ADD FILEGROUP [FG_User_Id_03]

ALTER DATABASE [MyDataBase]

ADD FILEGROUP [FG_User_Id_04]

--2.创建文件

ALTER DATABASE [MyDataBase]

ADD FILE

(NAME = N'FG_User_Id_01_data',FILENAME = N'E:\DataBase\FG_User_Id_01_data.ndf',SIZE = 30MB, FILEGROWTH = 10% )

TO FILEGROUP [FG_User_Id_01];

ALTER DATABASE [MyDataBase]

ADD FILE

(NAME = N'FG_User_Id_02_data',FILENAME = N'E:\DataBase\FG_User_Id_02_data.ndf',SIZE = 30MB, FILEGROWTH = 10% )

TO FILEGROUP [FG_User_Id_02];

ALTER DATABASE [MyDataBase]

ADD FILE

(NAME = N'FG_User_Id_03_data',FILENAME = N'E:\DataBase\FG_User_Id_03_data.ndf',SIZE = 30MB, FILEGROWTH = 10% )

TO FILEGROUP [FG_User_Id_03];

ALTER DATABASE [MyDataBase]

ADD FILE

(NAME = N'FG_User_Id_04_data',FILENAME = N'E:\DataBase\FG_User_Id_04_data.ndf',SIZE = 30MB, FILEGROWTH = 10% )

TO FILEGROUP [FG_User_Id_04];

--3.创建分区函数

CREATE PARTITION FUNCTION

Fun_User_Id(INT) AS

RANGE RIGHT

FOR VALUES(10000000,20000000,30000000)

--4.创建分区方案

CREATE PARTITION SCHEME

Sch_User_Id AS

PARTITION Fun_User_Id

TO([FG_User_Id_01],[FG_User_Id_02],[FG_User_Id_03],[FG_User_Id_04])

--5.分区函数的记录数

SELECT $PARTITION.Fun_User_Id(Id) AS Partition_num,

MIN(Id) AS Min_value,MAX(Id) AS Max_value,COUNT(1) AS Record_num

FROM dbo.User

GROUP BY $PARTITION.Fun_User_Id(Id)

ORDER BY $PARTITION.Fun_User_Id(Id);

三、后记

在MSND的SQL Server 2005 中的分区表和索引中同样提供了一个脚本用于生成表分区，和他不同的是：他采用了表来保存文件路径，再使用游标来创建文件而已，其实这只能生成一部分代码，而我的脚本不同之处就是能最大限度的生成常规表分区的常用代码，方便快捷很多。有兴趣的童鞋可以下载：SQL2005PartitioningScripts.exe

上面使用表保存信息的这种想法在后期的数据搬迁（比如需要对一个现有的表进行表分区，这种情况下通常会先建一个分区表，再进行导入现有表的数据）中是有很大的用处的，后面的文章中会讲讲如何进行自动化的数据搬迁，敬请留意。

SQL Server 2005 自动化删除表分区设计方案

一、目的

在前面的文章中我已经介绍了SQL Server 合并（删除）分区解惑 和SQL Server 2005 分区模板与实例 和SQL Server 动态生成分区脚本，这篇文章就是在上面3篇文章衍生出来的。

我们的服务器的数据已经有了800G，并且每天进数据大概有120W条记录（数据空间大概为7G），而服务器现在已经没有太多的磁盘空间了，面对这样的问题，一般都是使用交换表分区来快速删除数据，并使用之前的分区来存放新进的数据，如果每次都人工的话就太麻烦了，所以我对这个如何进行交换分区删除数据来清理磁盘空间做成了自动化。希望大家拍砖。

二、分析与设计思路

分区特点：分区使用了自增ID作为分区字段；分区的索引进行存储位置对齐；

设计步骤1：表分区已经确定了各个分区值，我们就用一个表保存可能存在的分区值，并插入到表中，当达到预警值（Change_Value）时，我们就执行交换分区；

设计步骤2：使用一个存储过程来完成交换分区；

1) 创建一个临时表

2) 交换分区数据

3) 删除临时表

4) 修改分区方案

5) 修改分区函数

设计步骤3：使用作业定时执行存储过程，实现自动化；

三、参考脚本

下面是创建表的脚本和执行交换分区的存储过程，希望对你有帮助。

--创建表

CREATE TABLE [dbo].[PartitionManage](

[Id] [int] IDENTITY(1,1) NOT NULL,

[Change_Value] [int] NULL,

[Part_Value] [int] NULL,

[IsDone] [bit] NULL,

CONSTRAINT [PK_PartitionManage] PRIMARY KEY CLUSTERED

(

[Id] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

--插入数据

declare @i int

declare @maxValue int

set @i=10

set @maxValue=100

while @i <= @maxValue

begin

insert into dbo.PartitionManage values(@i-4,@i,0)

set @i = @i + 10

end

-- =============================================

-- Author: <Viajar>

-- Create date: <2011.02.22>

-- Description: <分区管理>

-- =============================================

CREATE PROCEDURE [dbo].[sp_PartitionManage]

BEGIN

DECLARE @Max_value INT

DECLARE @Change_value INT

DECLARE @PARTITION_value INT

SELECT @Max_value = MAX(Id) FROM [dbo].[Archive]

SELECT TOP 1 @Change_value = Change_Value,@PARTITION_value= Part_Value

FROM [dbo].[PartitionManage] WHERE IsDone = 0

IF(@Change_value <= @Max_value)--判断是否需要整理分区

BEGIN

--创建一个临时表

DECLARE @sql VARCHAR(MAX)

SET @sql = '

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[Temp_Archive]'') AND type in (N''U''))

BEGIN

DROP TABLE [dbo].[Temp_Archive]

END'

EXEC (@sql)

SET @sql = '

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[Temp_Archive]'') AND type in (N''U''))

BEGIN

CREATE TABLE [dbo].[Temp_Archive](

[Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,

[SiteId] [int] NULL,

[Title] [nvarchar](4000) NULL,

[Author] [nvarchar](4000) NULL,

[Content] [nvarchar](max) NULL,

CONSTRAINT [PK_Temp_Archive] PRIMARY KEY CLUSTERED

(

[Id] ASC

)WITH( PAD_INDEX = ON, FILLFACTOR = 100, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Sch_Archive_Id](Id)

) ON [Sch_Archive_Id]([Id])

END'

EXEC (@sql)

--交换分区数据

DECLARE @Min_Id INT

DECLARE @PARTITION_num INT

SELECT @Min_Id = MIN(Id) FROM [dbo].[Archive]

SELECT @PARTITION_num = [Archives].$PARTITION.Fun_Archive_Id(@Min_Id);

ALTER TABLE [dbo].[Archive] SWITCH PARTITION @PARTITION_num TO [dbo].[Temp_Archive] PARTITION @PARTITION_num

--删除临时表

DROP TABLE [dbo].[Temp_Archive]

--修改分区方案

DECLARE @PARTITION_string varchar(50)

SET @PARTITION_string = 'FG_Archive_Id_' + RIGHT('0' + CONVERT(NVARCHAR,@PARTITION_num),2)

SET @sql = 'ALTER PARTITION SCHEME [Sch_Archive_Id] NEXT USED ['+@PARTITION_string+']'

EXEC (@sql)

--修改分区函数

SET @sql = 'ALTER PARTITION FUNCTION Fun_Archive_Id() SPLIT RANGE ('+CONVERT(VARCHAR(50),@PARTITION_value)+')'

EXEC (@sql)

--更新表

UPDATE [dbo].[PartitionManage] SET IsDone = 1 WHERE Change_Value = @Change_value

END

SQL Server 表分区注意事项

一、表分区文章索引

SQL Server 合并（删除）分区解惑

SQL Server 2005 分区模板与实例

SQL Server 动态生成分区脚本

SQL Server 2005 自动化删除表分区设计方案

二、目的

这段时间，在SQL Server的生产环境中尝试了不同方式的表分区，积累了一些这方面的经验，这里就表分区的一些注意事项做些记录。

三、注意事项

1. 表分区的边界值问题，在使用Left和Right的时候需要注意，特别是在时间分割上需要特别注意，通常情况下，以00:00:00.000是最可靠的，这种分割就需要使用到Right，如果是Left的话就需要设置为23:59:59.997；更多信息请参考：SQL Server 合并（删除）分区解惑

2. 对于分区值的第一个值，符合这个值之前的数值都会给分配到第一个分区中，而使用Left和Right的区别就是这个分区值会被分配到第一个分区还是第二个分区而已；

3. 在SQL Server 2005 中的分区表和索引也提到在时间分区上的例子，RANGE RIGHT FOR VALUES ('20001001 00:00:00.000', '20010101 00:00:00.000', '20010401 00:00:00.000', '20010701 00:00:00.000')，更加简便的形式就是RANGE RIGHT FOR VALUES ('20001001', '20010101', '20010401', '20010701')，完全撇开了时分秒的问题了。

4. 通常情况下，我们会以一个表Id(int)，并且是自增作为分区字段，这样分区的好处就是很容易区分历史数据了（假如你的历史数据是以插入到表的时间来区别的话），而且对分区的操作隔离也是最明显的。这里有一个模板就是针对Id为例子的范文：SQL Server 2005 分区模板与实例

5. 当以Id作为分区函数值并不能满足你的需求的时候，你可能需要考虑不一样的东西了，因为在创建Id为主键的时候，默认的情况下就是为这个主键创建为聚集索引的，所以以Id为分区字段的话，Id自增，就会顺序的被放到递增的分区文件中。这里假如你想以分类标识ClassId作为分区的话，那么你有几种选择，一个就是把Id+ClassId作为非聚集的主键（PRIMARY KEY NONCLUSTERED），创建ClassId为聚集索引（CLUSTERED），这样就可以以ClassId作为分区字段了；另外一个选择就是：Id+ClassId作为聚集的主键（PRIMARY KEY CLUSTERED），这样就不用另外创建一个索引了。可以参考文献：已分区索引的特殊指导原则

6. 对聚集索引进行分区时，聚集键必须包含分区依据列。对非唯一的聚集索引进行分区时，如果未在聚集键中明确指定分区依据列，默认情况下 SQL Server 将在聚集索引键列表中添加分区依据列。如果聚集索引是唯一的，则必须明确指定聚集索引键包含分区依据列。对唯一的非聚集索引进行分区时，索引键必须包含分区依据列。对非唯一的非聚集索引进行分区时，默认情况下 SQL Server 将分区依据列添加为索引的非键（包含性）列，以确保索引与基表对齐。如果索引中已经存在分区依据列，SQL Server 将不会向索引中添加分区依据列。可以参考文献：已分区索引的特殊指导原则（要理解上面的描述，需要一点功底）

7. 如果你需要在你的分区上创建全文索引，那么你创建分区的时候就需要注意了，因为全文索引需要唯一索引的支持，而且这个唯一索引不能是复合索引，只能是单个字段的唯一索引。这个索引的要求：“unique, single-column, non-nullable index”。

8. 如果我们的分区值是随着时间的变化而增加的话，那么我们在设置表分区之后，系统跑了一段时间之后，那么最后一个分区占用的空间就会越来越大，除非你在创建分区的时候已经确认了这些分区值不会再增加了，在MSDN的文档中：SQL Server 2005 中的分区表和索引，里面提到的月份表分区、地区分区这些固定分区值，这并没有很好的表达在生产环境中所面临的分区值在不断增长的问题。所以这里就针对这个问题做了一个Job，这个Job可以动态、自动化的完成删除分区（交换分区），修改分区函数、分区方案等操作。进入阅读：SQL Server 2005 自动化删除表分区设计方案

9. 在MSDN自动化分区的前驱：SQL2005PartitioningScripts.exe，里面有些是值得参考的，但是开发一个适合自己业务需求的自动化分区也是必要的，有兴趣的可以阅读：SQL Server 动态生成分区脚本，这里主要解决了创建表分区过程中反复修改文件组、文件的名称设置的问题。

四、参考文献

SQL Server 2005 中的分区表和索引

已分区索引的特殊指导原则