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.pf_RegMail(rq)as partition_number_id
-- pt_fn_test(id)分区函数加分区函数列
,min(id)as min_id,max(id)as max_id,count(*)as partition_cnt
from test_data.dbo.PARTITIONERegMail
--分区表
group by$partition.pf_RegMail(rq)
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)
--SQL code--创建分区索引,现有分区方案TransactionsPS1 创建非聚集分区索引。

USE test_data
GO
IF EXISTS(SELECT name FROM sys.indexes
WHERE name= N'IX_TransactionHistory_ReferenceOrderID'--索引名称AND object_id=OBJECT_ID(N'dbo.PARTITIONERegMail'))--表名称DROP INDEX IX_TransactionHistory_ReferenceOrderID--索引名称ON dbo.PARTITIONERegMail --删除表的
GO
CREATE CLUSTERED INDEX IX_TransactionHistory_ReferenceOrderID ON dbo.PARTITIONERegMail
(
id asc
)--索引的列
ON ps_RegMail(rq);--分区方案+分区的列的值
GO。