--创建分区表过程一共分为三步:创建分区函数、创建分区方案、创建分区表
/*本实验涉及两个表: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进行循环
创建分区表过程
最新推荐文章于 2023-12-27 15:12:53 发布