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

  • Статья

ALTER INDEX (Transact-SQL)

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

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

Синтаксис

ALTER INDEX { index_name | ALL }
    ON <object>
    { REBUILD 
        [ [PARTITION = ALL]
          [ 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
  | DATA_COMPRESSION = { NONE | ROW | PAGE } 
     [ ON PARTITIONS ( { <partition_number_expression> | <range> } 
     [ , ...n ] ) ]
}
<range> ::= 
<partition_number_expression> TO <partition_number_expression>
}

<single_partition_rebuild_index_option> ::=
{
    SORT_IN_TEMPDB = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE | ROW | PAGE } }
}

<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-индекс

    IGNORE_DUP_KEY = ON

    Пространственный индекс

    XML-индекс

    ONLINE = ON

    Пространственный индекс

    XML-индекс

    Если ключевое слово 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, применяется значение по умолчанию, указанное в определении аргумента.

    При перестроении XML-индекса или пространственного индекса параметры ONLINE = ON и IGNORE_DUP_KEY = ON недопустимы.

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

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

    ПримечаниеПримечание

    При перестроении первичного XML-индекса индексированная пользовательская таблица недоступна в течение действия операции с индексами.

  • PARTITION
    Указывает, что только одна секция индекса будет перестроена или реорганизована. PARTITION не может быть указана, если аргумент index_name — несекционированный индекс.

    PARTITION = ALL, перестроение всех секций.

  • partition_number
    Номер секции секционированного индекса, который предстоит перестроить или реорганизовать partition_number, — константное выражение, которое может ссылаться на переменные. К ним относятся переменные определяемого пользователем типа или функции и определяемые пользователем функции, но нельзя ссылаться на инструкцию языка Transact-SQL. Должен существовать partition_number , или выполнение инструкции завершится с ошибкой.

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

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

  • DISABLE
    Помечает индекс как отключенный и недоступный для использования компонентом 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.

    ПримечаниеПримечание

    Значения коэффициентов заполнения 0 и 100 идентичны.

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

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

    Важное примечаниеВажно!

    Создание или замена кластеризованного индекса со значением FILLFACTOR влияет на пространство памяти, занимаемое данными, так как компонент Database Engine перераспределяет данные при создании кластеризованного индекса.

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

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

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

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

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

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

    • ON
      Если в уникальный индекс вставляются повторяющиеся значения ключа, выводится предупреждающее сообщение. С ошибкой завершаются только строки, нарушающие ограничение уникальности.

    • OFF
      Если в уникальный индекс вставляются повторяющиеся значения ключа, выводится сообщение об ошибке. Будет выполнен откат всей операции INSERT.

    IGNORE_DUP_KEY нельзя установить в значение ON для индексов, создаваемых для представлений, неуникальных индексов, XML-индексов, пространственных индексов и фильтруемых индексов.

    Для просмотра значения IGNORE_DUP_KEY используйте sys.indexes.

    Для обратной совместимости синтаксиса аргумент WITH IGNORE_DUP_KEY эквивалентен аргументу WITH IGNORE_DUP_KEY = ON.

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

    • ON
      Устаревшие статистики не пересчитываются автоматически.

    • OFF
      Автоматическое обновление статистических данных включено.

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

    Важное примечаниеВажно!

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

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

    Для XML-индекса или пространственного индекса поддерживается только значение ONLINE = OFF; при ONLINE = ON возникает ошибка.

    ПримечаниеПримечание

    Операции с индексами в сети доступны только в выпусках SQL Server Enterprise, Developer и Evaluation.

    • ON
      Долгосрочные блокировки таблицы не поддерживаются во время операций с индексами. Во время главной фазы операций с индексами только блокировка с намерением совмещаемого доступа (IS) удерживается в исходной таблице. Это позволяет продолжить выполнение запросов или обновлений для базовых таблиц и индексов. В начале операции совмещаемая блокировка (S) исходного объекта поддерживается в течение очень короткого времени. Если создается некластеризованный индекс, то по завершении операции на короткое время создается блокировка типа S (совмещаемая) для источника. Блокировка типа SCH-M (изменения схемы) запрашивается, если кластеризованный индекс создается или удаляется в режиме в сети либо, происходит перестроение кластеризованного или некластеризованного индекса. При создании индекса для временной локальной таблицы параметр ONLINE не может принимать значение ON.

    • OFF
      Блокировки таблиц применяются во время выполнения операций с индексами. Операция с индексами в режиме «вне сети», которая создает, перестраивает или удаляет кластеризованный, пространственный или XML-индекс либо перестраивает или удаляет некластеризованный индекс, получает блокировку изменения схемы (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
      Блокировки страниц не используются.

    ПримечаниеПримечание

    Индекс не может быть реорганизован, если ALLOW_PAGE_LOCKS установлен в состояние OFF.

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

    Важное примечаниеВажно!

    Хотя параметр MAXDOP синтаксически поддерживается для всех индексов XML, для пространственного или первичного XML-индекса инструкция ALTER INDEX в настоящее время использует только один процессор.

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

    • 1
      Подавляет формирование параллельных планов.

    • >1
      Ограничивает указанным значением максимальное число процессоров, используемых для параллельных операций с индексами.

    • 0 (по умолчанию)
      В зависимости от текущей рабочей нагрузки системы использует реальное или меньшее число процессоров.

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

    ПримечаниеПримечание

    Параллельные операции с индексами доступны только в выпусках SQL Server Developer, Evaluation и Enterprise.

  • DATA_COMPRESSION
    Задает режим сжатия данных для указанного индекса, номера секции или диапазона секций. Ниже приведены доступные параметры.

    • NONE
      Индекс или заданные секции не сжимаются.

    • ROW
      Для индекса или заданных секций производится сжатие строк.

    • PAGE
      Для индекса или заданных секций производится сжатие страниц.

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

  • ON PARTITIONS ( { <partition_number_expression> | <range> } [,...n] )
    Указывает секции, к которым применяется параметр DATA_COMPRESSION. Если индекс не секционирован, аргумент ON PARTITIONS создаст ошибку. Если не указано предложение ON PARTITIONS, то параметр DATA_COMPRESSION применяется ко всем секциям секционированного индекса.

    <Выражение_номера_секции> можно указать одним из следующих способов.

    • Указав номер секции, например ON PARTITIONS (2).

    • Указав номера нескольких секций, разделив их запятыми, например ON PARTITIONS (1, 5).

    • Предоставляет как диапазоны секций, так и отдельные секции, например ON PARTITIONS (2, 4, 6 TO 8).

    <Диапазон> можно указать номерами секций, разделенными ключевым словом TO, например ON PARTITIONS (6 TO 8).

    Чтобы для разных секций задать разные типы сжатия данных, укажите параметр DATA_COMPRESSION несколько раз, например следующим образом.

    REBUILD WITH 
(
DATA_COMPRESSION = NONE ON PARTITIONS (1), 
DATA_COMPRESSION = ROW ON PARTITIONS (2, 4, 6 TO 8), 
DATA_COMPRESSION = PAGE ON PARTITIONS (3, 5)
)

Замечания

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

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

ПримечаниеПримечание

Значения для параметров ONLINE, MAXDOP и SORT_IN_TEMPDB не хранятся в системном каталоге. Если значение некоторого параметра не указано в инструкции индекса, то используется значение по умолчанию.

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

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

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

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

ПримечаниеПримечание

Перестроение или реорганизация малых индексов часто не приводит к уменьшению фрагментации. Страницы индексов малого размера хранятся в смешанных экстентах. Смешанные экстенты могут находиться в общем пользовании у восьми объектов, поэтому фрагментацию в малом индексе нельзя уменьшить путем его реорганизации или перестроения. Дополнительные сведения о смешанных экстентах см. в разделе Страницы и экстенты.

В предыдущих версиях SQL Server иногда можно было перестроить некластеризованный индекс, чтобы исправить любые несоответствия, вызванные отказами оборудования. В SQL Server 2008 по-прежнему можно скорректировать такие несоответствия между индексом и кластеризованным индексом, перестроив некластеризованный индекс в режиме вне сети. Однако нельзя устранить несоответствия некластеризованного индекса, перестроив индекс в режиме в сети, потому что механизм перестроения в этом режиме будет использовать существующий некластеризованный индекс в качестве основы для перестроения и тем самым закрепит несоответствие. При перестроении индекса вне сети, в отличие от вышесказанного, принудительно запускается просмотр кластеризованного индекса (или кучи) и в результате устраняются несоответствия. В предыдущих версиях рекомендованным методом устранения несоответствий было восстановление неправильных данных из резервных копий, однако исправить несоответствия индекса можно, перестроив некластеризованный индекс в режиме «вне сети». Дополнительные сведения см. в разделе 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, то базовые объекты, таблицы и связанные с ними индексы доступны для запросов и изменения данных. Монопольные блокировки таблиц удерживаются лишь на очень короткое время в процессе изменения.

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

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

  • создание нескольких некластеризованных индексов;

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

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

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

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

Ограничения пространственного индекса

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

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

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

Чтобы изменить параметры, характерные для пространственного индекса (такие как BOUNDING_BOX или GRID), необходимо либо применить инструкцию CREATE SPATIAL INDEX с параметром DROP_EXISTING = ON, либо удалить пространственный индекс и создать новый. См. пример в разделе CREATE SPATIAL INDEX (Transact-SQL).

Сжатие данных

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

Оценить состояние сжатия таблицы, индекса или секции можно с помощью хранимой процедуры sp_estimate_data_compression_savings.

На секционированные индексы налагаются следующие ограничения.

  • Если у таблицы есть невыровненные индексы, то изменить настройку сжатия отдельной секции с помощью инструкции ALTER INDEX ALL невозможно.

  • Инструкция ALTER INDEX <index> ... Инструкция REBUILD PARTITION ... производит перестроение указанной секции индекса.

  • Инструкция ALTER INDEX <index> ... Инструкция REBUILD WITH ... производит перестроение всех секций индекса.

Статистика

При выполнении ALTER INDEX ALL … в таблице обновляется только статистика, связанная с индексами. Статистика, автоматически или вручную созданная для таблицы (кроме индекса), не обновляется.

Разрешения

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

Примеры

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

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

USE AdventureWorks2008R2;
GO
ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD;
GO

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

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

USE AdventureWorks2008R2;
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 AdventureWorks2008R2;
GO
ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto
REORGANIZE ;
GO

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

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

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

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

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

USE AdventureWorks2008R2;
GO
ALTER INDEX IX_Employee_OrganizationNode ON HumanResources.Employee
DISABLE ;
GO

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

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

USE AdventureWorks2008R2;
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 AdventureWorks2008R2;
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. В данном примере подразумевается, что образец секционированного индекса установлен.

USE AdventureWorks2008R2;
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

И. Изменение настроек сжатия индекса

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

ALTER INDEX IX_INDEX1 
ON T1
REBUILD 
WITH ( DATA_COMPRESSION = PAGE )
GO

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