分区实例

1.创建文件组

ALTER DATABASE [TestDB] ADD FILEGROUP [FG_RealTime_Min];

ALTER DATABASE [TestDB] ADD FILEGROUP [FG_RealTime_20170629];

ALTER DATABASE [TestDB] ADD FILEGROUP [FG_RealTime_Max];

2.创建文件（分区存储文件夹需手动创建）

ALTER DATABASE [TestDB] ADD FILE ( Name = N'File_RealTime_Min', FileName = N'E:\DBData\File_RealTime_Min.ndf' , SIZE = 1MB , FILEGROWTH = 10MB ) TO FILEGROUP [FG_RealTime_Min];

ALTER DATABASE [TestDB] ADD FILE ( Name = N'File_RealTime_20170629', FileName = N'E:\DBData\File_RealTime_20170629.ndf' , SIZE = 1MB , FILEGROWTH = 10MB ) TO FILEGROUP [FG_RealTime_20170629];

ALTER DATABASE [TestDB] ADD FILE ( Name = N'File_RealTime_Max', FileName = N'E:\DBData\File_RealTime_Max.ndf' , SIZE = 1MB , FILEGROWTH = 10MB ) TO FILEGROUP [FG_RealTime_Max];

3.创建分区函数

CREATE PARTITION FUNCTION [PF_RealTime](DATETIME) AS RANGE RIGHT FOR VALUES ('2017-06-29 00:00:00.000', '2030-01-01 00:00:00.000');

    1、CREATE PARTITION FUNCTION意思是创建一个分区函数。

    2、PF_RealTime为分区函数名称。

    3、AS RANGE RIGHT为设置分区范围的方式为Right，也就是右置方式。

    4、FOR VALUES (……)为按这些个值来分区，Values中的值就是分区的条件

4.创建分区方案

CREATE PARTITION SCHEME [PS_RealTime] AS PARTITION [PF_RealTime] TO ([FG_RealTime_Min],[FG_RealTime_20170629],[FG_RealTime_Max]);

    1、CREATE PARTITION SCHEME意思是创建一个分区方案。

    2、PS_RealTime为分区方案名称。

    3、AS PARTITION pPF_RealTime]明该分区方案所使用的数据划分条件（也就是所使用的分区函数）为PF_RealTime]。

    4、TO后面的内容是指PF_RealTime]分区函数划分出来的数据对应存放的文件组。

5.创建分区表（工具选项中选择编写分区方案勾选TRUE）

然后创建脚本：(唯一索引的分区依据列必须是索引键的子集。)

CREATE TABLE [dbo].[xxxxxx](

CONSTRAINT [IX_xxxxxx_1] PRIMARY KEY CLUSTERED
(
[div] DESC,
[Time] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PS_RealTime]([Time])
) ON [PS_RealTime]([Time])

GO

6.自动创建各个时间段的分区表（存储+作业）

CREATE PROC [dbo].[Proc_AutoManagerPARTITION] @Time DATETIME
AS
BEGIN
DECLARE @DBName VARCHAR(50)--数据库名称
DECLARE @DataPath VARCHAR(500)--文件存放路径
DECLARE @SchemeName VARCHAR(50)--分区策略名称
DECLARE @FunctionName VARCHAR(50)--分区函数名称
DECLARE @FileGroupName VARCHAR(100)--文件组名称
DECLARE @FileName VARCHAR(100)--文件名称
DECLARE @PerCreateDay INT--提前创建
DECLARE @SQL VARCHAR(MAX)
DECLARE @FileNamePrefix VARCHAR(50)--文件名称前缀
DECLARE @FileGroupPrefix VARCHAR(50)--文件组名称前缀
DECLARE @CurrentPartitionTime DATETIME--当前分区时间
DECLARE @Now DATETIME
SET @Now = @Time
SET @PerCreateDay = 5
SET @DBName = 'TestDB'
SET @FileNamePrefix = 'File_RealTime_'
SET @FileGroupPrefix = 'FG_RealTime_'
SET @FunctionName = 'PF_RealTime'
SET @SchemeName = 'PS_RealTime'
SET @DataPath = 'E:\DBData\'

IF ((SELECT COUNT(1) FROM Sys.PARTITION_RANGE_VALUES WHERE function_id = (SELECT function_id FROM sys.partition_functions WHERE Name=@FunctionName))>=(1000-@PerCreateDay))
BEGIN
GOTO BeginMerge;
END

SELECT @CurrentPartitionTime=CAST(MIN(ISNULL(VALUE,@Now)) AS DATETIME) FROM(
SELECT TOP 2 VALUE FROM Sys.PARTITION_RANGE_VALUES WHERE function_id = (SELECT function_id FROM sys.partition_functions WHERE Name=@FunctionName) ORDER BY VALUE DESC
)A
IF(@CurrentPartitionTime IS NULL)
BEGIN
SET @CurrentPartitionTime = DATEADD(DAY,-1,@Now)
END
WHILE(DATEDIFF(DD,@CurrentPartitionTime,DATEADD(DD,@PerCreateDay,@Now))>0)
BEGIN
SET @CurrentPartitionTime = DATEADD(DD,1,@CurrentPartitionTime)
SET @FileGroupName = @FileGroupPrefix + CONVERT(VARCHAR(10),@CurrentPartitionTime,112)
SET @FileName = @FileNamePrefix + CONVERT(VARCHAR(10),@CurrentPartitionTime,112)
SET @SQL = 'IF NOT EXISTS(SELECT 1 FROM sys.filegroups WHERE name='''+@FileGroupName+''')'+CHAR(10)
SET @SQL = @SQL + 'ALTER DATABASE ['+@DBName+'] ADD FILEGROUP ['+ @FileGroupName +']'+CHAR(10)
SET @SQL = @SQL + 'IF NOT EXISTS(SELECT 1 FROM sys.master_files WHERE database_id = DB_ID('''+@DBName+''') AND name='''+@FileName+''')'+CHAR(10)
SET @SQL = @SQL + 'ALTER DATABASE ['+@DBName+'] ADD FILE ( Name = N'''+@FileName+''', FileName = N'''+@DataPath + @FileName +'.ndf'' , SIZE = 1MB , FILEGROWTH = 10MB ) TO FILEGROUP ['+@FileGroupName+']'+CHAR(10)
SET @SQL = @SQL + 'ALTER PARTITION SCHEME ['+@SchemeName+'] NEXT USED ['+@FileGroupName+']'+CHAR(10)
SET @SQL = @SQL + 'ALTER PARTITION FUNCTION ['+@FunctionName+']()SPLIT RANGE('''+ CONVERT(VARCHAR(25),@CurrentPartitionTime,120) +'.000'')'+CHAR(10)
PRINT @SQl
EXEC(@SQl)
END

BeginMerge:

--未达到上限
IF ((SELECT COUNT(1) FROM Sys.PARTITION_RANGE_VALUES WHERE function_id = (SELECT function_id FROM sys.partition_functions WHERE Name=@FunctionName))<(1000-@PerCreateDay))
BEGIN
RETURN
END
--按月合并
SELECT ROW_NUMBER() OVER(ORDER BY TM) AS RowNum,TM INTO #FileInfo FROM(
SELECT CONVERT(VARCHAR(7),VALUE,120) AS TM,COUNT(1) AS FileNum FROM Sys.PARTITION_RANGE_VALUES
WHERE function_id = (SELECT function_id FROM sys.partition_functions WHERE Name=@FunctionName)
GROUP BY CONVERT(VARCHAR(7),VALUE,120)
)A WHERE A.FileNum>1

DECLARE @TempTime DATETIME
DECLARE @CurrentFileInfoRowNum INT
DECLARE @MergeNum INT
SET @CurrentFileInfoRowNum = 1
SET @MergeNum = 0
WHILE EXISTS(SELECT 1 FROM #FileInfo WHERE RowNum = @CurrentFileInfoRowNum)
BEGIN
SELECT @TempTime=CAST(TM+'-01' AS DATETIME) FROM #FileInfo WHERE RowNum = @CurrentFileInfoRowNum

SET @CurrentFileInfoRowNum = @CurrentFileInfoRowNum + 1

SELECT ROW_NUMBER() OVER(ORDER BY VALUE) AS RowNum,* INTO #MonthFileInfo FROM Sys.PARTITION_RANGE_VALUES
WHERE function_id = (SELECT function_id FROM sys.partition_functions WHERE Name=@FunctionName)
AND CAST(VALUE AS DATETIME)>=@TempTime AND CAST(VALUE AS DATETIME)<DATEADD(M,1,@TempTime)

DECLARE @i INT
SET @i = 2
--合并数据到当月第一天
WHILE EXISTS(SELECT 1 FROM #MonthFileInfo WHERE RowNum = @i)
BEGIN
SELECT @CurrentPartitionTime = CAST(VALUE AS DATETIME) FROM #MonthFileInfo WHERE RowNum = @i
SET @FileGroupName = @FileGroupPrefix + CONVERT(VARCHAR(10),@CurrentPartitionTime,112)
SET @FileName = @FileNamePrefix + CONVERT(VARCHAR(10),@CurrentPartitionTime,112)
PRINT @FileName+CHAR(10)
SET @SQL = 'ALTER PARTITION FUNCTION ['+@FunctionName+']() MERGE RANGE('''+ CONVERT(VARCHAR(25),@CurrentPartitionTime,120) +'.000'')'+CHAR(10)
SET @SQL = @SQL + 'IF EXISTS(SELECT 1 FROM sys.master_files WHERE database_id = DB_ID('''+@DBName+''') AND name='''+@FileName+''')'+CHAR(10)
SET @SQL = @SQL + 'ALTER DATABASE ['+@DBName+'] REMOVE FILE ['+@FileName+']'+CHAR(10)
SET @SQL = @SQL + 'IF EXISTS(SELECT 1 FROM sys.filegroups WHERE name='''+@FileGroupName+''')'+CHAR(10)
SET @SQL = @SQL + 'ALTER DATABASE ['+@DBName+'] REMOVE FILEGROUP ['+@FileGroupName+']'+CHAR(10)
PRINT(@SQL)
EXEC(@SQL)
SET @MergeNum = @MergeNum + 1
SET @i = @i + 1
IF(@MergeNum>=@PerCreateDay)
BEGIN
DROP TABLE #MonthFileInfo
GOTO ProcEnd
END
END
DROP TABLE #MonthFileInfo
END
ProcEnd:
DROP TABLE #FileInfo

7.合并和删除(说明不包含该步骤)

ALTER DATABASE [TestDB] ADD FILEGROUP [FG_RealTime_20170630];
ALTER DATABASE [TestDB] ADD FILE ( Name = N'File_RealTime_20170630', FileName = N'E:\DBData\File_ARealTime_20170630.ndf' , SIZE = 1MB , FILEGROWTH = 10MB ) TO FILEGROUP [FG_ARealTime_20170630];
ALTER PARTITION SCHEME [PS_RealTime] NEXT USED [FG_RealTime_20170630]
ALTER PARTITION FUNCTION [PF_RealTime]()SPLIT RANGE('2017-06-30 00:00:00.000')

ALTER PARTITION FUNCTION [PF_RealTime]() MERGE RANGE('2017-06-29 00:00:00.000')
ALTER DATABASE [TestDB] REMOVE FILE File_RealTime_20170629
ALTER DATABASE [TestDB] REMOVE FILEGROUP [FG_RealTime_20170629];