< AS RANGE RIGHT FOR VALUES (N'2016-10-01T00:00:00.000')
GO
ALTER DATABASE [DATABASENAME] ADD FILEGROUP [FG_TABLENAME20161001]
GO
ALTER DATABASE [DATABASENAME] ADD FILE ( NAME = N'F_TABLENAME20161001', FILENAME = N'X:\Databases\DATABASENAME\F_TABLENAME20161001.ndf', SIZE = 8MB, FILEGROWTH = 512MB ) TO FILEGROUP [FG_TABLENAME20161001]
GO
CREATE PARTITION SCHEME [ps_TABLENAME] AS PARTITION [pf_TABLENAME] TO ([PRIMARY], [FG_TABLENAME20161001])
GO
CREATE TABLE [dbo].[TABLENAME](
[id] [BIGINT] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
...
[createDateTime] [datetime] NOT NULL,
CONSTRAINT [PK_TABLENAME] PRIMARY KEY CLUSTERED ([id], [createDateTime])
) ON pf_TABLENAME([createDateTime])
GO
DECLARE @Date date = '20160101', @DateString varchar(10) = '', @Cmd nvarchar(max) = '';
WHILE @Date < '20210101'
BEGIN
SET @DateString = FORMAT(@Date, 'yyyyMMdd')
SET @Cmd = '
USE [DATABASENAME];
GO
ALTER DATABASE [DATABASENAME] ADD FILEGROUP [FG_TABLENAME' + @DateString + ']
ALTER DATABASE [DATABASENAME] ADD FILE (NAME = ''F_TABLENAME' + @DateString + ''',
FILENAME = ''X:\Databases\DATABASENAME\F_TABLENAME' + @DateString + '.ndf'',
SIZE = 8MB,
MAXSIZE = UNLIMITED, FILEGROWTH = 128MB)
TO FILEGROUP [FG_TABLENAME' + @DateString + ']
ALTER PARTITION SCHEME [ps_TABLENAME] NEXT USED [FG_TABLENAME' + @DateString + ']
ALTER PARTITION FUNCTION [pf_TABLENAME]() split RANGE (''' + @DateString + ''')
'
PRINT '******************************************'
PRINT @CMD
EXEC (@cmd)
SET @Date = DATEADD(month, 1, @DAte)
END
]]>
