--生成分区脚本
declare @DataBaseName nvarchar(50)--数据库名称
declare @TableName nvarchar(50)--表名
declare @ColumnName nvarchar(50)--字段名称
declare @PartNumber int--需要分多少个分区
declare @Location nvarchar(50)--保存分区文件的路径
declare @Size nvarchar(50)--分区初始化大小
declare @FileGrowth nvarchar(50)--分区文件增量
declare @FunValue int--分区分段值
declare @i int
declare @PartNumberStr nvarchar(50)
declare @sql nvarchar(max)

--设置下面的变量
set @DataBaseName='HT_DATA_DISPLAY2'
set @TableName='tb_test'
set @ColumnName='id'
set @PartNumber=4
set @Location='F:\sql shili\database\'
set @Size='30mb'
set @FileGrowth='10%'
set @FunValue=100000

--创建文件组
set @i=1

while @i<@PartNumber
begin
set @PartNumberStr=RIGHT('0'+CONVERT(nvarchar,@i),2)

set @sql='alter database ['+@DataBaseName+']
add filegroup [FG_'+@TableName+'_'+@ColumnName+'_'+@PartNumberStr+']'

print @sql+char(13)
set @i=@i+1
end



--创建文件
set @i=1

--print char(13)

while @i<@PartNumber
begin
set @PartNumberStr=RIGHT('0'+CONVERT(nvarchar,@i),2)
set @sql='alter database ['+@DataBaseName+']
add file
(Name=N''FG_'+@TableName+'_'+@ColumnName+'_'+@PartNumberStr+'_data'',filename=N'''
+@Location+'FG_'+@TableName+'_'+@ColumnName+'_'+@PartNumberStr+'_data.ndf'',size='
+@Size+',filegrowth='+@FileGrowth+')
to filegroup [FG_'+@TableName+'_'+@ColumnName+'_'+@PartNumberStr+'];'
print @sql+char(13)
set @i=@i+1

end

--创建分区函数

declare @FunValueStr nvarchar(max)
set @i=1
set @FunValueStr=''
while @i<@PartNumber
begin
set @FunValueStr=@FunValueStr+CONVERT(nvarchar(50),(@i*@FunValue))+','
set @i=@i+1
end
set @FunValueStr=SUBSTRING(@FunValueStr,1,LEN(@FunValueStr)-1)
set @sql='create partition function Fun_'+@TableName+'_'+@ColumnName+'(int)
as range right for values('+@FunValueStr+')'
print @sql+char(13)


--创建分区方案

declare @FileGroupStr nvarchar(max)
set @i=1
set @FileGroupStr=''
while @i<=@PartNumber
begin
set @PartNumberStr=RIGHT('0'+CONVERT(nvarchar,@i),2)
set @FileGroupStr=@FileGroupStr+'[FG_'+@TableName+'_'+@ColumnName+'_'+@PartNumberStr+'],'
set @i=@i+1
end
set @FileGroupStr=SUBSTRING(@FileGroupStr,1,LEN(@FileGroupStr)-1)
set @sql='create partition scheme Sch_'+@TableName+'_'+@ColumnName+' AS
partition Fun_'+@TableName+'_'+@ColumnName+'to('+@FileGroupStr+')'
print  @sql+char(13)


--分区函数的记录数

set @sql='select $partition.Fun_'+@TableName+'_'+@ColumnName+'('+@ColumnName+') as partition_num,
min('+@ColumnName+') as min_value,max('+@ColumnName+') as max_value,count(1) as record_num
from dbo.'+@TableName+'
group by $partition.Fun_'+@TableName+'_'+@ColumnName+'('+@ColumnName+')
order by $partition.Fun_'+@TableName+'_'+@ColumnName+'('+@ColumnName+');'
print @sql +char(13)






alter database HT_DATA_DISPLAY_2
add filegroup FG_tml_id_1

alter database HT_DATA_DISPLAY_2
add file
(
name=N'FG_tml_updatetime_1_data',
filename=N'f:\sql shili\data\FG_tml_id_1_data.ndf',
size=3mb,
filegrowth=10%
)
to filegroup FG_tml_updatetime_1;