none
Move all nonclustered Indexes to a new filegroup RRS feed

  • Question

  • Is there a way to move all the nonclustered indexes of the database to a new filegroup? There are many tables and lots of indexes so recreating each of them manually is a tremendous job.
    • Edited by Curendra Friday, May 15, 2020 3:31 AM
    Friday, May 15, 2020 3:30 AM

Answers

  • Arithmetic overflow error for data type smallint, value = 65603.

    this script uses cursor. there is no variable in the script declared as smallint. 

    the error message shows the error was encountered at 'fetch next from' line.

    Does cursor have any limitation which could cause the above error?


    No, this is an error in the particular script you found.

    I thought I had posted  something for you, but somehow it did not make it to the thread. The query below scripts all your nonclustered indexes. You will need to tack on WITH (DROP_EXISTING  = ON) and the ON clause for the filegroup yourself, but that's a simple find/replace exercise in an editor.

    The script does not handle everything, but leaves out rarely used options such as PAD_INDEX, IGNORE_DUP_KEY etc. Nor does it handle special indexes like columnstore or XML indexes.

    SELECT  '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(i.name) +
            ' ON ' + quotename(s.name) + '.' + quotename(o.name) + 
            '(' + substring(ic.collist.value('.', 'nvarchar(MAX)'), 1,
                            len(ic.collist.value('.', 'nvarchar(MAX)')) - 1) + ')' +
            CASE WHEN inc.incllist IS NOT NULL 
                 THEN  ' INCLUDE(' +
                       substring(inc.incllist.value('.', 'nvarchar(MAX)'), 1,
                                 len(inc.incllist.value('.', 'nvarchar(MAX)')) - 1) + ')'
                 ELSE ''
           END +
           CASE WHEN filter_definition IS NOT NULL
                           THEN ' WHERE ' + filter_definition + ' '
                           ELSE ''
           END 
    FROM   sys.indexes i
    JOIN   sys.objects o ON i.object_id = o.object_id
    JOIN   sys.schemas s ON s.schema_id = o.schema_id
    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 = i.object_id
                    AND  ic.index_id  = i.index_id
                    AND  (i.type > 2 OR
                          ic.key_ordinal > 0)
                  ORDER  BY ic.key_ordinal
                  FOR XML PATH(''), TYPE) AS ic(collist)
    OUTER  APPLY  (SELECT quotename(c.name) + ','
                   FROM   sys.index_columns inc
                   JOIN   sys.columns c ON inc.object_id = c.object_id
                                       AND inc.column_id = c.column_id
                   WHERE  inc.object_id = i.object_id
                     AND  inc.index_id  = i.index_id
                     AND  inc.is_included_column = 1
                   ORDER  BY inc.index_column_id
                   FOR XML PATH(''), TYPE) AS inc(incllist)
    WHERE i.index_id >= 2
      AND o.type IN ('U', 'V')
    


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by Curendra Friday, June 5, 2020 1:53 AM
    Tuesday, May 19, 2020 9:42 PM

All replies

  • There is not automated way, you have to recreate all indexes on your own.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by Curendra Friday, May 15, 2020 8:25 AM
    • Unmarked as answer by Curendra Tuesday, May 19, 2020 2:05 AM
    Friday, May 15, 2020 5:23 AM
    Moderator
  • Hi Curendra,

    >Is there a way to move all the nonclustered indexes of the database to a new filegroup?

    Please refrence: move-an-existing-index-to-a-different-filegroup

    Try workaround as next:

    Index (choose the index)->Properties->Storge->Filegroup(choose the different one)

    Best Regards.

    yuxi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, May 15, 2020 5:53 AM
  • A script to generate the create of all indexes would also be helpful. I found one script on internet which generates the create of indexes but gave the following error in the end.

    Arithmetic overflow error for data type smallint, value = 65603.

    this script uses cursor. there is no variable in the script declared as smallint. 

    the error message shows the error was encountered at 'fetch next from' line.

    Does cursor have any limitation which could cause the above error?


    • Edited by Curendra Tuesday, May 19, 2020 2:13 AM
    Tuesday, May 19, 2020 2:10 AM
  • Hi Curendra,

    >Does cursor have any limitation which could cause the above error?

    I am not familar with T_Sql and you can post your questiones about T_Sql in Forum_TSQL.

    Best Regards.

    yuxi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, May 19, 2020 5:45 AM
  • Arithmetic overflow error for data type smallint, value = 65603.

    this script uses cursor. there is no variable in the script declared as smallint. 

    the error message shows the error was encountered at 'fetch next from' line.

    Does cursor have any limitation which could cause the above error?


    No, this is an error in the particular script you found.

    I thought I had posted  something for you, but somehow it did not make it to the thread. The query below scripts all your nonclustered indexes. You will need to tack on WITH (DROP_EXISTING  = ON) and the ON clause for the filegroup yourself, but that's a simple find/replace exercise in an editor.

    The script does not handle everything, but leaves out rarely used options such as PAD_INDEX, IGNORE_DUP_KEY etc. Nor does it handle special indexes like columnstore or XML indexes.

    SELECT  '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(i.name) +
            ' ON ' + quotename(s.name) + '.' + quotename(o.name) + 
            '(' + substring(ic.collist.value('.', 'nvarchar(MAX)'), 1,
                            len(ic.collist.value('.', 'nvarchar(MAX)')) - 1) + ')' +
            CASE WHEN inc.incllist IS NOT NULL 
                 THEN  ' INCLUDE(' +
                       substring(inc.incllist.value('.', 'nvarchar(MAX)'), 1,
                                 len(inc.incllist.value('.', 'nvarchar(MAX)')) - 1) + ')'
                 ELSE ''
           END +
           CASE WHEN filter_definition IS NOT NULL
                           THEN ' WHERE ' + filter_definition + ' '
                           ELSE ''
           END 
    FROM   sys.indexes i
    JOIN   sys.objects o ON i.object_id = o.object_id
    JOIN   sys.schemas s ON s.schema_id = o.schema_id
    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 = i.object_id
                    AND  ic.index_id  = i.index_id
                    AND  (i.type > 2 OR
                          ic.key_ordinal > 0)
                  ORDER  BY ic.key_ordinal
                  FOR XML PATH(''), TYPE) AS ic(collist)
    OUTER  APPLY  (SELECT quotename(c.name) + ','
                   FROM   sys.index_columns inc
                   JOIN   sys.columns c ON inc.object_id = c.object_id
                                       AND inc.column_id = c.column_id
                   WHERE  inc.object_id = i.object_id
                     AND  inc.index_id  = i.index_id
                     AND  inc.is_included_column = 1
                   ORDER  BY inc.index_column_id
                   FOR XML PATH(''), TYPE) AS inc(incllist)
    WHERE i.index_id >= 2
      AND o.type IN ('U', 'V')
    


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by Curendra Friday, June 5, 2020 1:53 AM
    Tuesday, May 19, 2020 9:42 PM
  • Hi Curendra,

    Is the reply helpful?

    Best Regards.

    yuxi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, May 20, 2020 1:34 AM
  • Thanks Erland.
    Friday, June 5, 2020 1:52 AM