-- Stored Procedure: dbo.MonthlyPartitionRollover -- Bill Wunder use admin GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MonthlyPartitionRollover]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[MonthlyPartitionRollover] GO CREATE PROCEDURE [dbo].[MonthlyPartitionRollover] @PlanName varchar(128) , @debug bit =1 AS /******************************************************************************************************* * admin.dbo.MonthlyPartitionRollover * Creator: bw * Date: 04-16-2004 * * Description: Process date or date represented character based monthly vertical partitioning plan * Notes: Must compile with SET QUOTED_IDENTIFIER ON! Use the following notes to generate a script to implement the Partition Plan Configuration script Creates a static config data set that can be reused going forward. Purpose: Create an ongoing vertical partitioning architecture for a table or group of tables - explicit interelationship not manditory but suggested. Need to maintain a specified number of historical partitions. Partitions are dropped as they age out of the active set. Partitiones are introduced to the active set before ther occur to reduce contention and processing time. For example, by creating partitions before their partitioning date column value occurs in time and by defining the check constraint used by the partitioned view as a fixed time interval rather than adding open ended partitions and then later altering the check constraint to be a fixed interval the time needed to alter the partitioning view is greatly reduced. The partition view is maintained as partitions are added and removed using ALTER VIEW. Each view should consists of n-number of a single identically structured table with names and explicitly named constraints rendered unique through introduction of a tool generated PartitionTag in all names. To start the partition, the legacy table will be the only table with kept data and will renamed to be the "Legacy" partition with a special openended check constraint to allow all rows prior to the end of the current month to be written and accessed via that table The rollover processing will be made aware of this "Lagacy" partition and will not remove this data until it has aged out of the active "PartitionsToKeep" set based on it's position as the currents months partition Required Inputs For each Partition Plan @PlanName - character data, should be descriptive but brief and avoid white space @PlanOwner - schema owner for all partition objects @PartitionsToKeep - number of populated partitions, up to and including the current partittion to persist in the database. @PartitionsToPrebuild - number of future partitions to pre-create and maintain in the partitioning view (Optional) @PartitionTagPlaceHolder - defaults to ######## - place holder strin pattern is embedded in the @TableDDLTemplate by the partition implementor in this script at each place in script where a database object must be uniquely identified between partition tables - table name, constraint names, index names Required Inputs For Each Partitioned View Define these values for each view included in the plan @ViewName - Name of the view. In new installation you may want to use an identifier that clealry indicates that this is a view. In existing applicaion it may be safest retro to use the existing table name as the view name. @PartitioningColumn - Each partition in a Partitioned view will have a range check constraint that will clearly and unambiguously identity the rows that go into each partition table. That column must be a part of the primary key of each table. The check constraint will be built on the column provided here. This column must be a datetime column or a character column where a datetime can be derived from the left most characters of the column. @TableNameCommonLiteral - Each partitioning table will be prefixed by this common literal that must follow the naming conventions of SQL Server. Each TableName will begin with this string and be suffixed with a numeric tag to indicate the year and month of the data contained in that partition. @TableDDLTemplate - This column must include the complete DDL statement for the partitioning tables in the view. All partition tables must be identical in structure. To insure that each table, constraint and index is unique withing the view, the PartitionTagPlaceHolder provided in the PartitionPlan table must be added to the script as a suffix in each place that the TableNameCommonLiteral occurs in the name of the table, constraints and indexes within the script. At Partition Table create time the PlaceHolder is repalced with a PartitionTag made up of the 4 digit year and 2 digit month that is contained in that Partition Table. -- Configuration Script Template use admin declare @PlanName varchar(128) , @DBName varchar(128) , @PlanOwner varchar(128) , @PartitionsToKeep smallint , @PartitionsToPrebuild smallint , @ViewName varchar(128) , @PartitioningColumn varchar(128) , @TableNameCommonLiteral varchar(128) , @TableDDLTemplate varchar(7500) set nocount on --Define the plan set @PlanName = ? set @PlanOwner = ? -- 'dbo' from SQL Server 2000 set @PartitionsToKeep = ? set @PartitionsToPrebuild = ? -- 2 recommended set @DBName = ? -- create the PartitionPlan metatables else abort exec admin.dbo.DBAInstallPartitionPlanMetaTables if @@error <> 0 raiserror('Error installing Partition Plan Metadata Tables',20,1) with log -- verify plan database else abort if db_name(@DBNAme) is null raiserror('Database "%s" for Partition Plan "%s" not available!',20,1, @DBName, @PlanName) with log --make and needed changes to existing tables here if not exists (select 1 from dbo.PartitionPlan where PlanName = @PlanName) insert dbo.PartitionPlan ( PlanName , DBName , PlanOwner , PartitionsToKeep , PartitionsToPrebuild , PartitionTagPlaceHolder , PlanNotes) values ( @PlanName , @DBName , @PlanOwner , @PartitionsToKeep , @PartitionsToPrebuild , default , null) -- define each view that will be included in the plan (repeat as necessary) set @ViewName = ? set @PartitioningColumn = ? set @TableNameCommonLiteral = ? set @TableDDLTemplate = ? if object_id(@DBName + '.' + @PlanOwner + '.' + @ViewName) is null raiserror('There is no existing view or table named %S.%S.',11,1, @PlanOwner, @ViewName) if not exists (select 1 from dbo.PartitionPlanView where ViewName = @ViewName) insert dbo.PartitionPlanView ( ViewName , PlanName , PartitioningColumn , TableNameCommonLiteral , TableDDLTemplate) values ( @ViewName , @PlanName , @PartitioningColumn , @TableNameCommonLiteral , @TableDDLTemplate) * * Usage: EXECUTE admin.dbo.MonthlyPartitionRollover 'orders',1 * * * Modifications: * Developer Name Date Brief Description * ------------------ -------- ------------------------------------------------------------ * ********************************************************************************************************/ --------------------------------------------- -- declare variables --------------------------------------------- declare @PlanOwner varchar(128) , @PartitionsToKeep smallint , @PartitionsToPrebuild smallint , @ViewName varchar(128) , @TableName varchar(128) , @TableScript nvarchar(4000) , @CheckConstraintScript nvarchar(4000) , @ViewChangedScript nvarchar(4000) , @ViewChanged bit , @ViewScript nvarchar(4000) , @DropTableScript nvarchar(4000) , @ViewBuilderCounter smallint , @NextIntervalOffset smallint , @NextBaseDt datetime , @DBName varchar(128) , @hr int , @er int , @FailedScript varchar(4000) --------------------------------------------- -- create temp tables --------------------------------------------- --------------------------------------------- -- set session variables --------------------------------------------- SET NOCOUNT ON SET ARITHABORT ON --------------------------------------------- -- body of stored procedure --------------------------------------------- select @DBName = DBName , @PlanOwner = PlanOwner , @PartitionsToKeep = PartitionsToKeep , @PartitionsToPrebuild = PartitionsToPrebuild from admin.dbo.PartitionPlan select @ViewName = (select top 1 ViewName from admin.dbo.PartitionPlanView where PlanName = @PlanName order by ViewName) while @ViewName is not null begin /* If partitioning has not yet been activated for this view, do it now. Fill in from the top (make the current table the max interval offset and let it percolate down to the 0 offeset) so that all specified tables to prebuild are created. Make the existing table the 0 offset and set it's check constraint to be anything less than the end of the current month process until the 0 IntervalOffset table covers the current month */ while (select PartitionEndDt from admin.dbo.PartitionPlanActiveTable where IntervalOffSet = 0 and ViewName = @ViewName) < getdate() or not exists (select 1 from admin.dbo.PartitionPlanActiveTable where IntervalOffSet = 0 and ViewName = @ViewName) begin -- find the next IntervalOffset in ascending order -- if we made it this far we know we need to add one select @NextIntervalOffset = max(IntervalOffset) from admin.dbo.PartitionPlanActiveTable where ViewName = @ViewName -- determine the correct base date for the next partition in the sequence select @NextBaseDt = dateadd(month,1,max(PartitionEndDt)) from admin.dbo.PartitionPlanActiveTable where ViewName = @ViewName and IntervalOffset = @NextIntervalOffset --decrement the interval offset update admin.dbo.PartitionPlanActiveTable set IntervalOffset = IntervalOffset - 1 where ViewName = @ViewName -- if new did not find anything in PartitionPlanActiveTable so init if still null if @NextIntervalOffset is null and @NextBaseDt is null begin set @NextIntervalOffset = @PartitionsToPrebuild set @NextBaseDt = getdate() end -- add a row insert admin.dbo.PartitionPlanActiveTable ( PlanName , ViewName , TableNameCommonLiteral , IntervalOffset , RecBaseDt) Select p.PlanName , v.ViewName , v.TableNameCommonLiteral , @NextIntervalOffset , @NextBaseDt from admin.dbo.PartitionPlan p join admin.dbo.PartitionPlanView v on p.PlanName = v.PlanName where v.ViewName = @ViewName and @NextIntervalOffset <= @PartitionsTOPrebuild -- does this do anything? if @@rowcount = 1 begin select @TableName = a.TableName , @TableScript = case when object_id(@DBName + '.' + @PlanOwner +'.' + @ViewName,'V') is null and object_id(@DBName + '.' + @PlanOwner +'.' + v.TableNameCommonLiteral,'U') is not null then 'use ' + @DBName + ' exec sp_rename ''' + v.TableNameCommonLiteral + ''', ''' + a.TableName + ''',''OBJECT''' else 'use ' + @DBName + ' if object_id(''' + @PlanOwner +'.' + a.TableName + ''',''U'') is null begin ' + replace(v.TableDDLTemplate, '########', a.PartitionTag) + ' end' end , @CheckConstraintScript = 'use ' + @DBName + ' if object_id(''ck_' + a.TableName + '__' + v.PartitioningColumn + ''') is null ' + ' alter table [' + @PlanOwner + '].[' + a.TableName + '] add constraint ck_' + a.TableName + '__' + v.PartitioningColumn + ' check (' + v.PartitioningColumn + case when object_id(@DBName + '.' + @PlanOwner +'.' + @ViewName,'V') is null and object_id(@DBName + '.' + @PlanOwner +'.' + v.TableNameCommonLiteral,'U') is not null then ' < ''' + cast(a.PartitionStartDt as varchar(25)) + ''')' else ' between ''' + cast(a.PartitionStartDt as varchar(25)) + ''' and ''' + cast(a.PartitionEndDt as varchar(25)) + ''')' end from admin.dbo.PartitionPlanActiveTable a join admin.dbo.PartitionPlanView v on a.ViewName = v.ViewName join dbo.PartitionPlan p on v.PlanName = p.PlanName where v.ViewName = @ViewName and a.IsCreated = 0 order by a.PartitionStartDt if @debug = 1 select @TableScript [@TableScript] exec @hr = sp_executesql @TableScript set @er = @@error if @hr <> 0 or @er <> 0 begin set @FailedScript = @TableScript goto ErrorHandler end if @debug = 1 select @CheckConstraintScript [@CheckConstraintScript] exec @hr = sp_executesql @CheckConstraintScript set @er = @@error if @hr <> 0 or @er <> 0 begin set @FailedScript = @CheckConstraintScript goto ErrorHandler end update admin.dbo.PartitionPlanActiveTable set IsCreated = 1 where ViewName = @ViewName and IsCreated = 0 and object_id(@DBName + '.' + @PlanOwner + '.' + TableName, 'U') is not null end -- added a new active table end -- current month not at 0 offset (or there is no 0 offset table) -- (re) create the view if everything worked and there are changes to incorporate into the view if exists (select 1 from dbo.PartitionPlanActiveTable where PlanName = @PlanName and ViewName = @ViewName and (IsCreated = 0 or object_id(@DBName + '.' + @PlanOwner + '.' + TableName, 'U') is null)) raiserror ('Partition table "%s" was not created for View "%s" of PartitionPlan "%s" in database "%s".' , 16 , 1 , @TableName , @ViewName , @PlanName , @DBName) else begin -- Find the first partition that must be in the view set @ViewBuilderCounter = 0 - @PartitionsToKeep if object_id(@DBName + '.' + @PlanOwner + '.' + @ViewName, 'V') is null set @ViewScript = 'use ' + @DBName + ' exec(''create view ' else set @ViewScript = 'use ' + @DBName + ' exec(''alter view ' set @ViewScript = @ViewScript + @PlanOwner + '.' + @ViewName + char(13) + char(10) + ' as ' + char(13) + char(10) while @ViewBuilderCounter <= @PartitionsToPrebuild begin -- here's where we could get into trouble if column sequnce is not idenitcal in all tables select @ViewScript = @ViewScript + 'select * from ' + @DBName + '.' + @PlanOwner + '.' + TableName + case when @ViewBuilderCounter = @PartitionsToPrebuild then ''')' else + char(13) + char(10) + 'union all ' + char(13) + char(10) end from dbo.PartitionPlanActiveTable where PlanName = @PlanName and ViewName = @ViewName and IntervalOffset = @ViewBuilderCounter set @ViewBuilderCounter = @ViewBuilderCounter + 1 end end -- all the tables for this view are created set @ViewChangedScript = 'use ' + @DBName + ' select @ViewChanged = 1 from INFORMATION_SCHEMA.VIEW_TABLE_USAGE v full outer join admin.dbo.PartitionPlanActiveTable p on v.VIEW_NAME = p.ViewName and v.TABLE_NAME = p.TableName where p.PlanName = @PlanName and p.ViewName = @ViewName and v.VIEW_SCHEMA = @PlanOwner and v.TABLE_SCHEMA = @PlanOwner and (v.TABLE_NAME is null or p.TableName is null)' exec @hr = sp_executesql @ViewChangedScript , N'@PlanName varchar(128), @ViewName varchar(128), @PlanOwner varchar(128), @ViewChanged bit OUTPUT' , @PlanName , @ViewName , @PlanOwner , @ViewChanged OUTPUT set @er = @@error if @hr <> 0 or @er <> 0 begin set @FailedScript = @ViewChangedScript goto ErrorHandler end if @ViewChanged = 1 begin if @debug = 1 select @ViewScript [@ViewScript] exec @hr = sp_executesql @ViewScript set @er = @@error if @hr <> 0 or @er <> 0 begin set @FailedScript = @ViewScript goto ErrorHandler end end -- view changed -- remove any tables that have been removed from the view -- if they are still in the view they cannot be dropped -- so as long as view exists we should be safe to proceed while exists (select 1 from dbo.PartitionPlanActiveTable where PlanName = @PlanName and ViewName = @ViewName and IntervalOffset < 0 - @PartitionsToKeep) and object_id(@DBName + '.' + @PlanOwner + '.' + @ViewName, 'V') is not null begin select @TableName = Tablename , @DropTableScript = 'drop table ' + @DBName + '.' + @PlanOwner + '.' + TableName from dbo.PartitionPlanActiveTable where PlanName = @PlanName and ViewName = @ViewName and IntervalOffset = (select min(IntervalOffset) from dbo.PartitionPlanActiveTable where IntervalOffset < 0 - @PartitionsToKeep) -- if archival processing is required before dropping, do it now -- this table is no longer part of the view so should be no contention -- between archival and application processing if @debug = 1 select @DropTableScript exec @hr = sp_executesql @DropTableScript set @er = @@error if @hr <> 0 or @er <> 0 begin set @FailedScript = @DropTableScript goto ErrorHandler end -- raiserror if a drop fails and but go ahead and remove the row -- to avoid an endless loop possibility here -- the message will only get raised once so a possibility remains that -- junk tables could get left in the database if object_id(@DBName + '.' + @PlanOwner + '.' + @TableName, 'U') is not null raiserror ('Unable to drop Table "%s" for View "%s" of PartitionPlan "%s" in database "%s".' , 16 , 1 , @TableName , @ViewName , @PlanName , @DBName) delete dbo.PartitionPlanActiveTable where PlanName = @PlanName and ViewName = @ViewName and TableName = @TableName end select @ViewName = (select top 1 ViewName from dbo.PartitionPlanView where PlanName = @PlanName and ViewName > @ViewName) end -- process a view -- make sure no missing tables or views slipped through the cracks if exists (select 1 from dbo.PartitionPlanActiveTable where PlanName = @PlanName and (IsCreated = 0 or object_id(@DBName + '.' + @PlanOwner + '.' + TableName, 'U') is null)) raiserror ('Some partition tables were not created for PartitionPlan "%s" in database "%s".' , 16 , 1 , @PlanName , @DBName) if exists (select 1 from dbo.PartitionPlanView where PlanName = @PlanName and object_id(@DBName + '.' + @PlanOwner + '.' + ViewName, 'V') is null) raiserror ('Some partition views are missing for PartitionPlan "%s" in database "%s".' , 16 , 1 , @PlanName , @DBName) Return ErrorHandler: Raiserror('Script Execution Failed: %s',16,1,@FailedScript) Return -1 GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO