USP_CREATENONCLUSTEREDCOLUMNSTOREINDEX

create procedure [BBDW].[USP_CREATENONCLUSTEREDCOLUMNSTOREINDEX] (@SCHEMA nvarchar(255), @TABLE nvarchar(255))

as

  declare @SQLVERSION decimal
  set @SQLVERSION = substring(cast(serverproperty('ProductVersion') as nvarchar), 1, charindex('.', cast(serverproperty('ProductVersion') as nvarchar)) - 1)

  declare @SQL nvarchar(max);
  declare @COLUMNS nvarchar(max);
  declare @CSINDEX nvarchar(255) = (select BBDW.[UFN_GETCOLUMNSTOREINDEXNAME] (@TABLE));

  declare @FILEGROUP nvarchar(40);

--SQL Server 2014: Nonclustered columnstore
if @SQLVERSION >= 12

begin

  set @FILEGROUP = 
    case
      when @TABLE like '%FACT_%' then ' on [BBRPT_FACTIDXGROUP]'
      when @TABLE like '%DIM_%' then ' on [BBRPT_DIMIDXGROUP]' 
      when @TABLE like '%RPT_%' then ' on [BBRPT_FACTIDXGROUP]'
      else ' on [BBRPT_DEFGROUP]'
    end;

  exec BBDW.[USP_DROPOBJECTIFEXISTS] @SCHEMA, @CSINDEX, @TABLE;

  set @SQL = 'create nonclustered columnstore index [' + @CSINDEX + '] on ' + @SCHEMA + '.[' + @TABLE + '] (';

  select 
    @COLUMNS = COALESCE(@COLUMNS+',' ,'') + [COLUMN_NAME]
  from INFORMATION_SCHEMA.[COLUMNS]
  where [TABLE_SCHEMA] = @SCHEMA and [TABLE_NAME] = @TABLE
  and [DATA_TYPE] not in ('varbinary', 'ntext', 'text', 'image', 'rowversion', 'timestamp', 'sql_variant', 'hierarchyid', 'xml') 
  and ([DATA_TYPE] not in ('nvarchar', 'varchar') or [CHARACTER_MAXIMUM_LENGTH] <> -1);

  set @SQL = @SQL + @COLUMNS + ')' + @FILEGROUP;

  exec sp_executesql @SQL;

end


--SQL Server 2012: Nonclustered columnstore
if @SQLVERSION = 11

begin

  set @FILEGROUP = 
    case
      when @TABLE like '%FACT_%' then ' on [BBRPT_FACTIDXGROUP]'
      when @TABLE like '%DIM_%' then ' on [BBRPT_DIMIDXGROUP]' 
      else ' on [BBRPT_DEFGROUP]'
    end;

  exec BBDW.[USP_DROPOBJECTIFEXISTS] @SCHEMA, @CSINDEX, @TABLE;

  set @SQL = 'create nonclustered columnstore index [' + @CSINDEX + '] on ' + @SCHEMA + '.[' + @TABLE + '] (';

  select 
    @COLUMNS = COALESCE(@COLUMNS+',' ,'') + [COLUMN_NAME]
  from INFORMATION_SCHEMA.[COLUMNS]
  where [TABLE_SCHEMA] = @SCHEMA and [TABLE_NAME] = @TABLE
  and ([DATA_TYPE] not in ('binary', 'varbinary', 'ntext', 'text', 'image', 'uniqueidentifier', 'rowversion', 'timestamp', 'sql_variant', 'decimal', 'hierarchyid', 'xml')
  and ([CHARACTER_MAXIMUM_LENGTH] <> -1 or [CHARACTER_MAXIMUM_LENGTH] is null)
  and ([NUMERIC_PRECISION] < 19 or [NUMERIC_PRECISION] is null or [DATA_TYPE] = 'bigint')
  and ([DATETIME_PRECISION] <3 or [DATETIME_PRECISION] is null)
  );

  set @SQL = @SQL + @COLUMNS + ')' + @FILEGROUP;

  exec sp_executesql @SQL;

end;