Revisions Spec: Drop and Create Indexes

The following element, contained within a revisions spec, contains embedded Transact-SQL. The deployment utility runs the Transact-SQL to create a procedure which drops and creates indexes. The CREATE PROCEDURE statement establishes a procedure to be used to create or drop indices from a table. For more information about the CREATE PROCEDURE statement, see Microsoft's MSDN article at CREATE PROCEDURE (Transact-SQL).

The Transact-SQL specifies that if no parameter is passed to the stored procedure, to throw an error. The parameter provided to execute the stored procedure must be 1 or 0. 1 indicates to create indexes and 0 indicates to drop indexes. For more information about the CREATE INDEX statement, see Microsoft's MSDN article at CREATE INDEX (Transact-SQL). For information specifically about nonclustered indexes, see Microsoft's MSDN article at Creating Nonclustered Indexes.

Note: To maintain consistent naming that avoids conflicts with out-of-the-box tables, apply the _EXT suffix to names for extension tables. If the extension is for a staging table, also apply the _STAGE suffix. Always apply _EXT first. Appropriate suffixes are _EXT and _EXT_STAGE. For the create and drop indices procedure, apply the CREATE_OR_DROP_ prefix and the _INDICES suffix.

  <DBRevision ID="20">
    <ExecuteSql>
      <![CDATA[	
create procedure [BBDW].[CREATE_OR_DROP_DIM_CONSTITUENTADDRESS_EXT_INDICES]
  @CREATE_OR_DROP bit --1 to create, 0 to drop.
  as
  set nocount on;

  if @CREATE_OR_DROP is null
    raiserror('@CREATE_OR_DROP must be 1 or 0 in [BBDW].[CREATE_OR_DROP_DIM_CONSTITUENTADDRESS_EXT_INDICES]',16,10);

  if @CREATE_OR_DROP =1
  begin
  --create

    if  [BBDW].[UFN_INDEXEXISTS]('IX_DIM_CONSTITUENTADDRESS_EXT_ADDRESSSYSTEMID') = 0
      create nonclustered index [IX_DIM_CONSTITUENTADDRESS_EXT_ADDRESSSYSTEMID] on [BBDW].[DIM_CONSTITUENTADDRESS_EXT] ([ADDRESSSYSTEMID]) on [BBRPT_DIMIDXGROUP]
      
    if  [BBDW].[UFN_INDEXEXISTS]('IX_DIM_CONSTITUENTADDRESS_EXT_ADDRESSTYPE') = 0
      create nonclustered index [IX_DIM_CONSTITUENTADDRESS_EXT_ADDRESSTYPE] on [BBDW].[DIM_CONSTITUENTADDRESS_EXT] ([ADDRESSTYPE]) on [BBRPT_DIMIDXGROUP];      
      
    if  [BBDW].[UFN_INDEXEXISTS]('IX_DIM_CONSTITUENTADDRESS_EXT_CONSTITUENTDIMID') = 0
      create nonclustered index [IX_DIM_CONSTITUENTADDRESS_EXT_CONSTITUENTDIMID] on [BBDW].[DIM_CONSTITUENTADDRESS_EXT] ([CONSTITUENTDIMID]) on [BBRPT_DIMIDXGROUP];      
      
    if  [BBDW].[UFN_INDEXEXISTS]('IX_DIM_CONSTITUENTADDRESS_EXT_SOURCEDIMID') = 0
      create nonclustered index [IX_DIM_CONSTITUENTADDRESS_EXT_SOURCEDIMID] on [BBDW].[DIM_CONSTITUENTADDRESS_EXT] ([SOURCEDIMID]) on [BBRPT_DIMIDXGROUP];      

  end
  else
  begin
  --drop

    if  [BBDW].[UFN_INDEXEXISTS]('IX_DIM_CONSTITUENTADDRESS_EXT_ADDRESSSYSTEMID') = 1
     drop index [IX_DIM_CONSTITUENTADDRESS_EXT_ADDRESSSYSTEMID] on [BBDW].[DIM_CONSTITUENTADDRESS_EXT];
     
    if  [BBDW].[UFN_INDEXEXISTS]('IX_DIM_CONSTITUENTADDRESS_EXT_ADDRESSTYPE') = 1
     drop index [IX_DIM_CONSTITUENTADDRESS_EXT_ADDRESSTYPE] on [BBDW].[DIM_CONSTITUENTADDRESS_EXT];
     
    if  [BBDW].[UFN_INDEXEXISTS]('IX_DIM_CONSTITUENTADDRESS_EXT_CONSTITUENTDIMID') = 1
     drop index [IX_DIM_CONSTITUENTADDRESS_EXT_CONSTITUENTDIMID] on [BBDW].[DIM_CONSTITUENTADDRESS_EXT];
     
    if  [BBDW].[UFN_INDEXEXISTS]('IX_DIM_CONSTITUENTADDRESS_EXT_SOURCEDIMID') = 1
     drop index [IX_DIM_CONSTITUENTADDRESS_EXT_SOURCEDIMID] on [BBDW].[DIM_CONSTITUENTADDRESS_EXT];

  end
      ]]>
    </ExecuteSql>
  </DBRevision>