USP_CONSTITUENTSEGMENT_CREATETABLE

CREATE procedure dbo.[USP_CONSTITUENTSEGMENT_CREATETABLE]
(
  @QUERYVIEWCATALOGID uniqueidentifier
)
with execute as owner
as
  set nocount on;

  declare @TABLENAME nvarchar(128);
  declare @SQL nvarchar(max);

  begin try
    set @TABLENAME = dbo.[UFN_CONSTITUENTSEGMENT_MAKETABLENAME](@QUERYVIEWCATALOGID);

    /* If the table is not yet created, then create it */
    if not exists(select 1 from [INFORMATION_SCHEMA].[TABLES] where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @TABLENAME)
      begin
        declare @PRIMARYKEYTYPENAME nvarchar(20);
        declare @USECOMPRESSION bit = (case when serverproperty('engineedition') = 3 then 1 else 0 end);

        /* If this is BBEC, and the BBEC record source, then we know the primary key type. We also want to add a special ID for OLAP.*/
        if dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC](@QUERYVIEWCATALOGID) = 1
          set @PRIMARYKEYTYPENAME = 'uniqueidentifier';
        else
          /* This is a generic query view source, so get the primary key type.*/
          select
            @PRIMARYKEYTYPENAME = [PRIMARYKEYTYPENAME]
          from dbo.[QUERYVIEWCATALOG]
          where [ID] = @QUERYVIEWCATALOGID;

        set @SQL = 'create table dbo.[' + @TABLENAME + '](' + char(13) +
                   '  [CONSTITUENTID] ' + @PRIMARYKEYTYPENAME + ' not null,' + char(13) +
                   '  [SEGMENTID] uniqueidentifier not null,' + char(13) +
                   '  [TESTSEGMENTID] uniqueidentifier,' + char(13) +
                   '  [SOURCECODEMAPID] uniqueidentifier,' + char(13) +
                   '  [FINDERNUMBER] bigint not null,' + char(13) +
                   /* NOTE: if modifying this primary key, then it can also be named [PK_' + @TABLENAME + '_MARKETINGCONSTITUENTID] */
                   /* in some databases because there was an error in the initial revision that added the primary key.              */
                   '  [MARKETINGCONSTITUENTID] bigint identity(1,1) not null constraint [PK_' + @TABLENAME + '] primary key clustered ([MARKETINGCONSTITUENTID] asc) with (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100),' + char(13) +
                   '  constraint [FK_' + @TABLENAME + '_MKTSEGMENTATIONSEGMENT] foreign key ([SEGMENTID]) references dbo.[MKTSEGMENTATIONSEGMENT] ([ID]) on delete cascade,' + char(13) +
                   '  constraint [FK_' + @TABLENAME + '_MKTSEGMENTATIONTESTSEGMENT] foreign key ([TESTSEGMENTID]) references dbo.[MKTSEGMENTATIONTESTSEGMENT] ([ID]) on delete no action,' + char(13) +
                   '  constraint [FK_' + @TABLENAME + '_MKTSOURCECODEMAP] foreign key ([SOURCECODEMAPID]) references dbo.[MKTSOURCECODEMAP] ([ID]) on delete no action,' + char(13) +
                   '  constraint [CK_' + @TABLENAME + '_FINDERNUMBER] CHECK ([FINDERNUMBER] >= 0)' + char(13) +
                   ') on [BIOGROUP]';

        if @USECOMPRESSION = 1
          set @SQL += ' WITH (DATA_COMPRESSION = PAGE)';

        exec (@SQL);

        set @SQL = 'create nonclustered index [IX_' + @TABLENAME + '_CONSTITUENTID] on dbo.[' + @TABLENAME + '] ([CONSTITUENTID] asc) include ([SEGMENTID], [TESTSEGMENTID], [SOURCECODEMAPID], [FINDERNUMBER]) with (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90' + (case when @USECOMPRESSION = 1 then ', DATA_COMPRESSION = PAGE' else '' end) + ') on [BIOIDXGROUP]';
        exec (@SQL);

        set @SQL = 'create nonclustered index [IX_' + @TABLENAME + '_SEGMENTID] on dbo.[' + @TABLENAME + '] ([SEGMENTID] asc) include ([CONSTITUENTID], [TESTSEGMENTID]) with (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90' + (case when @USECOMPRESSION = 1 then ', DATA_COMPRESSION = PAGE' else '' end) + ') on [BIOIDXGROUP]';
        exec (@SQL);

        set @SQL = 'create nonclustered index [IX_' + @TABLENAME + '_TESTSEGMENTID] on dbo.[' + @TABLENAME + '] ([TESTSEGMENTID] asc) with (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90' + (case when @USECOMPRESSION = 1 then ', DATA_COMPRESSION = PAGE' else '' end) + ') on [BIOIDXGROUP]';
        exec (@SQL);

        set @SQL = 'create nonclustered index [IX_' + @TABLENAME + '_SOURCECODEMAPID] on dbo.[' + @TABLENAME + '] ([SOURCECODEMAPID] asc) with (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90' + (case when @USECOMPRESSION = 1 then ', DATA_COMPRESSION = PAGE' else '' end) + ') on [BIOIDXGROUP]';
        exec (@SQL);

        set @SQL = 'create nonclustered index [IX_' + @TABLENAME + '_FINDERNUMBER] on dbo.[' + @TABLENAME + '] ([FINDERNUMBER] asc) include ([CONSTITUENTID], [SEGMENTID], [SOURCECODEMAPID]) with (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90' + (case when @USECOMPRESSION = 1 then ', DATA_COMPRESSION = PAGE' else '' end) + ') on [BIOIDXGROUP]';
        exec (@SQL);

        set @SQL = 'grant select, insert, delete on dbo.[' + @TABLENAME + '] to BBAPPFXSERVICEROLE';
        exec (@SQL);
      end
  end try

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

  return 0;