SqlServer自动化分区

1、新增文件组

ALTER DATABASE [Test] ADD FILEGROUP FG2010
ALTER DATABASE [Test] ADD FILEGROUP FG2011
ALTER DATABASE [Test] ADD FILEGROUP FGAll

2、新增分区文件

ALTER DATABASE [Test] ADD FILE (NAME='File2010',FILENAME=N'D:Program FilesDataFile2010.ndf') TO FILEGROUP FG2010
ALTER DATABASE [Test] ADD FILE (NAME='File2011',FILENAME=N'D:Program FilesDataFile2011.ndf') TO FILEGROUP FG2011
ALTER DATABASE [Test] ADD FILE (NAME='FileAll',FILENAME=N'D:Program FilesDataFileAll.ndf') TO FILEGROUP FGAll

3、创建分区函数

--该分区函数创建3个分区（<=2010-01-01、2010-01-01~2011-01-01，>2011-01-01）
--RANGE LEFT标识边界值在左边

CREATE PARTITION FUNCTION OrderPartitionFn(datetime)
AS RANGE LEFT
FOR VALUES('2010-01-01','2011-01-01')

4、创建分区方案

CREATE PARTITION SCHEME OrderPartitionScheme
AS PARTITION OrderPartitionFn
TO 
(
    FG2010,
    FG2011,
    FGAll
)

5、创建分区表

CREATE TABLE OrderRecords
(
    Id uniqueidentifier,
    CreateTime datetime,
    Total int,
)
 --OrderPartitionScheme是刚刚定义的分区架构，括号内为指定的分区列
ON OrderPartitionScheme(CreateTime)

6、定义执行自动分区操作

使用sql job 定期执行操作

DECLARE @maxValue DATETIME,
    @fileGroupName VARCHAR(200),
    @fileNamePath    VARCHAR(200),
    @partitionFnName VARCHAR(200),
    @schemeName VARCHAR(200),
    @fileName   VARCHAR(200),
    @sql        NVARCHAR(1000)

--分区函数
SET @partitionFnName='OrderPartitionFn';
--分区方案
SET @schemeName='OrderPartitionScheme';

--获取当前分区函数最大边界值
SELECT @MaxValue = Convert(datetime,MAX(value))
FROM SYS.PARTITION_RANGE_VALUES PRV
LEFT JOIN SYS.partition_functions fun on prv.function_id=fun.function_id
WHERE FUN.name=@partitionFnName--分区函数名

SET @fileGroupName='FG'+CONVERT(varchar,(YEAR(@MaxValue)+1))

--D:Program FilesDataFile2016.ndf
SET @fileNamePath='D:Program FilesDataFile'+ CONVERT(varchar,(YEAR(@MaxValue)+1))+'.ndf'
--File2016
SET @fileName=N'File'+ CONVERT(varchar,(YEAR(@MaxValue)+1))

--使用ALTER语句新增一个文件组
SET @sql='ALTER DATABASE [Test] ADD FILEGROUP '+@fileGroupName
PRINT @sql
EXEC(@sql)

SET @sql='ALTER DATABASE [Test] ADD FILE (NAME='''+@fileName+''',FILENAME=N'''+@fileNamePath+''') TO FILEGROUP'+' '+@fileGroupName
PRINT @sql
EXEC(@sql)
--修改分区方案，用一个新的文件组存放下一新增的数据
SET @sql='ALTER PARTITION SCHEME ['+@schemeName+'] NEXT USED'+' '+@fileGroupName
PRINT @sql;
EXEC(@sql)

SET @sql='ALTER PARTITION FUNCTION '+@partitionFnName+'() SPLIT RANGE ('''+Convert(varchar,DATEADD(YEAR,1,@maxValue),120)+''')';
print @sql;
EXEC(@sql)