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 Files\Data\File2010.ndf') TO FILEGROUP FG2010
ALTER DATABASE [Test] ADD FILE (NAME='File2011',FILENAME=N'D:\Program Files\Data\File2011.ndf') TO FILEGROUP FG2011
ALTER DATABASE [Test] ADD FILE (NAME='FileAll',FILENAME=N'D:\Program Files\Data\FileAll.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 Files\Data\File2016.ndf
SET @fileNamePath='D:\Program Files\Data\File'+ 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)