SQL Server基础SQL脚本之分区表、分区方案

--创建文件组
create database alex2
on primary
(name='alex',filename='c:\Data\alex.mdf',size=3mb,maxsize=10mb,filegrowth=1mb),
Filegroup old
(name='olddb',filename='c:\Data\olddb.ndf',size=1mb,maxsize=10mb,filegrowth=1mb),
Filegroup first
(name='firstdb',filename='c:\Data\firstdb.ndf',size=1mb,maxsize=10mb,filegrowth=1mb),
Filegroup second
(name='seconddb',filename='c:\Data\seconddb.ndf',size=1mb,maxsize=10mb,filegrowth=1mb),
Filegroup third
(name='thirddb',filename='c:\Data\thirddb.ndf',size=1mb,maxsize=10mb,filegrowth=10%),
Filegroup fourth
(name='fourthdb',filename='c:\Data\fourthdb.ndf',size=1mb,maxsize=10mb,filegrowth=10%)
log on
(name='alex_log',filename='c:\Data\alex_log.ldf',size=1mb,maxsize=10mb,filegrowth=1mb)
go

--使用数据库
use alex2
go

--创建分区函数
--按照一定的条件划分数据
--range left   (1900-01-01,1996-01-01] (1996-01-01,2000-01-01] 分界点的值归左边
--range right  (1900-01-01,1996-01-01) [1996-01-01,2000-01-01)    分界点的值归右边
create partition function RateChngDate(datetime)
as range right for values('1996-01-01','2000-01-01','2004-01-01','2008-01-01')


    --创建分区方案
--将分区函数区分的范围和文件组对应起来
create partition scheme RateChngDate_Scheme
as partition RateChngDate to (Old,First,Second,Third,Fourth)

--创建分区表
create table EmpPayHistPart
(
    EmployeeID int,
    RateChangeDate datetime,
    Rate money,
    PayFrequency tinyint,
    ModifiedDate datetime
)on RateChngDate_Scheme(RateChangeDate)


--添加数据
insert into EmpPayHistPart values(1,'1992-01-01',1200,2,'2005-01-01')
insert into EmpPayHistPart values(1,'2001-01-01',1200,2,'2005-01-01')
insert into EmpPayHistPart values(1,'2003-01-01',1200,2,'2005-01-01')
insert into EmpPayHistPart values(1,'2005-01-01',1200,2,'2005-01-01')
insert into EmpPayHistPart values(1,'2023-01-01',1200,2,'2005-01-01')
--检索分区
select * from EmpPayHistPart where $partition.RateChngDate(RateChangeDate)=0
select * from EmpPayHistPart where $partition.RateChngDate(RateChangeDate)=1
select * from EmpPayHistPart where $partition.RateChngDate(RateChangeDate)=2
select * from EmpPayHistPart where $partition.RateChngDate(RateChangeDate)=3
select * from EmpPayHistPart where $partition.RateChngDate(RateChangeDate)=4
select * from EmpPayHistPart where $partition.RateChngDate(RateChangeDate)=5
select * from EmpPayHistPart where $partition.RateChngDate(RateChangeDate)=6

select * from EmpPayHistPart


---------------分隔分区
--修改数据库添加文件组
alter database alex2
add    filegroup Fifth
--修改数据库向文件组中添加文件
alter database alex2
add file(name='Fifthdb',filename='c:\Data\Fifthdb.ndf',size=1mb,maxsize=10mb,filegrowth=1mb)
to filegroup Fifth
go
--修改分区方案 让下一个分区使用Fifth文件组
alter partition scheme RateChngDate_Scheme
next used Fifth
--修改分区函数 加入一个临界点2002-01-01
alter partition function RateChngDate()
split range ('2002-01-01')
go

---------------------合并分区
--将2008-01-01之后的数据和前一个分区数据合并
--原来是2004-01-01到2008-01-01，2008-01-01到以后
--现在是2004-01-01到以后
alter partition function RateChngDate()
merge range('2008-01-01')

-------------------创建表保存分区
--修改数据库添加文件组Sixth
alter database alex2
add    filegroup Sixth
--修改数据库添加文件到文件组Sixth
alter database alex2
add file(name='Sixthdb',filename='c:\Data\Sixthdb.ndf',size=1mb,maxsize=10mb,filegrowth=1mb)
to filegroup Sixth
go
--修改分区方案让下一个分区对应文件组Sixth
alter partition scheme RateChngDate_Scheme
next used Sixth
--分隔数据分区
alter partition function RateChngDate()
split range ('2012-01-01')
go
--创建表来保存分区数据
create table New_EmpPayHistPart
(
    EmployeeID int,
    RateChangeDate datetime,
    Rate money,
    PayFrequency tinyint,
    ModifiedDate datetime
)on Sixth
--将分区6中的数据移动到新表中
alter table EmpPayHistPart
switch partition 6 to New_EmpPayHistPart