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 when0 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)