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

-----------------------------建立分区表------------------------------- --建数据库

create database Tmp_db on (

name = Tmp_db_dat,

filename = 'D:\\Date\\Tmp_db.mdf', size = 10, maxsize = 50, filegrowth = 5 )

log on (

name = Tmp_db_log,

filename = 'D:\\Date\\Tmp_db.ldf', size = 5,

maxsize = 25, filegrowth = 5 ) go

use Tmp_db go

--增加文件组

alter database Tmp_db add filegroup f1 alter database Tmp_db add filegroup f2 alter database Tmp_db add filegroup f3 alter database Tmp_db add filegroup f4

--将文件分配到文件组

alter database Tmp_db

add file (name = N'Tmp_db_f1',filename =

N'D:\\Date\\Tmp_db_f1.ndf',size=3072kb,filegrowth=1024kb) to filegroup f1

alter database Tmp_db

add file (name = N'Tmp_db_f2',filename =

N'D:\\Date\\Tmp_db_f2.ndf',size=3072kb,filegrowth=1024kb) to filegroup f2

alter database Tmp_db

add file (name = N'Tmp_db_f3',filename =

N'D:\\Date\\Tmp_db_f3.ndf',size=3072kb,filegrowth=1024kb) to filegroup f3

alter database Tmp_db

add file (name = N'Tmp_db_f4',filename =

N'D:\\Date\\Tmp_db_f4.ndf',size=3072kb,filegrowth=1024kb) to filegroup f4

--创建分区函数

create partition function pt_fn_test(int)

as range left for values(3000,6000,9000)

--创建分区方案

create partition scheme sh_test as partition pt_fn_test to (f1,f2,f3,f4)

--创建分区表

create table t_test (

id int identity(1,1) primary key, class_a varchar(50), class_b varchar(50), add_dt datetime )on sh_test(id)

--为表插入W条数据 declare @i int select @i = 1 while @i<=14000 begin insert into t_test(class_a,class_b,add_dt) select case @i%2 when 0 then 'class_a' + cast(@i as varchar) else cast(@i as varchar)end, case @i%5 when 0 then 'class_b' + cast(@i as varchar) else cast(@i as varchar) end,getdate() select @i = @i +1 end

--查看分区信息 select $partition.pt_fn_test(id) as partition_number_id, min(id) as min_id, max(id) as max_id,count(*) as partition_cnt from tmp_db.dbo.t_test

group by $partition.pt_fn_test(id) order by partition_number_id go

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

alter database Tmp_db add filegroup f5

--将文件分配到文件组

alter database Tmp_db

add file (name = N'Tmp_db_f5',filename =

N'D:\\Date\\Tmp_db_f5.ndf',size=3072kb,filegrowth=1024kb) to filegroup f5

--修改分区方案

ALTER PARTITION SCHEME sh_test NEXT USED f5

--修改分区函数

ALTER PARTITION FUNCTION pt_fn_test () SPLIT RANGE (12000)