Mssql 滑动分区实现方案

直接上代码,按月实现数据分区，归档

USE [db2]
GO
/****** Object:  StoredProcedure [dbo].[partion_MoveScheme]    Script Date: 04/20/2011 18:41:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER    PROC [dbo].[partion_MoveScheme]
AS 
    BEGIN     
    
        DECLARE @nextpartition DATETIME,
            @curdate DATETIME
        SET @curdate = '2011-7-1'      
        SET @nextpartition = DATEADD(ms, -3,
                                     DATEADD(mm, DATEDIFF(m, 0, @curdate) + 6,
                                             0)) 
           
        DECLARE @movettime DATETIME
        SET @movettime = DATEADD(ms, -3,
                                 DATEADD(mm, DATEDIFF(m, 0, @curdate), 0)) 
        
        
        PRINT @movettime
        PRINT @nextpartition
    
        DECLARE @filegroupname VARCHAR(10)
        DECLARE @partionnum VARCHAR(1)
                
        SELECT  @filegroupname = fg.NAME,
                @partionnum = dds.destination_id
        FROM    ( ( ( sys.tables AS t
                      INNER JOIN sys.indexes AS i ON ( t.object_id = i.object_id )
                    )
                    INNER JOIN sys.partition_schemes AS ps ON ( i.data_space_id = ps.data_space_id )
                  )
                  INNER JOIN sys.destination_data_spaces AS dds ON ( ps.data_space_id = dds.partition_scheme_id )
                )
                INNER JOIN sys.filegroups AS fg ON dds.data_space_id = fg.data_space_id
        WHERE   ( t.name = 't_partitionTable' )
                AND ( i.index_id IN ( 0, 1 ) )
                AND dds.destination_id = $partition.pf_month(@movettime) 
                
        --SELECT   @filegroupname,    @partionnum         

    
        DECLARE @tempdate VARCHAR(6)
        SET @tempdate = CONVERT(VARCHAR(6), DATEADD(MM, -1, @curdate), 112)
       
        DECLARE @s VARCHAR(1000)
        SET @s = 'CREATE TABLE partition_t1_' + @tempdate + ' 
    (
    id  INT ,    
    ttime DATETIME
    )ON ' + @filegroupname
    
        PRINT ( @s )
        EXEC ( @s
            )
            
        SET @s = 'alter table t_partitionTable switch partition '
            + @partionnum + ' to  partition_t1_' + @tempdate
       
        PRINT ( @s )
        EXEC ( @s
            )
    
        SET @s = 'ALTER PARTITION FUNCTION pf_month() MERGE RANGE ('''
            + CONVERT(VARCHAR(23), @movettime, 121) + ''')'
        PRINT ( @s )
        
        EXEC ( @s
            )  
                
        PRINT ( 'merge' )
    
        SET @s = 'ALTER PARTITION SCHEME  ps_month next used '
            + @filegroupname
        
        PRINT ( @s )
        EXEC ( @s
            )        
    
        SET @s = 'ALTER PARTITION FUNCTION pf_month() split range('''
            + CONVERT(VARCHAR(23), @nextpartition, 121) + ''')'
        PRINT ( @s )
        EXEC ( @s
            )        
        
    END    
    
    /*
    

ALTER DATABASE db2
ADD  filegroup fg1

ALTER DATABASE db2
ADD  filegroup fg2

ALTER DATABASE db2
ADD  filegroup fg3

ALTER DATABASE db2
ADD  filegroup fg4

ALTER DATABASE db2
ADD  filegroup fg5

ALTER DATABASE db2
ADD  filegroup fg6

ALTER DATABASE db2
add FILE (NAME='fg1',filename='c:\a\fg1.ndf')
TO filegroup fg1

ALTER DATABASE db2
add FILE (NAME='fg2',filename='c:\a\fg2.ndf')
TO filegroup fg2

ALTER DATABASE db2
add FILE (NAME='fg3',filename='c:\a\fg3.ndf')
TO filegroup fg3


ALTER DATABASE db2
add FILE (NAME='fg4',filename='c:\a\fg4.ndf')
TO filegroup fg4

ALTER DATABASE db2
add FILE (NAME='fg5',filename='c:\a\fg5.ndf')
TO filegroup fg5


ALTER DATABASE db2
add FILE (NAME='fg6',filename='c:\a\fg6.ndf')
TO filegroup fg6


DROP  PARTITION FUNCTION pf_month
DROP PARTITION SCHEME  ps_month


CREATE  PARTITION FUNCTION pf_month(datetime)
AS  range LEFT FOR VALUES(
    '2011-4-30 23:59:59.997',
    '2011-5-31 23:59:59.997',
    '2011-6-30 23:59:59.997',
    '2011-7-31 23:59:59.997',
    '2011-8-31 23:59:59.997',
    '2011-9-30 23:59:59.997')


CREATE  PARTITION SCHEME  ps_month
AS  PARTITION pf_month TO (fg1,fg2,fg3,fg4,fg5,fg6,[primary]);


CREATE TABLE t_partitionTable ( id int ,ttime datetime default(getdate()))
ON [ps_month](ttime)

SELECT  * FROM t_partitionTable
*/

参考资料：

　　http://www.microsoft.com/china/technet/prodtechnol/sql/2005/realpart.mspx