动态创建分区函数
发布时间：2020-12-12 13:40:05 所属栏目：MsSql教程 来源：网络整理
导读：--生成分区脚本 declare @DataBaseName nvarchar(50)--数据库名称 declare @TableName nvarchar(50)--表名 declare @ColumnName nvarchar(50)--字段名称 declare @PartNumber int--需要分多少个分区 declare @Location nvarchar(50)--保存分区文件的路径 dec
|
--生成分区脚本 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 shilidatabase' 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,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,2) set @FileGroupStr=@FileGroupStr+'[FG_'+@TableName+'_'+@ColumnName+'_'+@PartNumberStr+'],' set @i=@i+1 end set @FileGroupStr=SUBSTRING(@FileGroupStr,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 shilidataFG_tml_id_1_data.ndf',size=3mb,filegrowth=10% ) to filegroup FG_tml_updatetime_1;
（编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |