一、手动创建分区

--创建分区函数
USE [guanggao];
CREATE PARTITION FUNCTION RangeTime (DATETIME)
AS RANGE LEFT FOR VALUES ('2016-03-27','2016-04-03');

--增加FileGroup
USE [guanggao];
GO
ALTER DATABASE [guanggao] ADD FILEGROUP [FileGroup20160327];
GO
ALTER DATABASE [guanggao] ADD FILEGROUP [FileGroup20160403];
GO
ALTER DATABASE [guanggao] ADD FILEGROUP [FileGroup20160410];
GO

--增加FileName
USE [guanggao];
GO
ALTER DATABASE [guanggao] ADD FILE ( NAME = N'File20160327', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\File20160327.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FileGroup20160327];
GO
ALTER DATABASE [guanggao] ADD FILE ( NAME = N'File20160403', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\File20160403.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FileGroup20160403];
GO
ALTER DATABASE [guanggao] ADD FILE ( NAME = N'File20160410', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\File20160410.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FileGroup20160410];
GO

--创建分区Schema
CREATE PARTITION SCHEME RangeSchema_CreateTime
AS PARTITION RangeTime
TO ([FileGroup20160327],[FileGroup20160403],[FileGroup20160410]);

--创建分区表
CREATE TABLE [dbo].[bihell_partition]
     (
.......
     )
ON  RangeSchema_CreateTime(create_date);

--插入数据
INSERT  INTO dbo.bihell_partition
         SELECT  *
         FROM    dbo.bihell;

--统计每个分区的数据量
SELECT  $partition.RangeTime(create_date) AS number
,       COUNT(*) AS rcount
FROM    [bihell_partition]
GROUP BY $partition.RangeTime(create_date);

二、自动创建分区并添加到分区函数

DECLARE @maxValue DATETIME
,   @secondMaxValue DATETIME
,   @differ INT
,   @fileGroupName VARCHAR(200)
,   @fileNamePath VARCHAR(200)
,   @fileName VARCHAR(200)
,   @sql NVARCHAR(1000);

SET @fileGroupName = 'FileGroup' +
     REPLACE(CAST(CAST(DATEADD(d, 6, CURRENT_TIMESTAMP) AS DATE) AS VARCHAR(10)),
             '-', '');
PRINT @fileGroupName;
SET @sql = 'ALTER DATABASE [guanggao] ADD FILEGROUP ' + @fileGroupName;
PRINT @sql;
--EXEC(@sql)

SET @fileNamePath = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\' +
     REPLACE(CAST(CAST(DATEADD(d, 6, CURRENT_TIMESTAMP) AS DATE) AS VARCHAR(10)),
             '-', '') + '.NDF';
SET @fileName = N'File' +
     REPLACE(CAST(CAST(DATEADD(d, 6, CURRENT_TIMESTAMP) AS DATE) AS VARCHAR(10)),
             '-', '');

SET @sql = 'ALTER DATABASE [guanggao] ADD FILE (NAME=''' + @fileName +
     ''',FILENAME=N''' + @fileNamePath + ''') TO FILEGROUP' + '    ' +
     @fileGroupName;
PRINT @sql;
--EXEC(@sql)

--修改分区方案，用一个新的文件组用于存放下一新增的数据
SET @sql = 'ALTER PARTITION SCHEME [RangeSchema_CreateTime] NEXT USED' +
     '    ' + @fileGroupName;
PRINT @sql;
--EXEC(@sql);

--分区架构
SELECT  @maxValue = CAST (MAX(value) AS DATETIME)
FROM    sys.partition_range_values PRV
WHERE   PRV.function_id = 65538;
PRINT @maxValue;

SELECT  @secondMaxValue = CAST (MIN(value) AS DATETIME)
FROM    ( SELECT TOP 2
                     *
           FROM      sys.partition_range_values
           WHERE     function_id = 65538
           ORDER BY  value DESC
         ) PRV;

PRINT @secondMaxValue;
SET @differ = DATEDIFF(d, @secondMaxValue, @maxValue); 
PRINT @differ;

ALTER PARTITION FUNCTION RangeTime()  --分区函数
SPLIT RANGE (DATEADD(d,@differ,@maxValue));

三、其他参考文档

BIWORK 分区表滑动窗口阅读与实践笔记

BIWORK 分区表阅读与实践笔记

sql server 小记——分区表（上）

SQL Server 解读【已分区索引的特殊指导原则】（1）- 索引对齐

SQL Server锁分区特性引发死锁解析

SqlServer 自动化分区方案

再说一下表分区

数据库优化实践【文件、文件组、分区篇】

深入浅出SQL Server 2008 分区函数和分区表

玩转SQL Server复制回路の变更数据类型、未分区表转为分区表