How To Add NonClustered ColumnStore Indexes To Every Table
Sometimes I feel really lazy. I’m just tinkering with a proof of concept database, a decent amount of test data, I just want to do some moderately fast querying over it and don’t really feel like going about creating proper indexes for everything just yet. I really only want to slap a ColumnStore on everything at once so I can get on with work and run some fast questions over the data.
Luckily, that’s what the cheat script below is for.
/* set the prefix for the indexes here */
DECLARE @IndexPrefix NVARCHAR(MAX) = 'TEST_NCCSI_';
/* dont worry about these */
DECLARE @SQL NVARCHAR(MAX) = '';
DECLARE @NL NVARCHAR(MAX) = NCHAR(13) + NCHAR(10);
DECLARE @TB NVARCHAR(MAX) = NCHAR(9);
/* and off we go */
WITH Query AS
(
SELECT
SchemaId = S.schema_id,
SchemaName = S.name,
TableId = T.object_id,
TableName = T.name,
ColumnId = C.column_id,
ColumnName = C.name,
IsFirstColumn = CASE WHEN C.column_id = MIN(C.column_id) OVER (PARTITION BY S.schema_id, T.object_id) THEN 1 ELSE 0 END,
IsLastColumn = CASE WHEN C.column_id = MAX(C.column_id) OVER (PARTITION BY S.schema_id, T.object_id) THEN 1 ELSE 0 END
FROM
sys.tables AS T
INNER JOIN sys.schemas AS S
ON S.schema_id = T.schema_id
INNER JOIN sys.columns AS C
ON C.object_id = T.object_id
)
SELECT
@SQL +=
/* add ddl statement with first column */
CASE WHEN Q.IsFirstColumn = 1 THEN
'CREATE NONCLUSTERED COLUMNSTORE INDEX ' + QUOTENAME(@IndexPrefix + Q.TableName) + ' ON ' + QUOTENAME(Q.SchemaName) + '.' + QUOTENAME(Q.TableName) + @NL +
'('
ELSE
''
END
/* add column name */
+
@NL + @TB + QUOTENAME(Q.ColumnName)
/* add comma after interim columns */
+
CASE WHEN Q.IsLastColumn = 0 THEN ',' ELSE '' END
/* close ddl after last column */
+
CASE WHEN Q.IsLastColumn = 1 THEN @NL + ')' + @NL + @NL ELSE '' END
FROM
Query AS Q
ORDER BY
SchemaId,
TableId,
ColumnId
;
SELECT @SQL;
PRINT @SQL;
Note that this straightforward script doesn’t try to partition align the indexes, detect invalid columns or any other fancy stuff. If you do end up adding those details, feel free to comment and I’ll add those in too.