sqlserver数据库分区表创建和修改实例.docVIP

--建数据库 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 NTmp_db_f1,filename ND:\Date\Tmp_db_f1.ndf,size 3072kb,filegrowth 1024kb to filegroup f1 alter database Tmp_db add file name NTmp_db_f2,filename ND:\Date\Tmp_db_f2.ndf,size 3072kb,filegrowth 1024kb to filegroup f2 alter database Tmp_db add file name NTmp_db_f3,filename ND:\Date\Tmp_db_f3.ndf,size 3072kb,filegrowth 1024kb to filegroup f3 alter database Tmp_db add file name NTmp_db_f4,filename ND:\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 NTmp_db_f5,filename ND:\Date\Tmp_db_f5.ndf,size 3072kb,filegrowth 1024kb to filegroup f5 --修改分区方案 ALTER PARTITION SCHEME sh_te