SQL Server удаляет и воссоздает индексы таблицы

ОТКЛЮЧИТЬ все индексы в целевой таблице

  ALTER INDEX Indexname ON Table DISABLE

Затем измените тип данных столбца

ALTER TABLE table
ALTER COLUMN columnname datatype

После этого включить индексы

ALTER INDEX Indexname ON Table REBUILD

Также попробуйте это, чтобы узнать все индексы в таблице с именами столбцов:

SELECT  OBJECT_SCHEMA_NAME(ind.object_id) AS SchemaName
      , OBJECT_NAME(ind.object_id) AS ObjectName
      , ind.name AS IndexName
      , ind.is_primary_key AS IsPrimaryKey
      , ind.is_unique AS IsUniqueIndex
      , col.name AS ColumnName
      , ic.is_included_column AS IsIncludedColumn
      , ic.key_ordinal AS ColumnOrder
FROM    sys.indexes ind
        INNER JOIN sys.index_columns ic
            ON ind.object_id = ic.object_id
               AND ind.index_id = ic.index_id
        INNER JOIN sys.columns col
            ON ic.object_id = col.object_id
               AND ic.column_id = col.column_id
        INNER JOIN sys.tables t
            ON ind.object_id = t.object_id
WHERE   t.is_ms_shipped = 0
ORDER BY OBJECT_SCHEMA_NAME(ind.object_id) --SchemaName
      , OBJECT_NAME(ind.object_id) --ObjectName
      , ind.is_primary_key DESC
      , ind.is_unique DESC
      , ind.name --IndexName
      , ic.key_ordinal

Вы можете использовать приведенный ниже скрипт, который возвращает имя и тип индекса для указанной таблицы/столбца:

DECLARE @tableName SYSNAME
DECLARE @columnName SYSNAME

SET @tableName = 'Products'
SET @columnName = 'Name'

SELECT  IDX.name, IDX.type_desc, IndexedColumn
FROM    sys.tables TBL
INNER JOIN  sys.indexes IDX ON TBL.object_id = IDX.object_id
LEFT JOIN   sys.filegroups FG ON IDX.data_space_id = FG.data_space_id
CROSS APPLY
(   SELECT  COLS.Name
    FROM    sys.index_columns IXCL
    INNER JOIN  sys.columns COLS
                ON IXCL.object_id = COLS.object_id
                AND IXCL.column_id = COLS.column_id
    WHERE   IDX.object_id = IXCL.object_id
    AND     IDX.index_id = IXCL.index_id
    AND     COLS.name = @columnName
    AND     IDX.object_id = OBJECT_ID(@tableName)
) Indexed (IndexedColumn)
WHERE   TBL.object_id = OBJECT_ID(@tableName)

Надеюсь это поможет...

Вы можете использовать встроенные инструменты для выполнения этой работы. В SQL Server Management Studio нажмите «Инструменты», затем «Параметры».

Разверните набор «Обозреватель объектов SQL Server» и в нем нажмите «Сценарии».

Прокрутите вниз до «Параметры таблицы и просмотра» справа.

Найдите запись под названием «Индексы сценариев» и установите для нее значение «Истина», затем нажмите «ОК».

Когда вы щелкаете правой кнопкой мыши свою таблицу в проводнике объектов, у вас есть варианты «Сценарий как ...», выбрав любой из этих параметров, теперь будут написаны индексы, а также сама таблица и ее ключи. Скопируйте необходимые сценарии или просто запустите все это в зависимости от ваших потребностей.

Предположим, что базовый случай (столбец не является частью какого-либо ограничения, не является столбцом XML, имеющим над ним XML-индекс и т. д.), можно сделать следующее:

• генерировать описания индексов в виде XML, используя select (...) from <sys.indexes + other sys schema views> FOR XML ...
• поместите каждый XML как расширенное свойство таблицы, назвав его, например. с префиксом 'IX_' ('IX_1', 'IX_2' и т. д.)
• удалить индексы
• изменить столбец
• собрать все расширенные свойства таблицы с префиксом 'IX_'
• воссоздавать каждый индекс на основе его XML-описания

Вот пример SQL для безопасного удаления и воссоздания индекса:

IF(select object_id from sys.indexes  where [name] = 'IDX_RecordSubscription' and object_id = OBJECT_ID('[SystemSetup].[RecordSubscription]')) IS NOT NULL 
BEGIN 
    DROP INDEX [SystemSetup].[RecordSubscription].IDX_RecordSubscription 
 END

GO

CREATE  UNIQUE  INDEX
    IDX_RecordSubscription
ON
    [SystemSetup].[RecordSubscription]
    (
            Subscriber ASC,
    MenuItem ASC,
    RecordPrimaryKeyGuid ASC

    )
    WITH
    (
        PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF
    ) ON [PRIMARY] 

GO

Вот некоторый код C#, который выкачивает это:

    protected override string GetCreateIndexScript(string uniquePart, string indexName, string fullTableName, string columnsPart)
    {
        return
            $"IF(select object_id from sys.indexes  where [name] = '{indexName}' and object_id = OBJECT_ID('{fullTableName}')) IS NOT NULL \r\n" +
            "BEGIN \r\n" +
            $"    DROP INDEX {fullTableName}.{indexName} \r\n " +
            "END\r\n\r\n" +
            "GO\r\n\r\n" +
            $"CREATE {uniquePart} INDEX\r\n" +
            $"\t{indexName}\r\n" +
            "ON\r\n" +
            $"\t{fullTableName}\r\n" +
            "\t(\r\n" +
            $"\t\t{columnsPart}\r\n" +
            "\t)\r\n" +
            "\tWITH\r\n" +
            "\t(\r\n" +
            "\t\tPAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF\r\n" +
            "\t) ON [PRIMARY] ";
    }

Вот код C# (который можно преобразовать в SQL), чтобы получить схему индекса:

            const string selectStatement = "select " +
                                           "    SCHEMAs.name + '.' + tabs.name as OBJECT_ID,  " +
                                           "    ind.name as INDEX_NAME,  " +
                                           "    indcol.index_id AS INDEX_ID,  " +
                                           "    indcol.key_ordinal AS COLUMN_ORDINAL,  " +
                                           "    col.name AS COLUMN_NAME,  " +
                                           "    ind.is_unique " +
                                           "from " +
                                           "    sys.indexes ind " +
                                           "inner join " +
                                           "    sys.index_columns indcol     " +
                                           "on " +
                                           "    ind.object_id = indcol.object_id and " +
                                           "    ind.index_id = indcol.index_id " +
                                           "inner join " +
                                           "    sys.columns col " +
                                           "on " +
                                           "    col.object_id = indcol.object_id and " +
                                           "    col.column_id = indcol.column_id " +
                                           "inner join " +
                                           "    sys.tables tabs " +
                                           "on  " +
                                           "    tabs.object_id = ind.object_id " +
                                           "inner join " +
                                           "   sys.schemas schemas " +
                                           "on " +
                                           "   tabs.schema_id = schemas.schema_id " +
                                           "where  " +
                                           "    ind.type =2 and" +
                                           "   tabs.name <> 'sysdiagrams' " +
                                           "order by " +
                                           "    tabs.object_id, " +
                                           "    indcol.index_id, " +
                                           "    indcol.key_ordinal ";

            return DatabaseAdapter.Fill(selectStatement, null, null, null);

Итак, в основном вы выполняете последний фрагмент кода здесь, перебираете результаты (индексы и столбцы) и вызываете GetCreateIndexScript для каждого возвращаемого индекса. Затем вы можете безопасно выполнить каждую из инструкций, созданных для удаления и повторного создания индексов.

Такой же подход можно использовать с TSQL или другим языком.

Вот что я придумал, чтобы удалить/создать/отключить/включить (перестроить) индексы Microsoft SQL Server:

CREATE VIEW dbo.vw_INDEX_TEXT AS
  SELECT
    x.[owner_name],
    x.[table_name],
    x.[index_name],
    
    /*
    ** DROP index
    */
    drop_sql = 'DROP INDEX ' + x.[index_name] + ' ON ' + x.[owner_name] + '.' + x.[table_name] + ';',

    
    /*
    ** CREATE index
    */
    create_sql = 
     'CREATE NONCLUSTERED INDEX ' + x.[index_name] + '
        ON ' + x.[owner_name] + '.' + x.[table_name] + '(' + 
          -- the common-delimited field list. 
         substring(
                (SELECT
                  ',' + [field_name] 
                FROM
                  (
                  SELECT 
                    [owner_name] = SCHEMA_NAME(O.schema_id),
                    [table_name] = O.name,
                    [index_name] = I.name,
                    [field_name] = C.name,
                    O.type,
                    S.key_ordinal,
                    S.is_descending_key,
                    S.is_included_column 
                  FROM 
                    sys.all_objects O inner join sys.indexes I       ON (O.object_id = I.object_id )
                                      inner join sys.index_columns S ON (O.object_id = S.object_id and I.index_id=S.index_id)
                                      inner join sys.columns C       ON (O.object_id = C.object_id and S.column_id = C.column_id)
                  WHERE 
                    I.index_id > 0 
                    AND SCHEMA_NAME(O.schema_id) = x.[owner_name] -- N'dbo' 
                    AND I.name                   = x.[index_name] -- the index name N'IDX_myindex' 
                    AND O.name                   = x.[table_name] -- the base table name N'mytable' 
                    AND O.type                   <> 'IT' 
                    AND I.is_primary_key         = 0 -- we are not creating primary keys  
                    AND I.is_unique_constraint   = 0 -- we are not creating unique constraints
                    AND (INDEXPROPERTY(I.object_id,I.name,'IsStatistics') <> 1)
                    AND (INDEXPROPERTY(I.object_id,I.name,'IsAutoStatistics') <> 1) 
                    AND (INDEXPROPERTY(I.object_id,I.name,'IsHypothetical') <> 1) 
                  ) as f
                FOR XML PATH('') 
            ), 2, 8000) -- we trim the leading comma 
            -- end of field list
        + ') 
     WITH (
          PAD_INDEX = OFF
         );',
         
    /*
    ** DISABLE index
    */
    disable_sql = 'ALTER INDEX ' + x.[index_name] + ' ON ' + x.[owner_name] + '.' + x.[table_name] + ' DISABLE;',

    /*
    ** ENABLE (REBUILD) index
    */
    enable_sql = 'ALTER INDEX ' + x.[index_name] + ' ON ' + x.[owner_name] + '.' + x.[table_name] + ' REBUILD;'

         
  FROM
    (SELECT 
      [owner_name] = SCHEMA_NAME(O.schema_id),
      [table_name] = O.name,
      [index_name] = I.name
      -- other interesting, but not used fields that might be useful for other index types:
      -- O.type,I.index_id,I.is_unique,
      -- prop= INDEXPROPERTY(I.object_id,I.name,'IsClustered'),
      -- I.is_padded,
      -- I.fill_factor,
      -- I.ignore_dup_key,I.allow_row_locks,I.allow_page_locks,I.is_disabled,I.data_space_id 
    FROM 
      sys.all_objects O INNER JOIN sys.indexes I on O.object_id=I.object_id 
    WHERE 
      I.index_id>0 
      -- AND O.name = @target_table_name
      AND O.type <> 'IT' 
      AND INDEXPROPERTY(I.object_id,I.name,'IsStatistics')     <> 1 
      AND INDEXPROPERTY(I.object_id,I.name,'IsAutoStatistics') <> 1 
      AND INDEXPROPERTY(I.object_id,I.name,'IsHypothetical')   <> 1 
      AND I.is_primary_key = 0 
      AND I.is_unique_constraint = 0  
    ) as x

Пересоздаются только самые основные индексы, но я оставил несколько дополнительных полей, которые могут пригодиться в качестве основы для создания более сложных индексов по мере необходимости.

Команды находятся в каждом поле зрения. Используйте exec для фактического выполнения такой операции (вставьте имя таблицы вместо @target_table_name):

  Declare @target_table_name as sysname = 'mytable';
  Declare @sql_cmd           as table(ID int identity, cmd varchar(max) );
  Declare @this_cmd          as varchar(max) = '';
  Declare @ct                as int = 0;
  
  -- populate a temp table to store the results
  INSERT INTO @sql_cmd(cmd)
    SELECT
      cmd = enable_sql
    FROM
      dbo.vw_INDEX_TEXT 
    WHERE 
      table_name = @target_table_name
 
  -- the ID column will help us step though the rows one at a time
  SELECT @ct = max(ID) FROM @sql_cmd

  -- loop over all rows in the table, finding each individual command
  While (@ct > 0) Begin
    SELECT
      @this_cmd = cmd
    FROM
      @sql_cmd
    WHERE
      id = @ct
      
    select @this_cmd

    -- un-comment this line to actually run the command:
    -- exec (@this_cmd)
    
    SET @ct = @ct - 1
  End
    

Обратите внимание, что exec в примере отключено.