USP_MKTFINDERFILE_CREATEDATATABLE

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

  declare @ACTIVE bit;

  select 
    @ACTIVE = [ACTIVE]
  from dbo.[MKTSEGMENTATION]
  where [ID] = @SEGMENTATIONID;

  if @ACTIVE = 1
    begin
      declare @SQL nvarchar(max);
      declare @DATATABLE nvarchar(256);

      set @DATATABLE = dbo.[UFN_MKTFINDERFILE_BUILDTABLENAME](@SEGMENTATIONID);

      if not exists(select 1 from INFORMATION_SCHEMA.TABLES where (TABLE_SCHEMA = 'dbo') and (TABLE_NAME = @DATATABLE))
        begin
          declare @USECOMPRESSION bit = (case when serverproperty('engineedition') = 3 then 1 else 0 end);

          /* Create the table */
          set @SQL = 'create table [dbo].[' + @DATATABLE + '] (' + char(13) +
                     '  [ID] uniqueidentifier rowguidcol not null constraint [DF_' + @DATATABLE + '_ID] default (newsequentialid()),' + char(13) +
                     '  [FINDERFILEID] [uniqueidentifier] not null,' + char(13) +
                     '  [FINDERNUMBER] bigint not null,' + char(13) +
                     '  [SOURCECODE] nvarchar(50),' + char(13) +
                     '  constraint [PK_' + @DATATABLE + '] primary key clustered ([ID] asc)' + char(13) +
                     ') on [DEFGROUP]';

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

          /* Grant select, insert, update, and alter rights on the new table */
          set @SQL = 'grant select, insert, update, alter, delete on dbo.[' + @DATATABLE + '] to BBAPPFXSERVICEROLE';
          exec (@SQL);

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

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

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

        end
    end

  return 0;