关于SQL2008分区表滑动窗口方案的应用策略

接着上次的分区表来说..创建好了后..就得有一个脚本来管理这些分区表的及时管理了。。

呵呵。。现在直接看脚本吧。。。

参考了网上的一些方法，结合自己的系统应用。。

由于本次分区的字段并非主键，因此遇到一些小问题。。

下面虽然能实现滑动窗口方案，但是总觉得通过删除索引在创建索引的方式来实现有点浪费。。。希望有高人能有有更好的意见。。

谢谢。。。

/*
 * 1.创建两个函数MinFileGroupsByPartitionBoundary,获取最小的日期分界值和分区号
 */
 IF EXISTS(SELECT * FROM SYSOBJECTS WHERE name='MinFileGroupsByPartitionBoundary' AND type='IF')
 BEGIN
	DROP FUNCTION MinFileGroupsByPartitionBoundary
 END
 GO
CREATE FUNCTION MinFileGroupsByPartitionBoundary() RETURNS TABLE AS RETURN (
            SELECT top 1 sf.name AS FileGroupName, sprv.value AS Boundary
            FROM SYS.PARTITION_SCHEMES AS SPS
            INNER JOIN SYS.PARTITION_FUNCTIONS AS SPF ON SPS.FUNCTION_ID = SPF.FUNCTION_ID
            INNER JOIN SYS.DESTINATION_DATA_SPACES AS SDD ON SDD.PARTITION_SCHEME_ID = SPS.DATA_SPACE_ID AND SDD.DESTINATION_ID <= SPF.FANOUT
            INNER JOIN SYS.PARTITION_RANGE_VALUES SPRV ON SPRV.FUNCTION_ID = SPF.FUNCTION_ID AND SPRV.BOUNDARY_ID = SDD.DESTINATION_ID
            INNER JOIN SYS.FILEGROUPS AS SF ON SF.DATA_SPACE_ID = SDD.DATA_SPACE_ID
            WHERE SPS.NAME= 'FROMCITYCODEPARTITION' ORDER BY BOUNDARY )
GO

/*
 *创建最大的日期分界值和分区号MaxFileGroupsByPartitionBoundary
 */
 
 IF EXISTS(SELECT * FROM SYSOBJECTS WHERE name='MaxFileGroupsByPartitionBoundary' AND type='IF')
 BEGIN
	DROP FUNCTION MaxFileGroupsByPartitionBoundary
 END
 GO
CREATE FUNCTION MaxFileGroupsByPartitionBoundary() RETURNS TABLE AS RETURN (
      SELECT top 1 sf.name AS FileGroupName, sprv.value AS Boundary
      FROM SYS.PARTITION_SCHEMES AS SPS
	  INNER JOIN SYS.PARTITION_FUNCTIONS AS SPF ON SPS.FUNCTION_ID = SPF.FUNCTION_ID
	  INNER JOIN SYS.DESTINATION_DATA_SPACES AS SDD ON SDD.PARTITION_SCHEME_ID = SPS.DATA_SPACE_ID AND SDD.DESTINATION_ID <= SPF.FANOUT
	  INNER JOIN SYS.PARTITION_RANGE_VALUES SPRV ON SPRV.FUNCTION_ID = SPF.FUNCTION_ID AND SPRV.BOUNDARY_ID = SDD.DESTINATION_ID
	  INNER JOIN SYS.FILEGROUPS AS SF ON SF.DATA_SPACE_ID = SDD.DATA_SPACE_ID
	  WHERE SPS.NAME= 'FROMCITYCODEPARTITION' ORDER BY BOUNDARY DESC)
GO

--  删除分区表原来的索引

IF EXISTS(SELECT * FROM SYSINDEXES WHERE NAME='PK_AIR_ID' AND ID IN(SELECT ID FROM SYSOBJECTS WHERE NAME='BA_CACHE_AIR_RESULT'))
BEGIN
ALTER TABLE BA_CACHE_AIR_RESULT DROP constraint PK_AIR_ID
END
GO

IF EXISTS(SELECT * FROM SYSINDEXES WHERE NAME='IX_001' AND ID IN(SELECT ID FROM SYSOBJECTS WHERE NAME='BA_CACHE_AIR_RESULT'))
BEGIN
DROP INDEX IX_001 ON BA_CACHE_AIR_RESULT
END
GO

IF EXISTS(SELECT * FROM SYSINDEXES WHERE NAME='IX_002' AND ID IN(SELECT ID FROM SYSOBJECTS WHERE NAME='BA_CACHE_AIR_RESULT'))
BEGIN
DROP INDEX IX_002 ON BA_CACHE_AIR_RESULT
END
GO


--申明三个变量，下面会用到。

DECLARE 
@FileGroupName VARCHAR(50),
@MinBoundary DATETIME,
@MaxBoundary DATETIME
SELECT @FileGroupName = FileGroupName,@MinBoundary=CONVERT(DATETIME,Boundary) FROM MinFileGroupsByPartitionBoundary()
SELECT @MaxBoundary = CONVERT(DATETIME,Boundary) FROM MaxFileGroupsByPartitionBoundary()

/*
 2.创建一个临时的表，暂名为TEMP_LOG，用于存储要迁出的数据，即2个月内最早的一天的数据。
 ［注意］这里创建的临时的表，需要与要迁出的数据在同一个分区里，如1月份数据是存放在分区CITY01里的话，则创建临时表的时候，
 也要创建在CITY01上。
 */

   IF EXISTS(SELECT * FROM SYSOBJECTS WHERE name='TEMP_LOG' AND type='U')
   DROP TABLE TEMP_LOG

    DECLARE @CreateText NVARCHAR(MAX)
    SET @CreateText = 'CREATE TABLE [dbo].[TEMP_LOG](
	[AIR_ID] [nvarchar](50) NOT NULL,
	[TRIPINDEX] [nvarchar](50) NULL,
	[AIR_AGENT_ID] [varchar](36) NULL,
	[AIR_FROM_AIRPORT_CODE] [nvarchar](10) NULL,
	[AIR_TO_AIRPORT_CODE] [nvarchar](10) NULL,
	[AIR_FROM_AIRPORT_NAME] [nvarchar](50) NULL,
	[AIR_TO_AIRPORT_NAME] [nvarchar](50) NULL,
	[AIR_FROM_CITY_NAME] [nvarchar](50) NULL,
	[AIR_TO_CITY_NAME] [nvarchar](50) NULL,
	[AIR_FROM_DATETIME] [datetime] NULL,
	[AIR_TO_DATETIME] [datetime] NULL,
	[AIR_AIRFARE_PRICE] [decimal](18, 2) NULL,
	[AIR_ADULT_DISCOUNT] [decimal](18, 2) NULL,
	[AIR_CHILD_DISCOUNT] [decimal](18, 2) NULL,
	[AIR_BABY_DISCOUNT] [decimal](18, 2) NULL,
	[AIR_ADULT_DISCOUNT_ORDER] [tinyint] NULL,
	[AIR_TAX_FEE] [nvarchar](40) NULL,
	[AIR_OIL_FEE] [nvarchar](40) NULL,
	[AIR_AIRLINES_INFO] [nvarchar](50) NULL,
	[AIR_FLIGHT_CODE] [nvarchar](50) NULL,
	[AIR_FLIGHT_TYPE] [nvarchar](50) NULL,
	[AIR_CABIN_CODE] [nvarchar](50) NULL,
	[AIR_CABIN_NAME] [nvarchar](50) NULL,
	[AIR_AIRPLANE_INFO] [nvarchar](1000) NULL,
	[AIR_TUIPIAO_DESC] [nvarchar](1000) NULL,
	[AIR_GENGGAI_DESC] [nvarchar](1000) NULL,
	[AIR_QIANZHUAN_DESC] [nvarchar](1000) NULL,
	[AIR_INSURANCE_FEE] [decimal](18, 0) NULL,
	[AIR_TICKET_COUNT] [nvarchar](10) NULL,
	[AIR_LEG_TYPE] [nvarchar](1) NULL,
	[AIR_TRANSIT_AIRPORT_CODE] [nvarchar](50) NULL,
	[AIR_TRANSIT_AIRPORT_NAME] [nvarchar](50) NULL,
	[AIR_TRANSIT_CITY_NAME] [nvarchar](50) NULL,
	[AIR_TRANSIT_TO_DATETIME] [nvarchar](50) NULL,
	[AIR_TRANSIT_FROM_DATETIME] [nvarchar](50) NULL,
	[AIR_TRANSIT_AIRLINES_INFO] [nvarchar](50) NULL,
	[AIR_TRANSIT_FLIGHT_CODE] [nvarchar](50) NULL,
	[AIR_TRANSIT_AIRPLANE_INFO] [nvarchar](1000) NULL,
	[AIR_TRANSIT_TAX_FEE] [nvarchar](20) NULL,
	[AIR_TRANSIT_OIL_FEE] [nvarchar](20) NULL,
	[AIR_FROM_CITY_CODE] [nvarchar](10) NULL,
	[AIR_TO_CITY_CODE] [nvarchar](10) NULL,
	[AIR_AFTER_DISCOUNT_ADULT_PRICE] [decimal](18, 2) NULL,
	[AIR_AFTER_DISCOUNT_CHILD_PRICE] [decimal](18, 2) NULL,
	[AIR_AFTER_DISCOUNT_BABY_PRICE] [decimal](18, 2) NULL,
	[AIR_AIRLINE_RULE_ID] [varchar](5) NULL,
	[AIR_TOURCODE_ID] [varchar](20) NULL,
	[AIR_TJ] [nvarchar](1) NULL,
	[AIR_FAREBASIS] [nvarchar](100) NULL,
	[AIR_CODESHARE] [varchar](5) NULL,
	[AIR_CREATETIME] [datetime] NULL,
	[AIR_OFTEN] INT NULL
	) ON ' + @FileGroupName + ';'

       EXEC SP_EXECUTESQL @CreateText
    
      /*
       3.这个时候就可以迁出分界值最小的那个分区里的数据了。
       */

        ALTER TABLE BA_CACHE_AIR_RESULT SWITCH PARTITION 1 TO TEMP_LOG

		/*
          4.删除临时表temp_Log，这个时候，迁出的数据也删除掉了，实现了将最小分区值里的数据进行清理的目的。
		*/
		
        TRUNCATE TABLE TEMP_LOG
        DROP TABLE TEMP_LOG



		/*
          5.当最小的日期分界值的分区数据清理掉后，还需要将分界值也去掉。
		*/
        ALTER PARTITION FUNCTION [FromCityCodePartitionFunction]() MERGE RANGE(@MinBoundary)



      /*
          6.去掉最小分界值后，相应的分区也就空出来了，这时候，就可以将空出来的这个分区置为可用状态了。
		*/
        SET @CreateText = 'ALTER PARTITION SCHEME [FromCityCodePartition] NEXT USED ' + @FileGroupName

		EXEC SP_EXECUTESQL @CreateText


		/*
          7.将最后的一个分区，在这里相当于我们的CITY09，对它进行拆分，以什么拆分呢，就以我们的下一个日期分界值，即’20070701 00:00:00.000’，这样在第6步空出来的分区就被用来存储’ 2007-07-01 00:00:00.000’以后的数据了。
		*/
		ALTER PARTITION FUNCTION [FromCityCodePartitionFunction]() SPLIT RANGE (DATEADD(DAY,2,@MaxBoundary))


--创建主键，但不设为聚集索引   
ALTER TABLE BA_CACHE_AIR_RESULT ADD CONSTRAINT PK_AIR_ID PRIMARY KEY NONCLUSTERED   
(   
    AIR_ID ASC   
) ON [PRIMARY]
GO

IF EXISTS(
SELECT * FROM SYSINDEXES WHERE name='IX_001' AND id IN(SELECT id FROM sysobjects WHERE name='BA_CACHE_AIR_RESULT')
)
BEGIN
 DROP INDEX IX_001 ON BA_CACHE_AIR_RESULT
END
CREATE NONCLUSTERED INDEX [IX_001] ON [dbo].[BA_CACHE_AIR_RESULT] 
(
	[AIR_FROM_CITY_CODE] ASC,
	[AIR_TO_CITY_CODE] ASC,
	[AIR_FROM_DATETIME] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, 
IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, 
ALLOW_PAGE_LOCKS  = ON) 
GO


IF EXISTS(SELECT * FROM SYSINDEXES WHERE name='IX_002' AND id IN(SELECT id FROM sysobjects WHERE name='BA_CACHE_AIR_RESULT'))
BEGIN
 DROP INDEX IX_002 ON BA_CACHE_AIR_RESULT
END
/****** Object:  Index [IX_002]    Script Date: 03/09/2011 19:03:52 ******/
CREATE NONCLUSTERED INDEX [IX_002] ON [dbo].[BA_CACHE_AIR_RESULT] 
(
	[AIR_AGENT_ID] ASC,
	[AIR_FLIGHT_CODE] ASC,
	[AIR_TRANSIT_FROM_DATETIME] ASC,
	[AIR_FROM_CITY_CODE] ASC,
	[AIR_TO_CITY_CODE] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, 
IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, 
ALLOW_PAGE_LOCKS  = ON) 
GO


/*


SELECT PARTITION = $PARTITION.FROMCITYCODEPARTITIONFUNCTION(AIR_FROM_DATETIME)
      ,ROWS      = COUNT(*)
      ,MINVAL    = MIN(AIR_FROM_DATETIME)
      ,MAXVAL    = MAX(AIR_FROM_DATETIME)
  FROM DBO.BA_CACHE_AIR_RESULT
 GROUP BY $PARTITION.FROMCITYCODEPARTITIONFUNCTION(AIR_FROM_DATETIME)
 ORDER BY PARTITION
GO


*/