Mssql 滑动分区实现方案

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

posted on 2011-05-02 14:45  Henry.Lau 阅读( ...) 评论( ...) 编辑 收藏

转载于:https://www.cnblogs.com/Henry1225/archive/2011/05/02/msql.html