USP_MKTSEGMENTATIONACTIVATE_ACTIVATE

CREATE procedure dbo.[USP_MKTSEGMENTATIONACTIVATE_ACTIVATE]
(
  @SEGMENTATIONID uniqueidentifier,
  @ISEXCLUSION bit = 0
)
as
  set nocount on;

  declare @SQL as nvarchar(max);
  declare @DATATABLE nvarchar(128);
  declare @MAILINGTYPECODE tinyint;
  declare @INDEXNAME nvarchar(128);
  declare @USECOMPRESSION bit = (case when serverproperty('engineedition') = 3 then 1 else 0 end);

  begin try
    select
      @DATATABLE = case 
        when @ISEXCLUSION = 1 then dbo.[UFN_MKTSEGMENTATIONACTIVATEEXCLUSION_MAKETABLENAME]([ID]) 
        else dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME]([ID]) end,
      @MAILINGTYPECODE = [MAILINGTYPECODE]
    from dbo.[MKTSEGMENTATION]
    where [ID] = @SEGMENTATIONID;


    /* Rebuild the clustered index first on DonorQueryViewCatalogID and DonorID to clean up the fragmentation from activating */
    if @MAILINGTYPECODE in (1, 5)
      set @INDEXNAME = 'IX_' + @DATATABLE + '_DONORQUERYVIEWCATALOGID_REVENUEID_DONORID';
    else if @MAILINGTYPECODE = 2
      set @INDEXNAME = 'IX_' + @DATATABLE + '_DONORQUERYVIEWCATALOGID_MEMBERSHIPID_DONORID';
    else if @MAILINGTYPECODE = 3
      set @INDEXNAME = 'IX_' + @DATATABLE + '_DONORQUERYVIEWCATALOGID_SPONSORSHIPID_DONORID';
    else
      set @INDEXNAME = 'IX_' + @DATATABLE + '_DONORQUERYVIEWCATALOGID_DONORID';
    exec dbo.[USP_MKTCOMMON_REBUILDINDEX] @DATATABLE, @INDEXNAME, 100, 1;


    --We want the index fillfactor to be 100% to save space since we are now done working with this table.


    /* Create an index on the SegmentID */
    set @SQL = 'create nonclustered index [IX_' + @DATATABLE + '_SEGMENTID] on [dbo].[' + @DATATABLE + '] ([SEGMENTID] asc) with (PAD_INDEX  = off, SORT_IN_TEMPDB = on, DROP_EXISTING = off, IGNORE_DUP_KEY = off, ONLINE = off, FILLFACTOR = 100' + (case when @USECOMPRESSION = 1 then ', DATA_COMPRESSION = PAGE' else '' end) + ') on [IDXGROUP]';
    exec (@SQL);

    /* Create an index on the TestSegmentID */
    set @SQL = 'create nonclustered index [IX_' + @DATATABLE + '_TESTSEGMENTID] on [dbo].[' + @DATATABLE + '] ([TESTSEGMENTID] asc) with (PAD_INDEX  = off, SORT_IN_TEMPDB = on, DROP_EXISTING = off, IGNORE_DUP_KEY = off, ONLINE = off, FILLFACTOR = 100' + (case when @USECOMPRESSION = 1 then ', DATA_COMPRESSION = PAGE' else '' end) + ') on [IDXGROUP]';
    exec (@SQL);

    /* Create an index on the SourceCode */
    set @SQL = 'create nonclustered index [IX_' + @DATATABLE + '_SOURCECODE] on [dbo].[' + @DATATABLE + '] ([SOURCECODE] asc) with (PAD_INDEX  = off, SORT_IN_TEMPDB = on, DROP_EXISTING = off, IGNORE_DUP_KEY = off, ONLINE = off, FILLFACTOR = 100' + (case when @USECOMPRESSION = 1 then ', DATA_COMPRESSION = PAGE' else '' end) + ') on [IDXGROUP]';
    exec (@SQL);

    /* Create an index on the AskLadderID */
    set @SQL = 'create nonclustered index [IX_' + @DATATABLE + '_ASKLADDERID] on [dbo].[' + @DATATABLE + '] ([ASKLADDERID] asc) with (PAD_INDEX  = off, SORT_IN_TEMPDB = on, DROP_EXISTING = off, IGNORE_DUP_KEY = off, ONLINE = off, FILLFACTOR = 100' + (case when @USECOMPRESSION = 1 then ', DATA_COMPRESSION = PAGE' else '' end) + ') on [IDXGROUP]';
    exec (@SQL);

     /* Create an index on the TESTSEGMENTID and DONORID */
    set @SQL = 'create nonclustered index [IX_' + @DATATABLE + '_TESTSEGMENTID_DONORID] on [dbo].[' + @DATATABLE + '] ([TESTSEGMENTID], [DONORID]) include([SEGMENTID]) with (PAD_INDEX  = off, SORT_IN_TEMPDB = on, DROP_EXISTING = off, IGNORE_DUP_KEY = off, ONLINE = off, FILLFACTOR = 100' + (case when @USECOMPRESSION = 1 then ', DATA_COMPRESSION = PAGE' else '' end) + ') on [IDXGROUP]';
    exec (@SQL);

    /* Create an index on the ConstituentAppealID, only for BBEC */
    if dbo.[UFN_INSTALLEDPRODUCTS_PRODUCTIS]('BB9873D7-F1ED-430A-8AB4-F09F47056538') = 0
      begin
        set @SQL = 'create nonclustered index [IX_' + @DATATABLE + '_CONSTITUENTAPPEALID] on [dbo].[' + @DATATABLE + '] ([CONSTITUENTAPPEALID] asc) with (PAD_INDEX  = off, SORT_IN_TEMPDB = on, DROP_EXISTING = off, IGNORE_DUP_KEY = off, ONLINE = off, FILLFACTOR = 100' + (case when @USECOMPRESSION = 1 then ', DATA_COMPRESSION = PAGE' else '' end) + ') on [IDXGROUP]';
        exec (@SQL);
      end
  end try

  begin catch
    exec dbo.[USP_RAISE_ERROR];
    return 1;
  end catch

  return 0;