Use "15" to number the ID for that revision.
<DBRevision ID="15"> </DBRevision>
Within <DBRevision ID="15">, add element to embed Transact-SQL code.
<ExecuteSql>
<![CDATA[
]]>
</ExecuteSql>
For more information about CDATA sections, see Microsoft's MSDN article at CDATA Sections [XML Standards].
Within that, add Transact-SQL code to create a stored procedure.
create procedure [BBDW].[CREATE_OR_DROP_FACT_INTERACTIONRESPONSE_EXT_INDICES] @CREATE_OR_DROP bit --1 to create, 0 to drop.
For the procedure name, use the convention CREATE_OR_DROP_<Table name>_INDICES.
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.
@CREATE_OR_DROP is a parameter used to indicate which actions the procedure takes (create or drop indexes).
For more information about the CREATE PROCEDURE statement, see Microsoft's MSDN article at CREATE PROCEDURE (Transact-SQL). Parameters are also discussed in the article.
Insert an AS clause and indicate to turn off extraneous SQL Server messages.
as set nocount on;
This is listed as a best practice by Microsoft in the MSDN article at CREATE PROCEDURE (Transact-SQL). For more information about SET NOCOUNT, see Microsoft's MSDN article at SET NOCOUNT (Transact-SQL).
Following that, in the body of the procedure, add this logic to create an index on an ID column when the parameter indicates to create indexes:
if @CREATE_OR_DROP = 1
begin
--create
if [BBDW].[UFN_INDEXEXISTS]('IX_FACT_INTERACTIONRESPONSE_EXT_INTERACTIONRESPONSESYSTEMID') = 0
create index [IX_FACT_INTERACTIONRESPONSE_EXT_INTERACTIONRESPONSESYSTEMID] on [BBDW].[FACT_INTERACTIONRESPONSE_EXT]([INTERACTIONRESPONSESYSTEMID]) on [BBRPT_FACTIDXGROUP];
end
[UFN_INDEXEXISTS] is a scalar-valued function in a BBDW database that determines whether indexes exist for the given parameter. For more information about scalar-valued functions, see Microsoft's MSDN article at Types of Functions.
For more information about the CREATE INDEX statement, see Microsoft's MSDN article at CREATE INDEX (Transact-SQL).
Add CREATE INDEX statements for these columns:
[INTERACTIONFACTID]
[RESPONSEDIMID]
[INTERACTIONRESPONSEDATEDIMID]
Add a corresponding section to drop indexes. Place it as an ELSE clause of the IF statement that uses the @CREATE_OR_DROP parameter. The first DROP INDEX is shown below.
else
begin
--drop
if [BBDW].[UFN_INDEXEXISTS]('IX_FACT_INTERACTIONRESPONSE_EXT_INTERACTIONRESPONSESYSTEMID') = 1
drop index [IX_FACT_INTERACTIONRESPONSE_EXT_INTERACTIONRESPONSESYSTEMID] on [BBDW].[FACT_INTERACTIONRESPONSE_EXT];
end
For more information about the DROP INDEX statement, see Microsoft's MSDN article at DROP INDEX (Transact-SQL).
Add DROP INDEX statements for these columns:
[INTERACTIONFACTID]
[RESPONSEDIMID]
[INTERACTIONRESPONSEDATEDIMID]
Add DROP INDEX statements for these columns:
[INTERACTIONFACTID]
[RESPONSEDIMID]
[INTERACTIONRESPONSEDATEDIMID]
Save your revisions file.
The revision should look like this:
<DBRevision ID="15">
<ExecuteSql>
<![CDATA[
create procedure [BBDW].[CREATE_OR_DROP_FACT_INTERACTIONRESPONSE_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_FACT_INTERACTIONRESPONSE_INDICES]',16,10);
if @CREATE_OR_DROP = 1
begin
--create
if [BBDW].[UFN_INDEXEXISTS]('IX_FACT_INTERACTIONRESPONSE_EXT_INTERACTIONRESPONSESYSTEMID') = 0
create index [IX_FACT_INTERACTIONRESPONSE_EXT_INTERACTIONRESPONSESYSTEMID] on [BBDW].[FACT_INTERACTIONRESPONSE_EXT]([INTERACTIONRESPONSESYSTEMID]) on [BBRPT_FACTIDXGROUP];
if [BBDW].[UFN_INDEXEXISTS]('IX_FACT_INTERACTIONRESPONSE_EXT_INTERACTIONFACTID') = 0
create index [IX_FACT_INTERACTIONRESPONSE_EXT_INTERACTIONFACTID] on [BBDW].[FACT_INTERACTIONRESPONSE_EXT]([INTERACTIONFACTID]) on [BBRPT_FACTIDXGROUP];
if [BBDW].[UFN_INDEXEXISTS]('IX_FACT_INTERACTIONRESPONSE_EXT_RESPONSEDIMID') = 0
create index [IX_FACT_INTERACTIONRESPONSE_EXT_RESPONSEDIMID] on [BBDW].[FACT_INTERACTIONRESPONSE_EXT]([RESPONSEDIMID]) on [BBRPT_FACTIDXGROUP];
if [BBDW].[UFN_INDEXEXISTS]('IX_FACT_INTERACTIONRESPONSE_EXT_INTERACTIONRESPONSEDATEDIMID') = 0
create index [IX_FACT_INTERACTIONRESPONSE_EXT_INTERACTIONRESPONSEDATEDIMID] on [BBDW].[FACT_INTERACTIONRESPONSE_EXT]([INTERACTIONRESPONSEDATEDIMID]) on [BBRPT_FACTIDXGROUP];
end
else
begin
--drop
if [BBDW].[UFN_INDEXEXISTS]('IX_FACT_INTERACTIONRESPONSE_EXT_INTERACTIONRESPONSESYSTEMID') = 1
drop index [IX_FACT_INTERACTIONRESPONSE_EXT_INTERACTIONRESPONSESYSTEMID] on [BBDW].[FACT_INTERACTIONRESPONSE];
if [BBDW].[UFN_INDEXEXISTS]('IX_FACT_INTERACTIONRESPONSE_EXT_INTERACTIONFACTID') = 1
drop index [IX_FACT_INTERACTIONRESPONSE_EXT_INTERACTIONFACTID] on [BBDW].[FACT_INTERACTIONRESPONSE_EXT];
if [BBDW].[UFN_INDEXEXISTS]('IX_FACT_INTERACTIONRESPONSE_EXT_RESPONSEDIMID') = 1
drop index [IX_FACT_INTERACTIONRESPONSE_EXT_RESPONSEDIMID] on [BBDW].[FACT_INTERACTIONRESPONSE_EXT];
if [BBDW].[UFN_INDEXEXISTS]('IX_FACT_INTERACTIONRESPONSE_EXT_INTERACTIONRESPONSEDATEDIMID') = 1
drop index [IX_FACT_INTERACTIONRESPONSE_EXT_INTERACTIONRESPONSEDATEDIMID] on [BBDW].[FACT_INTERACTIONRESPONSE_EXT];
end
]]>
</ExecuteSql>
</DBRevision>