Поделиться через

ALTER INDEX (Transact-SQL)

  • Статья

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

Значок ссылки на разделСоглашения о синтаксическом обозначении в Transact-SQL

Синтаксис

ALTER INDEX { index_name | ALL }
    ON <object>
    { REBUILD 
        [ [ WITH ( <rebuild_index_option> [ ,...n ] ) ] 
          | [ PARTITION = partition_number 
                [ WITH ( <single_partition_rebuild_index_option>
                        [ ,...n ] )
                ] 
            ]
        ]
    | DISABLE
    | REORGANIZE 
        [ PARTITION = partition_number ]
        [ WITH ( LOB_COMPACTION = { ON | OFF } ) ]
    | SET ( <set_index_option> [ ,...n ] ) 
    }
[ ; ]

<object> ::=
{
    [ database_name. [ schema_name ] . | schema_name. ]
        table_or_view_name
}

<rebuild_index_option > ::=
{
    PAD_INDEX  = { ON | OFF }
  | FILLFACTOR = fillfactor 
  | SORT_IN_TEMPDB = { ON | OFF }
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | ONLINE = { ON | OFF } 
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
}

<single_partition_rebuild_index_option> ::=
{
    SORT_IN_TEMPDB = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
}

<set_index_option>::=
{
    ALLOW_ROW_LOCKS= { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
}

Аргументы

  • index_name
    Имя индекса. Имена индексов должны быть уникальными в пределах таблицы или представления, но необязательно должны быть уникальными в пределах базы данных. Имена индексов должны удовлетворять правилам для идентификаторов.

  • ALL
    Указывает все индексы, связанные с таблицей или представлением, независимо от типа индекса. Если указывается ключевое слово ALL, то инструкция не будет выполнена, если один или несколько индексов находятся в автономной или предназначенной только для чтения файловой группе или указанная операция запрещена для одного или нескольких типов индекса. В следующей таблице перечислены операции с индексами и запрещенные типы индексов.

    Указание ALL с этой операцией Отказывает, если в таблице имеется один или несколько.

    REBUILD WITH ONLINE = ON

    XML-индекс

    Столбцы типов данных больших объектов: image, text, ntext, varchar(max), nvarchar(max), varbinary(max) и xml

    REBUILD PARTITION = partition_number

    Несекционированный индекс, XML-индекс или отключенный индекс.

    REORGANIZE

    Индексирует с параметром ALLOW_PAGE_LOCKS, имеющим значение OFF.

    REORGANIZE PARTITION = partition_number

    Несекционированный индекс, XML-индекс или отключенный индекс.

    SET

    Отключенный индекс.

    Если ключевое слово ALL указывается вместе с PARTITION = partition_number, то все индексы должны быть выровнены. Следовательно, они секционируются на основе эквивалентных функций секционирования. При использовании ключевого слова ALL вместе с PARTITION все индексные секции с одинаковым аргументом partition_number будут перестроены или реорганизованы. Дополнительные сведения о секционированных индексах см. в разделе Секционированные таблицы и индексы.

  • database_name
    Имя базы данных.
  • schema_name
    Имя схемы, к которой принадлежит таблица или представление.
  • table_or_view_name
    Имя таблицы или представления, связанного с индексом. Чтобы отобразить отчет по индексам объекта, следует воспользоваться представлением каталога sys.indexes.

  • REBUILD [ WITH (<rebuild_index_option> [ ,... n]) ]
    Указывает, что индекс будет перестроен с использованием тех же столбцов, типов индекса, атрибута уникальности и порядка сортировки. Это предложение эквивалентно DBCC DBREINDEX. REBUILD включает отключенный индекс. При перестройке кластеризованного индекса не перестраиваются ассоциированные некластеризованные индексы, если только не указано ключевое слово ALL. Если параметры индекса не заданы, то применяется существующий параметр индекса, который хранится в таблице sys.indexes. Для любого параметра индекса, значение которого не хранится в таблице sys.indexes, применяется значение по умолчанию, указанное в определении аргумента.

    Параметры ONLINE и IGNORE_DUP_KEY не действуют при перестройке XML-индекса.

    Если указано ключевое слово ALL, а базовая таблица реализована в виде кучи, операция перестроения не воздействует на таблицу. Перестраиваются все некластеризованные индексы, ассоциированные с таблицей.

    Возможно минимальное протоколирование операции перестройки, если модель восстановления базы данных настроена на массовый или простой режим. Дополнительные сведения см. в разделе Выбор модели восстановления для операций с индексами.

    ms188388.note(ru-ru,SQL.90).gifПримечание.
    При перестройке первичного XML-индекса индексированная пользовательская таблица недоступна в течение действия операции с индексами.
  • PARTITION
    Указывает, что только одна секция индекса будет перестроена или реорганизована. PARTITION не может быть указана, если аргумент index_name — несекционированный индекс.
  • partition_number
    Номер секции секционированного индекса, который предстоит перестроить или реорганизовать partition_number, — неизменяемое выражение, которое может ссылаться на переменные. К ним относятся переменные пользовательского типа или функции и пользовательские функции, но нельзя ссылаться на инструкцию языка Transact-SQL. Должен существовать partition_number , или выполнение инструкции завершится с ошибкой.

  • WITH (<single_partition_rebuild_index_option>)
    SORT_IN_TEMPDB и MAXDOP — параметры, которые могут быть указаны при перестройке одиночной секции (PARTITION = n). XML-индексы не могут быть указаны в операции перестроения одиночной секции.

    Перестроение секционированного индекса нельзя выполнять в оперативном режиме. Во время этой операции вся таблица блокируется.

  • DISABLE
    Помечает индекс как отключенный и недоступный для использования компонентом SQL Server 2005 Database Engine. Любой индекс может быть отключен. Определение отключенного индекса остается в системном каталоге без базовых индексных данных. Отключение кластеризованного индекса блокирует доступ пользователя к данным базовой таблицы. Чтобы активировать индекс, следует использовать инструкцию ALTER INDEX REBUILD или CREATE INDEX WITH DROP_EXISTING. Дополнительные сведения см. в разделе Отключение индексов.
  • REORGANIZE
    Указывает, что конечный уровень индекса будет реорганизован. Это предложение эквивалентно DBCC INDEXDEFRAG. Инструкция ALTER INDEX REORGANIZE всегда выполняется в оперативном режиме. Это означает, что долгосрочные блокировки таблицы не удерживаются и запросы или обновления базовой таблицы могут продолжаться во время выполнения транзакции ALTER INDEX REORGANIZE. REORGANIZE не может быть указана для отключенного индекса или индекса с ALLOW_PAGE_LOCKS со значением OFF.

  • WITH ( LOB_COMPACTION = { ON | OFF } )
    Указывает, что все страницы, содержащие данные большого объекта (LOB), сжимаются. Типы данных LOB: image, text, ntext, varchar(max), nvarchar(max), varbinary(max) и xml. Сжатие этих данных может освободить место на диске. Значение по умолчанию — ON.

    • ON
      Все страницы, содержащие данные большого объекта, сжимаются.

      Изменение указанного кластеризованного индекса сжимает все столбцы LOB, которые содержатся в кластеризованном индексе. Изменение некластеризованного индекса сжимает все столбцы LOB, являющиеся неключевыми столбцами, включенными в индекс. Дополнительные сведения см. в разделе Создание индексов с включенными столбцами.

      Если указывается ключевое слово ALL, то все индексы, связанные с конкретной таблицей или представлением, реорганизовываются, все столбцы LOB, связанные с кластеризованным индексом, базовой таблицей или некластеризованным индексом с включенными столбцами, сжимаются.

    • OFF
      Все страницы, содержащие данные большого объекта, не сжимаются.

      Параметр OFF не влияет на кучу.

    Предложение LOB_COMPACTION пропускается, если отсутствуют столбцы LOB.

  • SET ( <set_index option> [ ,... n] )
    Указывает параметры индекса без перестройки или реорганизации индекса. SET нельзя указать для отключенного индекса.

  • PAD_INDEX = { ON | OFF }
    Определяет заполнение индекса. Значение по умолчанию — OFF.

    • ON
      Процент свободного места, определяемый параметром FILLFACTOR, применяется к страницам индекса промежуточного уровня. Если FILLFACTOR не указан и одновременно PAD_INDEX установлен в состояние ON, то используется значение коэффициента заполнения, хранимое в таблице sys.indexes.
    • OFF или fillfactor не указан
      Страницы промежуточного уровня заполняются почти полностью. При этом остается достаточно места по крайней мере для одной строки максимального размера, которого может достигать индекс, в зависимости от набора ключей в промежуточных страницах.

    Дополнительные сведения см. в разделе CREATE INDEX (Transact-SQL).

  • FILLFACTOR = fillfactor
    Указывает значение в процентах, показывающее, насколько полным компонент Database Engine должен сделать конечный уровень каждой индексной страницы во время создания или изменения индекса. Аргумент fillfactor должен быть целым числом от 1 до 100. По умолчанию имеет значение 0.

    ms188388.note(ru-ru,SQL.90).gifПримечание.
    Значения коэффициентов заполнения 0 и 100 идентичны.

    Явный параметр FILLFACTOR применяется, только если индекс создается впервые или перестраивается. Компонент Database Engine не сохраняет динамически указанный процентный объем свободного места на страницах. Дополнительные сведения см. в разделе CREATE INDEX (Transact-SQL).

    Для просмотра коэффициента заполнения следует использовать sys.indexes.

    ms188388.note(ru-ru,SQL.90).gifВажно!
    Создание или замена кластеризованного индекса со значением FILLFACTOR влияет на пространство памяти, занимаемое данными, так как компонент Database Engine перераспределяет данные при создании кластеризованного индекса.

  • SORT_IN_TEMPDB = { ON | OFF }
    Указывает, следует ли сохранять результаты сортировки в базе данных tempdb. Значение по умолчанию — OFF.

    • ON
      Промежуточные результаты сортировки, которые используются при индексировании, хранятся в базе данных tempdb. Это может сократить время, требуемое для создания индекса, если база данных tempdb размещена на иных дисках, нежели пользовательская база данных. Однако это увеличивает использование места на диске, которое используется при индексировании.
    • OFF
      Промежуточные результаты сортировки хранятся в той же базе данных, где и индекс.

    Если выполнение сортировки не требуется или если сортировка может быть выполнена в памяти, параметр SORT_IN_TEMPDB пропускается.

    Дополнительные сведения см. в разделе База данных tempdb и создание индекса.

  • IGNORE_DUP_KEY = { ON | OFF }
    Определяет реакцию на ошибку вследствие дублирования значений ключа при транзакции вставки нескольких строк для уникального кластеризованного или уникального некластеризованного индекса. Значение по умолчанию — OFF.

    • ON
      Выдается предупреждение, и ошибки возникнут только в тех строках, которые нарушают индекс UNIQUE.
    • OFF
      Выдается сообщение об ошибке, и выполняется откат для всей транзакции.

    Настройка IGNORE_DUP_KEY применяется только к операциям вставки, производимым после создания или перестроения индекса. Настройка недействительна во время операций с индексами. IGNORE_DUP_KEY не имеет никакого влияния на инструкцию UPDATE.

    Настройка IGNORE_DUP_KEY не может принимать значение ON для XML-индексов и индексов, созданных для представлений. Дополнительные сведения см. в разделе CREATE INDEX (Transact-SQL).

  • STATISTICS_NORECOMPUTE = { ON | OFF }
    Указывает, выполнялся ли перерасчет статистики распределения. Значение по умолчанию — OFF.

    • ON
      Устаревшие статистики не пересчитываются автоматически.
    • OFF
      Автоматическое обновление статистических данных включено.

    Чтобы восстановить автоматическое обновление статистики, следует установить STATISTICS_NORECOMPUTE в значение OFF или выполнить UPDATE STATISTICS без предложения NORECOMPUTE.

    ms188388.note(ru-ru,SQL.90).gifВажно!
    Отключение автоматического перерасчета статистики распределения может помешать оптимизатору запросов выбрать оптимальные планы выполнения запросов, обращенных к таблице.

  • ONLINE = { ON | OFF }
    Определяет, будут ли базовые таблицы и связанные индексы доступны для запросов и изменения данных во время операций с индексами. Значение по умолчанию — OFF.

    ms188388.note(ru-ru,SQL.90).gifПримечание.
    Фоновые операции с индексами доступны только в выпуске SQL Server 2005 Enterprise Edition.
    • ON
      Долгосрочные блокировки таблицы не поддерживаются во время операций с индексами. Во время главной фазы операций с индексами только блокировка с намерением совмещаемого доступа (IS) удерживается в исходной таблице. Это позволяет продолжить выполнение запросов или обновлений для базовых таблиц и индексов. В начале операции совмещаемая блокировка (S) исходного объекта поддерживается в течение очень короткого времени. Если создается некластеризованный индекс, то по завершении операции на короткое время создается блокировка типа S (совмещаемая) для источника. Блокировка типа SCH-M (изменения схемы) запрашивается, если кластеризованный индекс создается или удаляется в оперативном режиме либо происходит перестроение кластеризованного или некластеризованного индекса. При создании индекса для временной локальной таблицы параметр ONLINE не может принимать значение ON.
    • OFF
      Блокировки таблиц применяются во время выполнения операций с индексами. Блокировку изменения схемы (Sch-M) в таблице получает автономная операция с индексами, которая создает, перестраивает или удаляет кластеризованный индекс либо перестраивает или удаляет некластеризованный индекс. Это предотвращает доступ к базовой таблице всех пользователей во время операции. Автономная операция с индексами, создающая некластеризованный индекс, получает совмещаемую блокировку (S) в таблице. Это запрещает проводить обновления базовой таблицы, но разрешает проводить операции чтения, например инструкции SELECT.

    Дополнительные сведения см. в разделе Об оперативных действиях над индексом. Дополнительные сведения о блокировке см. в разделе Режимы блокировки.

    Индексы, в том числе индексы глобальных временных таблиц, могут быть перестроены в оперативном режиме со следующими исключениями:

    • отключенные индексы;
    • XML-индексы
    • индексы локальных временных таблиц;
    • секционированные индексы;
    • кластеризованные индексы, если базовая таблица содержит типы данных LOB;
    • некластеризованные индексы, определенные со столбцами типа данных LOB.

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

  • ALLOW_ROW_LOCKS = { ON | OFF }
    Указывает, разрешена ли блокировка строк. Значение по умолчанию — ON.

    • ON
      Блокировки строк допустимы при доступе к индексу. Необходимость в блокировке строк определяет компонент Database Engine.
    • OFF
      Блокировки строк не используются.

  • ALLOW_PAGE_LOCKS = { ON | OFF }
    Указывает, разрешена ли блокировка страниц. Значение по умолчанию — ON.

    • ON
      Блокировки страниц допустимы при доступе к индексу. Необходимость в блокировке строк определяет компонент Database Engine.
    • OFF
      Блокировки страниц не используются.
    ms188388.note(ru-ru,SQL.90).gifПримечание.
    Индекс не может быть реорганизован, если ALLOW_PAGE_LOCKS установлен в состояние OFF.

  • MAXDOP = max_degree_of_parallelism
    Переопределяет параметр конфигурации максимальная степень параллелизма на время операций с индексами. Дополнительные сведения см. в разделе Параметр max degree of parallelism. MAXDOP можно использовать для ограничения числа процессоров, используемых в одновременном выполнении планов. Максимальное число процессоров — 64.

    Аргумент max_degree_of_parallelism может иметь следующие значения.

    • 1
      Подавляет формирование параллельных планов.
    • >1
      Ограничивает указанным значением максимальное число процессоров, используемых для параллельных операций с индексами.
    • 0 (по умолчанию)
      В зависимости от текущей рабочей нагрузки системы использует реальное или меньшее число процессоров.

    Дополнительные сведения см. в разделе Настройка параллельных индексных операций.

    ms188388.note(ru-ru,SQL.90).gifПримечание.
    Параллельные операции с индексами доступны только в выпуске SQL Server 2005 Enterprise Edition.

Замечания

Инструкция ALTER INDEX не может использоваться для повторного секционирования индекса или его перемещения в другую файловую группу. Эта инструкция не может использоваться для изменения определения индекса, в том числе добавления или удаления столбцов или изменения порядка столбцов. Для выполнения этих операций следует использовать инструкцию CREATE INDEX с предложением DROP_EXISTING.

Если параметр не указан явно, то применяется текущий параметр. Например, если параметр FILLFACTOR не указан в предложении REBUILD, то коэффициент заполнения, сохраненный в системном каталоге, будет использоваться в процессе перестройки. Для просмотра текущего параметра индекса следует использовать таблицу sys.indexes.

ms188388.note(ru-ru,SQL.90).gifПримечание.
Значения для параметров ONLINE, MAXDOP и SORT_IN_TEMPDB не хранятся в системном каталоге. Если значение некоторого параметра не указано в инструкции индекса, то используется значение по умолчанию.

В компьютерах с несколькими процессорами инструкция ALTER INDEX REBUILD, как и другие запросы, использует больше процессоров для операций просмотра и сортировки, связанных с изменением индекса. При выполнении инструкции ALTER INDEX REORGANIZE, без предложения LOB_COMPACTION или с ним, значение аргумента max degree of parallelism представляет собой однопотоковую операцию. Дополнительные сведения см. в разделе Настройка параллельных индексных операций.

Индекс нельзя реорганизовать или перестроить, если файловая группа, в которой он находится, размещена автономно или предназначена только для чтения. Если указывается ключевое слово ALL, а один или несколько индексов находятся в файловой группе, которая размещена автономно или предназначена только для чтения, то выполнить инструкцию не удастся.

Восстановление индексов

При перестроении старый индекс удаляется, и создается новый. Таким образом, устраняется фрагментация, восстанавливается место на диске путем сжатия страниц с учетом указанного или существующего коэффициента заполнения, переупорядочиваются индексные строки в последовательных страницах. Если указывается ключевое слово ALL, то все индексы для таблицы удаляются и перестраиваются в одной транзакции. Ограничения FOREIGN KEY не обязательно отменять заранее. Если перестраиваются индексы с 128 или большим числом экстентов, то компонент Database Engine откладывает процедуры освобождения страниц и связанные с ними блокировки до фиксации транзакции. Дополнительные сведения см. в разделе Удаление и повторная сборка больших объектов.

ms188388.note(ru-ru,SQL.90).gifПримечание.
Перестроение или реорганизация малых индексов часто не приводит к уменьшению фрагментации. Страницы индексов малого размера хранятся в смешанных экстентах. Смешанные экстенты могут совместно использоваться восемью объектами, поэтому фрагментация в небольшом индексе может не сократиться после реорганизации или перестроения индекса. Дополнительные сведения о смешанных экстентах см. в разделе Страницы и экстенты.

В предыдущих версиях SQL Server иногда можно было перестроить некластеризованный индекс, чтобы исправить любые несоответствия, вызванные отказами оборудования. В SQL Server 2005 по-прежнему можно скорректировать такие несоответствия между индексом и кластеризованным индексом, перестроив некластеризованный индекс в автономном режиме. Однако нельзя устранить несоответствия некластеризованного индекса, перестроив индекс в оперативном режиме, потому что оперативный механизм перестройки будет использовать существующий некластеризованный индекс в качестве основы для перестройки и тем самым закрепит несоответствие. При оперативном перестроении индекса, в отличие от вышесказанного, принудительно запускается просмотр кластеризованного индекса (или кучи) и в результате устраняются несоответствия. В предыдущих версиях рекомендованным методом устранения несоответствий было восстановление неправильных данных из резервных копий, однако исправить несоответствия индекса можно, перестроив некластеризованный индекс в автономном режиме. Дополнительные сведения см. в разделе DBCC CHECKDB (Transact-SQL).

Реорганизация индексов

Для реорганизации индекса требуется минимальный объем системных ресурсов. Дефрагментируется конечный уровень кластеризованного и некластеризованного индексов для таблиц и представлений путем физической реорганизации страниц на конечном уровне в соответствии с логическим, слева направо, порядком конечных узлов. Кроме того, реорганизация сжимает страницы индекса. Их сжатие производится в соответствии с текущим значением коэффициента заполнения. Для просмотра коэффициента заполнения следует использовать sys.indexes.

Если указывается ключевое слово ALL, то реляционные индексы, как кластеризованные, так и некластеризованные, и XML-индексы для таблицы реорганизуются. Существуют некоторые ограничения при указании ключевого слова ALL, см. определение ALL в разделе «Аргументы».

Дополнительные сведения см. в разделе Реорганизация и перестроение индексов.

Отключение индексов

Отключение индексов предотвращает доступ пользователя к индексам в случае использования кластеризованных индексов к данным базовой таблицы. Определение индекса остается в системном каталоге. Отключение некластеризованных индексов или кластеризованных индексов в представлении физически удаляет данные индекса. При отключении кластеризованного индекса блокируется доступ к данным, но данные остаются необслуживаемыми в сбалансированном дереве до тех пор, пока индекс не будет удален или перестроен. Для просмотра состояния включенного или отключенного индекса следует направить запрос в столбец is_disabled в представлении каталога sys.indexes.

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

Для активизации индекса следует использовать инструкцию ALTER INDEX REBUILD или инструкцию CREATE INDEX WITH DROP_EXISTING. Перестроить отключенный кластеризованный индекс нельзя, если параметр ONLINE установлен в ON. Дополнительные сведения см. в разделе Отключение индексов.

Установка параметров

Можно установить параметры ALLOW_ROW_LOCKS, ALLOW_PAGE_LOCKS, IGNORE_DUP_KEY и STATISTICS_NORECOMPUTE для конкретного индекса без перестройки или реорганизации этого индекса. Измененные значения немедленно применяются к индексу. Для просмотра этих установок следует использовать таблицу sys.indexes. Дополнительные сведения см. в разделе Установка параметров индекса.

Параметры блокировок строк и страниц

Когда присвоены значения ALLOW_ROW_LOCKS = ON и ALLOW_PAGE_LOCK = ON, при доступе к индексу допустимы блокировки на уровне строк, уровне страниц и уровне таблиц. Компонент Database Engine выберет соответствующую блокировку и может повысить уровень блокировки с уровня строки или таблицы до уровня страницы.

Если присвоены значения ALLOW_ROW_LOCKS = OFF и ALLOW_PAGE_LOCK = OFF, при доступе к индексу допустима только блокировка на уровне таблиц. Дополнительные сведения о настройке гранулярности блокировки индекса см. в разделе Настройка блокировки индекса.

Если при установке параметров блокировки строки или страницы указывается ключевое слово ALL, то установки применяются ко всем индексам. Если базовая таблица представляет собой кучу, установки применяются следующими способами:

ALLOW_ROW_LOCKS = ON или OFF

Для кучи и любых соответствующих некластеризованных индексов.

ALLOW_PAGE_LOCKS = ON

Для кучи и любых соответствующих некластеризованных индексов.

ALLOW_PAGE_LOCKS = OFF

Полностью для некластеризованных индексов. Это означает, что все блокировки страниц запрещаются для некластеризованных индексов. В куче запрещены только общая блокировка (S), блокировка обновления (U) и монопольная блокировка (X) для страниц. Компонент Database Engine может запросить намеренную блокировку страницы (IS, IU или IX) для внутренних целей.

Дополнительные сведения см. в разделе Укрупнение блокировки (компонент Database Engine).

Фоновые операции с индексами

Если при перестройке индекса параметр ONLINE установлен в значение ON, то базовые объекты, таблицы и связанные с ними индексы доступны для запросов и изменения данных. Монопольные блокировки таблиц удерживаются лишь на очень короткое время в процессе изменения.

Реорганизация индекса всегда выполняется в оперативном режиме. Процесс не удерживает блокировку в течение долгого времени и поэтому не блокирует выполняемые запросы и обновления.

Параллельные фоновые операции с индексами для одной таблицы можно выполнять лишь при выполнении следующих действий:

  • создание нескольких некластеризованных индексов;
  • реорганизация различных индексов в одной таблице;
  • реорганизация различных индексов при перестройке неперекрывающихся индексов в одной таблице.

Все остальные попытки выполнения фоновых операций с индексами завершаются ошибкой. Например, нельзя одновременно перестроить два или несколько индексов в одной таблице или создать новый индекс в процессе перестройки существующего индекса для этой таблицы.

Дополнительные сведения см. в разделе Оперативное выполнение операций над индексом.

Разрешения

Для выполнения ALTER INDEX необходимо иметь как минимум разрешение ALTER для таблицы или представления.

Примеры

А. Перестроение индекса

В следующем примере перестраивается один индекс в таблице Employee.

USE AdventureWorks;
GO
ALTER INDEX PK_Employee_EmployeeID ON HumanResources.Employee
REBUILD;
GO

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

В нижеследующем примере указывается ключевое слово ALL. Тем самым выполняется перестроение всех индексов, связанных с таблицей. Указываются три параметра.

USE AdventureWorks;
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON);
GO

В. Реорганизация индекса со сжатием LOB

В нижеследующем примере производится реорганизация отдельного кластеризованного индекса. Поскольку индекс содержит тип данных LOB на конечном уровне, инструкция также подвергает сжатию все страницы, в которых содержатся данные больших объектов. Следует отметить, что указывать параметр WITH (LOB_COMPACTION) не требуется, так как значение по умолчанию — ON.

USE AdventureWorks;
GO
ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto
REORGANIZE ;
GO

Г. Назначение параметров для индекса

В следующем примере устанавливаются несколько параметров для индекса AK_SalesOrderHeader_SalesOrderNumber.

USE AdventureWorks;
GO
ALTER INDEX AK_SalesOrderHeader_SalesOrderNumber ON
    Sales.SalesOrderHeader
SET (
    STATISTICS_NORECOMPUTE = ON,
    IGNORE_DUP_KEY = ON,
    ALLOW_PAGE_LOCKS = ON
    ) ;
GO

Д. Отключение индекса

В следующем примере отключается некластеризованный индекс в таблице Employee.

USE AdventureWorks;
GO
ALTER INDEX IX_Employee_ManagerID ON HumanResources.Employee
DISABLE ;
GO

Е. Снятие ограничений

В следующем примере снимается ограничение PRIMARY KEY путем отключения индекса PRIMARY KEY. Ограничение FOREIGN KEY в базовой таблице автоматически отключается, и выводится предупредительное сообщение.

USE AdventureWorks;
GO
ALTER INDEX PK_Department_DepartmentID ON HumanResources.Department
DISABLE ;
GO

Результирующий набор возвращает это предупреждающее сообщение.

Warning: Foreign key 'FK_EmployeeDepartmentHistory_Department_DepartmentID' on table 'EmployeeDepartmentHistory' referencing table 'Department' was disabled as a result of disabling the index 'PK_Department_DepartmentID'.

Ж. Активация ограничений

В следующем примере активируются ограничения PRIMARY KEY и FOREIGN KEY, снятые в примере Е.

Ограничение PRIMARY KEY активируется путем перестройки индекса PRIMARY KEY.

USE AdventureWorks;
GO
ALTER INDEX PK_Department_DepartmentID ON HumanResources.Department
REBUILD ;
GO

Затем активируется ограничение FOREIGN KEY.

ALTER TABLE HumanResources.EmployeeDepartmentHistory
CHECK CONSTRAINT FK_EmployeeDepartmentHistory_Department_DepartmentID;
GO

З. Перестройка секционированного индекса

В следующем примере перестраивается одиночная секция, секция номер 5 секционированного индекса IX_TransactionHistory_TransactionDate. В данном примере подразумевается, что образец секционированного индекса установлен. Дополнительные сведения по установке см. в разделе Readme_PartitioningScript.

USE AdventureWorks;
GO
-- Verify the partitioned indexes.
SELECT *
FROM sys.dm_db_index_physical_stats (DB_ID(),OBJECT_ID(N'Production.TransactionHistory'), NULL , NULL, NULL);
GO
--Rebuild only partition 5.
ALTER INDEX IX_TransactionHistory_TransactionDate
ON Production.TransactionHistory
REBUILD Partition = 5;
GO

См. также

Справочник

CREATE INDEX (Transact-SQL)
sys.dm_db_index_physical_stats
EVENTDATA (Transact-SQL)

Другие ресурсы

Отключение индексов
Индексы для столбцов типа данных xml
Оперативное выполнение операций над индексом
Реорганизация и перестроение индексов

Справка и поддержка

Получение помощи по SQL Server 2005