SQLServer 表分区 根据时间自动扩展分区

前提条件

1. 已经创建好分区方案
2. 已经创建好分区函数

创建文件组

'alter database ' + @Database + ' add filegroup ' + [@fileGroupName]

创建文件并且将文件加入文件组

'alter database ' + @Database + ' add file ' +
           '( ' +
           'name = ''' + @FileName + ''',' +
           'filename = ''' + @FilePath + '\' + @FileName + '.mdf '',' +
           'size = ' + @FileSize + ',' +
           'filegrowth = ' + @FileGrowth + ',' +
           'maxsize = ' + @FileMaxLimit + '' +
           ')' +
           'to filegroup ' + @FileGroupName

修改分区方案

 'alter partition scheme ' + @SchemeName + ' next used ' + @FileGroupName 

修改分区函数

这里注意range函数内日志格式,如果日志格式不对则会上下界限问题

 'alter partition function ' + @PartitionName + ' split range (' + CONVERT(varchar(30), getdate(), 120) +')'

创建存储过程

1. 以下存储过程是将上面每个步骤的整合
2. 增加分区是针对分区方案和分区函数的,单纯增加分区这块来看,对表没有直接操作
3. 基于第二条,凡是用了以上方案和函数的表都会增加分区
4. 以下代码可以将exec替换成print 看从之态输出测试后在正式运行
5. 如果想拿来就用建议测试测试,想深入点去官网看把…

create procedure AutoExtendTrainMainDataPartition
as
begin
    declare
        @FilePath                      varchar(100),--文件路径
        @FileName                      varchar(100),--文件名称
        @FileSize                      varchar(100),--文件大小
        @FileGrowth                    varchar(100),--文件增长
        @FileMaxLimit                  varchar(100),--文件最大限制
        @FileGroupName                 varchar(100),--文件组名称
        @Database                      varchar(100),--操作数据库
        @CurrentDateTimeByYearAndMonth varchar(100),--当前时间,年月
        @SchemeName                    varchar(100), --分区方案名称
        @PartitionName                 varchar(100), --分区名称
        @Random                        smallint,
        @sql                           varchar(400)


-- 赋值文件属性
    set @FileSize = '3MB'
    set @FileGrowth = '10%'
    set @FileMaxLimit = 'unlimited'
    set @CurrentDateTimeByYearAndMonth = left(CONVERT(varchar(30), getdate(), 112), 6)
    set @FileName = 'TrainMainData_' + @CurrentDateTimeByYearAndMonth
    set @Random = cast(ceiling(rand() * 20) as int) -- 获取随机数,利用随机数决定当前执行脚本存放那块硬盘
    if @Random % 2 = 0
        set @FilePath = 'W:\yibiao\TrainMainDataPartitions'
    else
        set @FilePath = 'Y:\yibiao\TrainMainDataPartitions'

-- 赋值数据库属性
    set @Database = 'YiBiaoData'

-- 赋值文件组属性
    set @FileGroupName = 'TrainMainData_' + @CurrentDateTimeByYearAndMonth

-- 赋值分区属性
    set @SchemeName = 'AutoExtendScheme'
    set @PartitionName = 'AutoExtendPartition()'


--创建文件组
    set @sql = 'alter database ' + @Database + ' add filegroup ' + @fileGroupName + ''
    exec(@sql)

    --创建组文件,将文件绑定于文件组
--创建文件,指定文件组
    set @sql = 'alter database ' + @Database + ' add file ' +
               '( ' +
               'name = ''' + @FileName + ''',' +
               'filename = ''' + @FilePath + '\' + @FileName + '.mdf '',' +
               'size = ' + @FileSize + ',' +
               'filegrowth = ' + @FileGrowth + ',' +
               'maxsize = ' + @FileMaxLimit + '' +
               ')' +
               'to filegroup ' + @FileGroupName
    exec(@sql)

-- 修改分区方案
    set @sql = 'alter partition scheme ' + @SchemeName + ' next used ' + @FileGroupName + ''
    exec(@sql)

-- 修改分区函数
    set @sql = 'alter partition function ' + @PartitionName + ' split range (' + CONVERT(varchar(30), getdate(), 120) +
               ')'
    exec(@sql)


end
go

利用各种定时任务执行以上存储过程

由于分区界限和分区名称等都是用时间为准,建议每月第一天最早时间执行