SQL Server Automation partition

1. New File Group

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

2. New Partition file

ALTER DATABASE [Test] ADD FILE(NAME='File2010', FILENAME=N'D:\Program files\data\file2010.ndf') toFILEGROUP FG2010ALTER DATABASE [Test] ADD FILE(NAME='File2011', FILENAME=N'D:\Program files\data\file2011.ndf') toFILEGROUP FG2011ALTER DATABASE [Test] ADD FILE(NAME='Fileall', FILENAME=N'D:\Program files\data\fileall.ndf') toFILEGROUP Fgall

3. Create a partition function

--This partition function creates 3 partitions (<=2010-01-01, 2010-01-01~2011-01-01,>2011-01-01)
--range left identifies the boundary value

CREATE FUNCTION ORDERPARTITIONFN (datetime)as left forVALUES('  2010-01-01','2011-01-01')

4. Create a partition scheme

CREATE PARTITION SCHEME orderpartitionscheme  as PARTITION ORDERPARTITIONFN  to (    FG2010,    FG2011,    fgall)

5. Create a partitioned table

CREATE TABLE orderrecords (    uniqueidentifier,    datetime,    int -- Orderpartitionscheme is the partition schema that you just defined, in parentheses for the specified partition column on    Orderpartitionscheme (Createtime)

6. Define automatic partition operation

Perform actions on a regular basis using SQL job

DECLARE @maxValue DATETIME,    @fileGroupName VARCHAR( $),    @fileNamePath    VARCHAR( $),    @partitionFnName VARCHAR( $),    @schemeName VARCHAR( $),    @fileName   VARCHAR( $),    @sql        NVARCHAR( +)--partition FunctionSET @partitionFnName='ORDERPARTITIONFN';--Partitioning SchemeSET @schemeName='Orderpartitionscheme';--gets the current partition function maximum boundary valueSELECT @MaxValue = Convert(datetime,MAX(value)) fromSYS. Partition_range_values PRV Left JOINSys.partition_functions Fun onprv.function_id=fun.function_idWHEREFun.name=@partitionFnName--name of partition functionSET @fileGroupName='FG'+CONVERT(varchar,( Year(@MaxValue)+1))--D:\Program files\data\file2016.ndfSET @fileNamePath='D:\Program Files\data\file'+ CONVERT(varchar,( Year(@MaxValue)+1))+'. NDF'--File2016SET @fileName=N'File'+ CONVERT(varchar,( Year(@MaxValue)+1))--Add a filegroup using the ALTER statementSET @sql='ALTER DATABASE [Test] ADD FILEGROUP'+@fileGroupNamePRINT @sqlEXEC(@sql)SET @sql='ALTER DATABASE [Test] ADD FILE (name=" "+@fileName+" ", Filename=n" "+@fileNamePath+" ") to FILEGROUP'+' '+@fileGroupNamePRINT @sqlEXEC(@sql)--Modify the partition scheme to store the next new data with a single file groupSET @sql='ALTER PARTITION SCHEME ['+@schemeName+'] NEXT used'+' '+@fileGroupNamePRINT @sql;EXEC(@sql)SET @sql='ALTER PARTITION FUNCTION'+@partitionFnName+'() SPLIT RANGE (" "+Convert(varchar,DATEADD( Year,1,@maxValue), -)+" ")';Print @sql;EXEC(@sql)

SQL Server Automation partition