sql server数据库分区表创建和修改实例.doc

-----------------------------建立分区表-------------------------------
--建数据库
createdatabaseTmp_db
on
(
name=Tmp_db_dat,
filename='D:\Date\Tmp_db.mdf',
size=10,
maxsize=50,
filegrowth=5
)
logon
(
name=Tmp_db_log,
filename='D:\Date\Tmp_db.ldf',
size=5,
maxsize=25,
filegrowth=5
)
go

useTmp_db
go

--增加文件组
alterdatabaseTmp_dbaddfilegroupf1
alterdatabaseTmp_dbaddfilegroupf2
alterdatabaseTmp_dbaddfilegroupf3
alterdatabaseTmp_dbaddfilegroupf4

--将文件分配到文件组

alterdatabaseTmp_db
addfile(name=N'Tmp_db_f1',filename=N'D:\Date\Tmp_db_f1.ndf',size=3072kb,filegrowth=1024kb)
tofilegroupf1

alterdatabaseTmp_db
addfile(name=N'Tmp_db_f2',filename=N'D:\Date\Tmp_db_f2.ndf',size=3072kb,filegrowth=1024kb)
tofilegroupf2

alterdatabaseTmp_db
addfile(name=N'Tmp_db_f3',filename=N'D:\Date\Tmp_db_f3.ndf',size=3072kb,filegrowth=1024kb)
tofilegroupf3

alterdatabaseTmp_db
addfile(name=N'Tmp_db_f4',filename=N'D:\Date\Tmp_db_f4.ndf',size=3072kb,filegrowth=1024kb)
tofilegroupf4

--创建分区函数
createpartitionfunctionpt_fn_test(int)
asrangeleftforvalues(3000,6000,9000)

--创建分区方案
createpartitionschemesh_testaspartitionpt_fn_testto(f1,f2,f3,f4)

--创建分区表
createtablet_test
(
idintidentity(1,1)primarykey,
class_avarchar(50),
class_bvarchar(50),
add_dtdatetime
)onsh_test(id)

--为表插入W条数据
declare@iint
select@i=1
while@i<=14000
begin
insertintot_test(class_a,class_b,add_dt)
selectcase@i%2when0then'class_a'+cast(@iasvarchar)elsecast(@iasvarchar)end,
case@i%5when0then'class_b'+cast(@iasvarchar)elsecast(@iasvarchar)end,getdate()
select@i=@i+1
end

--查看分区信息
select$partition.pt_fn_test(id)aspartition_number_id,min(id)asmin_id,max(id)asmax_id,count(*)aspartition_cnt
fromtmp_db.dbo.t_test
groupby$partition.pt_fn_test(id)
orderbypartition_number_id
go

-------------------------修改分区表------------------------
--增加文件组
alterdatabaseTmp_dbaddfilegroupf5

--将文件分配到文件组
alterdatabaseTmp_db
addfile(name=N'Tmp_db_f5',filename=N'D:\Date\Tmp_db_f5.ndf',size=3072kb,filegrowth=1024kb)
tofilegroupf5

--修改分区方案
ALTERPARTITIONSCHEMEs