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).

Answer 1

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 AS

DECLARE @tblname   sysname,
        @object_id int,
        @index_id  int

SELECT @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_id

RETURN

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 2

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 **.

Answer 3

Related blog:

New SQL Server 2012 rewrite for sp_helpindex

By: Kimberly Tripp

Posted on: September 10, 2013

---

Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

Answer 4

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>'