关于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 */