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.
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>