Answered by:
Generate ColumnStore Index through tsql

Question
-
I am using partition switching for data retention. I would like to truncate the staging table, but the table has a column store index so I need to drop and recreate that columnstore index. Does anyone have a script to dynamically generate the create index for columnstore indexes? I suppose I could just get a list of all columns in the table and generate it based on those columns, but wanted to script the index as it currently is (in case not all columns are on the index).
Friday, December 20, 2013 3:45 PM
Answers
-
Here is a stored procedure I have around. I have not tested it very much with COLUMNSTORE indexes, but I don't think there are very many differences. Also, I had to rip out some parts that are related to the tool where I took this from:
CREATE PROCEDURE ap_oss_script_index_sp @ixname sysname,
@ixcmd nvarchar(MAX) OUTPUT ASDECLARE @tblname sysname,
@object_id int,
@index_id intSELECT @index_id = index_id
FROM sys.indexes
WHERE object_id = @object_id
AND name = @ixname-- Get the mandatory part of the index definition. That is type and columns.
SELECT @ixcmd = 'CREATE ' +
CASE i.is_unique WHEN 1 THEN 'UNIQUE ' ELSE '' END +
CASE i.index_id WHEN 1 THEN 'CLUSTERED ' ELSE '' END +
CASE WHEN i.type = 3 THEN 'XML '
WHEN i.type = 4 THEN 'SPATIAL '
WHEN i.type IN (5, 6) THEN 'COLUMNSTORE '
ELSE ''
END + 'INDEX ' + quotename(@ixname) +
' ON ' + quotename(@tblname) +
'(' + substring(ic.collist.value('.', 'nvarchar(MAX)'), 1,
len(ic.collist.value('.', 'nvarchar(MAX)')) - 1) + ')'
FROM sys.indexes i
CROSS APPLY (SELECT quotename(c.name) +
CASE ic.is_descending_key
WHEN 1 THEN ' DESC'
ELSE ''
END + ','
FROM sys.index_columns ic
JOIN sys.columns c ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
WHERE ic.object_id = @object_id
AND ic.index_id = @index_id
AND (i.type > 2 OR
ic.key_ordinal > 0)
ORDER BY ic.key_ordinal
FOR XML PATH(''), TYPE) AS ic(collist)
WHERE object_id = @object_id
AND index_id = @index_id-- Add any included columns. (We need to do this query by query, since a big
-- query fails with a QP error, see Connect 777049.
IF EXISTS (SELECT *
FROM sys.index_columns
WHERE object_id = @object_id
AND index_id = @index_id
AND is_included_column = 1)
BEGIN
SELECT @ixcmd = @ixcmd + ' INCLUDE(' +
substring(ic.incllist.value('.', 'nvarchar(MAX)'), 1,
len(ic.incllist.value('.', 'nvarchar(MAX)')) - 1) + ')'
FROM (SELECT quotename(c.name) + ','
FROM sys.index_columns ic
JOIN sys.columns c ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
WHERE ic.object_id = @object_id
AND ic.index_id = @index_id
AND ic.is_included_column = 1
ORDER BY ic.index_column_id
FOR XML PATH(''), TYPE) AS ic(incllist)
END-- Tack on index filter and IGNORE_DUP_KEY setting. Thet latter is always there,
-- so that we know that with have WITH section for the rest.
SELECT @ixcmd = @ixcmd +
CASE WHEN filter_definition IS NOT NULL
THEN ' WHERE ' + filter_definition + ' '
ELSE ''
END '
FROM sys.indexes
WHERE object_id = @object_id
AND index_id = @index_idRETURN
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se- Proposed as answer by Elvis Long Thursday, January 2, 2014 5:27 AM
- Marked as answer by Elvis Long Monday, January 6, 2014 1:02 PM
Friday, December 20, 2013 10:54 PM -
Hi ,
Try like this,
SET CONCAT_NULL_YIELDS_NULL OFF; SELECT ' CREATE ' + I.type_desc COLLATE DATABASE_DEFAULT +' INDEX ' + QUOTENAME(I.name) + ' ON ' + QUOTENAME(Schema_name(T.Schema_id))+'.'+QUOTENAME(T.name) + ' ( ' + KeyColumns + ' ) WITH (' + -- default value ' DROP_EXISTING = OFF ' + ' ) ON [' + DS.name + ' ] ' [CreateIndexScript] FROM sys.indexes I JOIN sys.tables T ON T.Object_id = I.Object_id JOIN (SELECT * FROM ( SELECT IC2.object_id , IC2.index_id , STUFF((SELECT ' , ' + C.name FROM sys.index_columns IC1 JOIN Sys.columns C ON C.object_id = IC1.object_id AND C.column_id = IC1.column_id AND (IC1.is_included_column = 1 AND IC1.index_id = 6 ) WHERE IC1.object_id = IC2.object_id AND IC1.index_id = IC2.index_id GROUP BY IC1.object_id,C.name,index_id ORDER BY MAX(IC1.key_ordinal) FOR XML PATH('')), 1, 2, '') KeyColumns FROM sys.index_columns IC2 WHERE (IC2.is_included_column = 1 AND IC2.index_id = 6 ) --AND IC2.Object_id = object_id('Person.Person') --Comment for all tables GROUP BY IC2.object_id ,IC2.index_id) tmp3 )tmp4 ON I.object_id = tmp4.object_id AND I.Index_id = tmp4.index_id JOIN sys.data_spaces DS ON I.data_space_id=DS.data_space_id WHERE I.index_id = 6
Note : is_included_column = 1 in sys.index_columns for ColumnStore Index columns
Reference - http://gallery.technet.microsoft.com/scriptcenter/SQL-Server-Generate-Index-fa790441
sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.
- Edited by SathyanarrayananS Saturday, December 21, 2013 6:14 AM code edit
- Proposed as answer by Elvis Long Thursday, January 2, 2014 5:27 AM
- Marked as answer by Elvis Long Monday, January 6, 2014 1:02 PM
Saturday, December 21, 2013 5:56 AM -
Related blog:
New SQL Server 2012 rewrite for sp_helpindexKalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012- Proposed as answer by Elvis Long Thursday, January 2, 2014 5:27 AM
- Marked as answer by Elvis Long Monday, January 6, 2014 1:02 PM
Saturday, December 21, 2013 8:31 AM
All replies
-
Here is a stored procedure I have around. I have not tested it very much with COLUMNSTORE indexes, but I don't think there are very many differences. Also, I had to rip out some parts that are related to the tool where I took this from:
CREATE PROCEDURE ap_oss_script_index_sp @ixname sysname,
@ixcmd nvarchar(MAX) OUTPUT ASDECLARE @tblname sysname,
@object_id int,
@index_id intSELECT @index_id = index_id
FROM sys.indexes
WHERE object_id = @object_id
AND name = @ixname-- Get the mandatory part of the index definition. That is type and columns.
SELECT @ixcmd = 'CREATE ' +
CASE i.is_unique WHEN 1 THEN 'UNIQUE ' ELSE '' END +
CASE i.index_id WHEN 1 THEN 'CLUSTERED ' ELSE '' END +
CASE WHEN i.type = 3 THEN 'XML '
WHEN i.type = 4 THEN 'SPATIAL '
WHEN i.type IN (5, 6) THEN 'COLUMNSTORE '
ELSE ''
END + 'INDEX ' + quotename(@ixname) +
' ON ' + quotename(@tblname) +
'(' + substring(ic.collist.value('.', 'nvarchar(MAX)'), 1,
len(ic.collist.value('.', 'nvarchar(MAX)')) - 1) + ')'
FROM sys.indexes i
CROSS APPLY (SELECT quotename(c.name) +
CASE ic.is_descending_key
WHEN 1 THEN ' DESC'
ELSE ''
END + ','
FROM sys.index_columns ic
JOIN sys.columns c ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
WHERE ic.object_id = @object_id
AND ic.index_id = @index_id
AND (i.type > 2 OR
ic.key_ordinal > 0)
ORDER BY ic.key_ordinal
FOR XML PATH(''), TYPE) AS ic(collist)
WHERE object_id = @object_id
AND index_id = @index_id-- Add any included columns. (We need to do this query by query, since a big
-- query fails with a QP error, see Connect 777049.
IF EXISTS (SELECT *
FROM sys.index_columns
WHERE object_id = @object_id
AND index_id = @index_id
AND is_included_column = 1)
BEGIN
SELECT @ixcmd = @ixcmd + ' INCLUDE(' +
substring(ic.incllist.value('.', 'nvarchar(MAX)'), 1,
len(ic.incllist.value('.', 'nvarchar(MAX)')) - 1) + ')'
FROM (SELECT quotename(c.name) + ','
FROM sys.index_columns ic
JOIN sys.columns c ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
WHERE ic.object_id = @object_id
AND ic.index_id = @index_id
AND ic.is_included_column = 1
ORDER BY ic.index_column_id
FOR XML PATH(''), TYPE) AS ic(incllist)
END-- Tack on index filter and IGNORE_DUP_KEY setting. Thet latter is always there,
-- so that we know that with have WITH section for the rest.
SELECT @ixcmd = @ixcmd +
CASE WHEN filter_definition IS NOT NULL
THEN ' WHERE ' + filter_definition + ' '
ELSE ''
END '
FROM sys.indexes
WHERE object_id = @object_id
AND index_id = @index_idRETURN
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se- Proposed as answer by Elvis Long Thursday, January 2, 2014 5:27 AM
- Marked as answer by Elvis Long Monday, January 6, 2014 1:02 PM
Friday, December 20, 2013 10:54 PM -
Hi ,
Try like this,
SET CONCAT_NULL_YIELDS_NULL OFF; SELECT ' CREATE ' + I.type_desc COLLATE DATABASE_DEFAULT +' INDEX ' + QUOTENAME(I.name) + ' ON ' + QUOTENAME(Schema_name(T.Schema_id))+'.'+QUOTENAME(T.name) + ' ( ' + KeyColumns + ' ) WITH (' + -- default value ' DROP_EXISTING = OFF ' + ' ) ON [' + DS.name + ' ] ' [CreateIndexScript] FROM sys.indexes I JOIN sys.tables T ON T.Object_id = I.Object_id JOIN (SELECT * FROM ( SELECT IC2.object_id , IC2.index_id , STUFF((SELECT ' , ' + C.name FROM sys.index_columns IC1 JOIN Sys.columns C ON C.object_id = IC1.object_id AND C.column_id = IC1.column_id AND (IC1.is_included_column = 1 AND IC1.index_id = 6 ) WHERE IC1.object_id = IC2.object_id AND IC1.index_id = IC2.index_id GROUP BY IC1.object_id,C.name,index_id ORDER BY MAX(IC1.key_ordinal) FOR XML PATH('')), 1, 2, '') KeyColumns FROM sys.index_columns IC2 WHERE (IC2.is_included_column = 1 AND IC2.index_id = 6 ) --AND IC2.Object_id = object_id('Person.Person') --Comment for all tables GROUP BY IC2.object_id ,IC2.index_id) tmp3 )tmp4 ON I.object_id = tmp4.object_id AND I.Index_id = tmp4.index_id JOIN sys.data_spaces DS ON I.data_space_id=DS.data_space_id WHERE I.index_id = 6
Note : is_included_column = 1 in sys.index_columns for ColumnStore Index columns
Reference - http://gallery.technet.microsoft.com/scriptcenter/SQL-Server-Generate-Index-fa790441
sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.
- Edited by SathyanarrayananS Saturday, December 21, 2013 6:14 AM code edit
- Proposed as answer by Elvis Long Thursday, January 2, 2014 5:27 AM
- Marked as answer by Elvis Long Monday, January 6, 2014 1:02 PM
Saturday, December 21, 2013 5:56 AM -
Related blog:
New SQL Server 2012 rewrite for sp_helpindexKalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012- Proposed as answer by Elvis Long Thursday, January 2, 2014 5:27 AM
- Marked as answer by Elvis Long Monday, January 6, 2014 1:02 PM
Saturday, December 21, 2013 8:31 AM -
Hi,
I have a similar problem getting the index_key information for a NonClustered Columnstore index using sp_helpindex and since my use-case is much simpler, I wrote a simpler sql script as follows. Can someone please let me know if there is any issue with my version to just list the index_keys and also if there is a more standardized stored procedure available with later versions of SQL Server that will solve the issue?
select ac.name as COLUMN_NAME from sys.index_columns ic JOIN sys.all_columns ac on ac.object_id = ic.object_id AND ac.column_id = ic.column_id where ic.object_id = OBJECT_ID('<OBJECT_ID>') AND ic.index_id = '<INDEX_ID>'
Tuesday, June 3, 2014 12:59 AM