-- Stored Procedure: dbo.DBAInstallPartitionPlanMetaTables -- 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].[DBAInstallPartitionPlanMetaTables]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[DBAInstallPartitionPlanMetaTables] GO CREATE PROCEDURE [dbo].[DBAInstallPartitionPlanMetaTables] @DropExisting varchar(5) = 'false' AS /******************************************************************************************************* * admin.dbo.DBAInstallPartitionPlanMetaTables * Creator: bw * Date: 04-14-2004 * * Description: Create the tables necessary to implement a Monthly partitioning plan * Notes: need quoted Identifier ON to create computed columns * * Purpose: Create an ongoing vertical (Monthly) partitioning architecture for a table or collection * of tables in one user database on one server under a unified partitioning plan - it is * intended that the tables are a logically related group that should all be maintain online * for a specified number of historical monthly partitions and able to support all select, * insert, update and delete operation. Partitions will be dropped as they age out of the * active set. Partitiones are introduced to the active set a specified number of vertical * partitions before they should expect to see data. This is to reduce contention and * processing time by insuring that no data must be checked when the check constraint is * created for a given partition. 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 will 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. Ideally the view name will be the same literal that is * prepended to the PartitionTag of each table, though the use of a view name literal different * from the table name common literal * * Usage: EXECUTE admin.dbo.DBAInstallPartitionPlanMetaTables 'true' * * * Modifications: * Developer Name Date Brief Description * ------------------ -------- ------------------------------------------------------------ * ********************************************************************************************************/ --------------------------------------------- -- declare variables --------------------------------------------- --------------------------------------------- -- create temp tables --------------------------------------------- create table #settings(Opt varchar(128), Val varchar(128)) --------------------------------------------- -- set session variables --------------------------------------------- SET NOCOUNT ON /* Quoted identifier must be set on before the proc is compiled! When creating and manipulating indexes on computed columns or indexed views, the SET options ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, ANSI_PADDING, and ANSI_WARNINGS must be set to ON. The option NUMERIC_ROUNDABORT must be set to OFF */ insert #settings exec sp_executesql N'dbcc useroptions' if not exists (select 1 from #settings where opt = 'quoted_identifier' and val = 'SET') raiserror ('quoted_identifier must be on when creating this procedure',18,1) SET ARITHABORT ON SET CONCAT_NULL_YIELDS_NULL ON SET ANSI_NULLS ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON SET NUMERIC_ROUNDABORT OFF --------------------------------------------- -- body of stored procedure --------------------------------------------- if @DropExisting = 'true' begin drop table admin.dbo.PartitionPlanActiveTable drop table admin.dbo.PartitionPlanView drop table admin.dbo.PartitionPlan end if object_id('admin.dbo.PartitionPlan','U') is null create table admin.dbo.PartitionPlan ( PlanName varchar(128) not null , DBName varchar(128) not null , PlanOwner varchar(128) not null , PartitionsToKeep smallint not null -- must be signed int , PartitionsToPrebuild smallint not null -- must be signed int , PartitionTagPlaceHolder char(8) not null constraint dft_PartitionPlan__PartitionTagPlaceHolder default '########' , PlanNotes varchar(7500) , constraint pkc_PartitionPlan__PlanName primary key (PlanName)) if object_id('admin.dbo.PartitionPlanView','U') is null create table admin.dbo.PartitionPlanView ( ViewName varchar(128) not null , PlanName varchar(128) not null , PartitioningColumn varchar(128) not null , TableNameCommonLiteral varchar(128) not null , TableDDLTemplate varchar(7500) not null , constraint pkc_PartitionPlanItem__ViewName primary key (ViewName) , constraint fk_PartitionPlanItem__PlanName__TO__PartitionPlan__PlanName foreign key (PlanName) references PartitionPlan(PlanName)) if object_id('admin.dbo.PartitionPlanActiveTable','U') is null create table admin.dbo.PartitionPlanActiveTable ( PlanName varchar(128) not null , ViewName varchar(128) not null , TableName as TableNameCommonLiteral + convert(varchar(6),RecBaseDt,112) , TableNameCommonLiteral varchar(128) not null , IntervalOffset smallint , IsCreated bit constraint dft_PartitionPlanActiveTable__IsCreated default 0 , PartitionStartDt as cast(convert(varchar(8),RecBaseDt,102) + '01 ' as datetime) , PartitionEndDt as cast(convert(varchar(8),dateadd(month,1,RecBaseDt),102) + '01 23:59:59' as datetime) - 1 , PartitionTag as convert(varchar(6),RecBaseDt,112) , RecBaseDt datetime not null , RecCreatedDt datetime constraint dft_PartitionPlanActiveTables__RecCreatedDt default getdate() , constraint ukc_PartitionPlanActiveTables__ViewName__PlanName__TableName unique clustered (ViewName,PlanName,TableName)) RETURN GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO