none
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 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, esquel@sommarskog.se
    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 **.


    Saturday, December 21, 2013 5:56 AM
    Moderator
  • Saturday, December 21, 2013 8:31 AM
    Moderator

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 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, esquel@sommarskog.se
    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 **.


    Saturday, December 21, 2013 5:56 AM
    Moderator
  • Saturday, December 21, 2013 8:31 AM
    Moderator