创建分区

【IT专家网独家】你是否在千方百计优化SQL Server 数据库的性能?如果你的数据库中含有大量的表格，把这些表格分区放入独立的文件组可能会让你受益匪浅。SQL Server 2005引入的表分区技术，让用户能够把数据分散存放到不同的物理磁盘中，提高这些磁盘的并行处理性能以优化查询性能。

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

　　1. 创建分区函数

　　2. 创建分区架构

　　3. 对表进行分区

　　下面将对每个步骤进行详细介绍。

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

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

　　我们可以通过指定每个分区的边界条件来定义分区。例如，假定我们有一份Customers表，其中包含了关于所有客户的信息，以一一对应的客户编号(从1到1,000,000)来区分。我们将通过以下的分区函数把这个表分为四个大小相同的分区：　　

 

CREATE PARTITION FUNCTION customer_partfunc (int)
　　AS RANGE RIGHT
　　FOR VALUES (250000, 500000, 750000)

　　这些边界值定义了四个分区。第一个分区包括所有值小于250,000的数据，第二个分区包括值在250,000到49,999之间的数据。第三个分区包括值在500,000到7499,999之间的数据。所有值大于或等于750,000的数据被归入第四个分区。

　　请注意，这里调用的"RANGE RIGHT"语句表明每个分区边界值是右界。类似的，如果使用"RANGE LEFT"语句，则上述第一个分区应该包括所有值小于或等于250,000的数据，第二个分区的数据值在250,001到500,000之间，以此类推。

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

　　一旦给出描述如何分割数据的分区函数，接着就要创建一个分区架构，用来定义分区位置([u]where[/u])。创建过程非常直截了当，只要将分区连接到指定的文件组就行了。例如，如果有四个文件组，组名从"fg1"到"fg4"，那么以下的分区架构就能达到想要的效果：　　

 

CREATE PARTITION SCHEME customer_partscheme
　　AS PARTITION customer_partfunc
　　TO (fg1, fg2, fg3, fg4)

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

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

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

　　例如，使用以上的分区架构创建一个客户表，可以调用以下的Transact-SQL指令：　　

 

CREATE TABLE customers (FirstName nvarchar(40), LastName nvarchar(40), CustomerNumber int)
　　ON customer_partscheme (CustomerNumber)

　　关于SQL Server的表分区功能，你知道上述的相关知识就足够了。记住!编写能够用于多个表的一般的分区函数和分区架构就能够大大提高可复用性。

=======================================第二遍==================================================

--创建分区表过程一共分为三步:创建分区函数、创建分区方案、创建分区表

 

/*本实验涉及两个表:transactionhistory、transactionhistoryarchive，数据从adventureworks导过来，
下面要将这两张表分别建成分区表*/
--创建分区表transactionhistory
--创建分区函数
use wjz
go
create partition function transactionhistorypf1(datetime)
as range right for values(
'2003-9-1','2003-10-1','2003-11-1','2003-12-1','2004-1-1',
'2004-2-1','2004-3-1','2004-4-1','2004-5-1','2004-6-1',
'2004-7-1','2004-8-1'
);
go

--创建分区方案
create partition scheme transactionhistoryps1
as partition transactionhistorypf1 to
([primary],wjz2,wjz3,wjz4,wjz5,wjz6,wjz7,wjz8,wjz9,wjz10,wjz11,wjz12,wjz13,wjz14)
go

--创建分区表
CREATE TABLE [TransactionHistory](
[TransactionID] [int] IDENTITY(100000,1) NOT NULL,
[ProductID] [int] NOT NULL,
[ReferenceOrderID] [int] NOT NULL,
[ReferenceOrderLineID] [int] NOT NULL CONSTRAINT [DF_TransactionHistory_ReferenceOrderLineID]   DEFAULT ((0)),
[TransactionDate] [datetime] NOT NULL CONSTRAINT [DF_TransactionHistory_TransactionDate]   DEFAULT (getdate()),
[TransactionType] [nchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Quantity] [int] NOT NULL,
[ActualCost] [money] NOT NULL,
[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_TransactionHistory_ModifiedDate]   DEFAULT (getdate()),

) ON transactionhistoryps1(TransactionDate);
go

--创建分区表transactionhistoryarchive
--创建分区函数
create partition function transactionhistoryarchivepf1(datetime)
as range right for values( '2003-9-1','2003-10-1')
go

--创建分区方案
create partition scheme transactionhistoryarchiveps1
as partition transactionhistoryarchivepf1 to(
[primary],wjz2,wjz3
);
go

--创建分区表
CREATE TABLE [TransactionHistoryArchive](
[TransactionID] [int] NOT NULL,
[ProductID] [int] NOT NULL,
[ReferenceOrderID] [int] NOT NULL,
[ReferenceOrderLineID] [int] NOT NULL CONSTRAINT [DF_TransactionHistoryArchive_ReferenceOrderLineID]   DEFAULT ((0)),
[TransactionDate] [datetime] NOT NULL CONSTRAINT [DF_TransactionHistoryArchive_TransactionDate]   DEFAULT (getdate()),
[TransactionType] [nchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Quantity] [int] NOT NULL,
[ActualCost] [money] NOT NULL,
[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_TransactionHistoryArchive_ModifiedDate]   DEFAULT (getdate()),

) ON transactionhistoryarchiveps1(transactiondate);
go

--Load data use SSIS

--分区表创建完毕

--管理分区表
--将transactionhistory的第2个分区移动至transactionhistoryarchive的第2个分区
--注意，两张表的第2个分区刚好位于同一个文件组wjz2中
alter table transactionhistory
switch partition 2 to
transactionhistoryarchive
partition 2
go
--验证一下数据已经在一秒钟之内转移到新表里了
select * from transactionhistoryarchive

--为transactionhistory表在右端新增一个分区
--注意，因为我创建分区方案时多写了一个文件组，因此那个多余的文件组就成了next used，
--否则要先修改分区方案来增加新的可用文件组
alter partition function transactionhistorypf1()
split range ('2004-9-1')

--将transactionhistory表左端的两个空分区合而为一
alter partition function transactionhistorypf1()
merge range('2003-9-1')
go

--为transactionhistoryarchive表在右端新增一个分区，做好下次转移准备，注意现在要先修改分区方案
alter partition scheme transactionhistoryarchiveps1
next used wjz4
go
alter partition function transactionhistoryarchivepf1()
split range('2003-11-1')
go

--将transactionhistoryarchive表左端的两个分区合并（这个可选，至少是好是坏，目前还没想好）
alter partition function transactionhistoryarchivepf1()
merge range('2003-9-1')

/*删除用的
drop table transactionhistoryarchive
go
drop partition scheme transactionhistoryarchiveps1
go
drop partition function transactionhistoryarchivepf1
go

drop table transactionhistory
go
drop partition scheme transactionhistoryps1
go
drop partition function transactionhistorypf1
go*/

--以上情况可写成job进行循环