Simple UDF to generate columnstore index

This is a simple UDF which will generate the SQL code to create a columnstore index on all columns in a given table.  You need to supply the table schema, table name, index name, whether you are dropping an existing index and finally the MAXDOP.  Although the order of the columns does not matter in a columnstore index I kept the column output ordered.

NOTE:  Because computed columns are currently not supported in columnstore indexes the code ignores such columns from the index creation.

Let me know what you think.

 

CREATE FUNCTION [dbo].[GenerateColumnStoreIndex] (@TableSchema VARCHAR(100), @TableName VARCHAR(100), @IndexName VARCHAR(50), @DropExisting BIT, @MAXDOP INT=6 )
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @output VARCHAR(MAX) = ”,
@columnList VARCHAR(MAX) = ”,
@i INT = 0,
@count INT,
@ColumnName VARCHAR(150),
@Drop VARCHAR(20)

SET @DROP = CASE WHEN @DropExisting = 1 THEN ‘ON’ ELSE ‘OFF’ END

— Figure out the number of columns to be added.
DECLARE curColumns CURSOR READ_ONLY
FOR SELECT ‘[‘+ COLUMN_NAME +’]’
FROM INFORMATION_SCHEMA.COLUMNS WITH (READUNCOMMITTED)
WHERE TABLE_NAME = @TableName
AND TABLE_SCHEMA= @TableSchema
AND COLUMN_NAME NOT IN (SELECT sc.name
FROM syscolumns sc
JOIN sysobjects so ON sc.id=so.id
WHERE so.name = @TableName
AND sc.iscomputed=1)
ORDER BY ORDINAL_POSITION ASC

OPEN curColumns

FETCH NEXT FROM curColumns INTO @ColumnName

WHILE( @@FETCH_STATUS = 0 )
BEGIN
SET @columnList += + CHAR(10) + @ColumnName + ‘,’

FETCH NEXT FROM curColumns INTO @ColumnName
END

CLOSE curColumns
DEALLOCATE curColumns

SET @output += ‘CREATE NONCLUSTERED COLUMNSTORE INDEX [‘ +@IndexName+ ‘] ON [‘ +@TableSchema+ ‘].[‘ +@TableName+ ‘]’
SET @output += CHAR(10) + ‘(‘
SET @output += CHAR(9) + LEFT( @columnList, DATALENGTH(@columnList)-1)
SET @output += CHAR(10) + ‘)’ + CHAR(10)
SET @output += ‘WITH (DROP_EXISTING = ‘ +@Drop+ ‘, MAXDOP = ‘ +CAST(@MAXDOP AS VARCHAR)+ ‘)’ +CHAR(10)

RETURN @output
END

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s