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

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

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

    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, перестроение всех секций.

    ПредупреждениеВнимание!

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

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

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

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

  • DISABLE
    Помечает индекс как отключенный и недоступный для использования компонентом Ядро СУБД. Любой индекс может быть отключен. Определение отключенного индекса остается в системном каталоге без базовых индексных данных. Отключение кластеризованного индекса блокирует доступ пользователя к данным базовой таблицы. Чтобы активировать индекс, следует использовать инструкцию ALTER INDEX REBUILD или CREATE INDEX WITH DROP_EXISTING. Дополнительные сведения см. в разделах Отключение индексов и ограничений и Включение индексов и ограничений.

  • REORGANIZE
    Указывает, что конечный уровень индекса будет реорганизован. Инструкция 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.

    • ВКЛЮЧЕНА
      Все страницы, содержащие данные большого объекта, сжимаются.

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

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

    • ОТКЛЮЧЕНА
      Все страницы, содержащие данные большого объекта, не сжимаются.

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

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

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

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

    • ВКЛЮЧЕНА
      Процент свободного места, определяемый параметром FILLFACTOR, применяется к страницам индекса промежуточного уровня. Если FILLFACTOR не указан и одновременно PAD_INDEX установлен в состояние ON, то используется значение коэффициента заполнения, хранимое в таблице sys.indexes.

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

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

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

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

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

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

    Увидеть коэффициент заполнения можно в таблице sys.indexes.

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

    Создание или замена кластеризованного индекса со значением FILLFACTOR влияет на пространство памяти, занимаемое данными, так как компонент Ядро СУБД перераспределяет данные при создании кластеризованного индекса.

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

    • ВКЛЮЧЕНА
      Промежуточные результаты сортировки, которые используются при индексировании, хранятся в базе данных tempdb. Это может сократить время, требуемое для создания индекса, если база данных tempdb размещена на иных дисках, нежели пользовательская база данных. Однако это увеличивает использование места на диске, которое используется при индексировании.

    • ОТКЛЮЧЕНА
      Промежуточные результаты сортировки хранятся в той же базе данных, где и индекс.

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

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

  • IGNORE_DUP_KEY = { ON | OFF }
    Определяет ответ на ошибку, случающуюся, когда операция вставки пытается вставить в уникальный индекс повторяющиеся значения ключа. Параметр IGNORE_DUP_KEY применяется только к операциям вставки, производимым после создания или перестроения индекса. Значение по умолчанию — 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.

    • ВКЛЮЧЕНА
      Устаревшие статистики не пересчитываются автоматически.

    • ОТКЛЮЧЕНА
      Автоматическое обновление статистических данных включено.

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

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

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

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

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

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

    Операции с индексами в сети доступны не во всех выпусках Microsoft SQL Server. Список функций, поддерживаемых в разных выпусках SQL Server, см. в разделе Возможности, поддерживаемые различными выпусками SQL Server 2012.

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

    • ОТКЛЮЧЕНА
      Блокировки таблиц применяются во время выполнения операций с индексами. Операция с индексами в режиме «вне сети», которая создает, перестраивает или удаляет кластеризованный, пространственный или XML-индекс либо перестраивает или удаляет некластеризованный индекс, получает блокировку изменения схемы (Sch-M) для этой таблицы. Это предотвращает доступ к базовой таблице всех пользователей во время операции. Операция с индексами вне сети, создающая некластеризованный индекс, получает совмещаемую блокировку (S) в таблице. Это запрещает проводить обновления базовой таблицы, но разрешает проводить операции чтения, например инструкции SELECT.

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

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

    • XML-индексов

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

    • подмножество секционированного индекса (секционированный индекс можно целиком перестроить в сети).

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

    • ВКЛЮЧЕНА
      Блокировки строк допустимы при доступе к индексу. Компонент Ядро СУБД определяет, когда используются блокировки строк.

    • ОТКЛЮЧЕНА
      Блокировки строк не используются.

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

    • ВКЛЮЧЕНА
      Блокировки страниц допустимы при доступе к индексу. Необходимость в блокировке страниц определяет компонент Ядро СУБД.

    • ОТКЛЮЧЕНА
      Блокировки страниц не используются.

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

    Индекс не может быть реорганизован, если 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 (по умолчанию)
      В зависимости от текущей рабочей нагрузки системы использует реальное или меньшее число процессоров.

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

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

    Параллельные операции с индексами доступны не во всех выпусках Microsoft SQL Server. Список функций, поддерживаемых в разных выпусках SQL Server, см. в разделе Возможности, поддерживаемые различными выпусками SQL Server 2012.

  • 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 или большим числом экстентов, то компонент Ядро СУБД откладывает процедуры освобождения страниц и связанные с ними блокировки до фиксации транзакции.

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

Статистические данные в SQL Server 2012 не создаются путем сканирования всех строк таблицы при создании или перестроении секционированного индекса. Вместо этого оптимизатор запросов использует для создания статистики алгоритм выборки по умолчанию. Для получения статистики по секционированным индексам путем сканирования всех строк таблицы используйте инструкции CREATE STATISTICS или UPDATE STATISTICS с предложением FULLSCAN.

В предыдущих версиях 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, при доступе к индексу допустимы блокировки на уровне строк, уровне страниц и уровне таблиц. Компонент Ядро СУБД выбирает соответствующую блокировку и может повышать уровень с блокировки строки или страницы до блокировки таблицы.

Если присвоены значения 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) для страниц. Компонент Ядро СУБД может запросить намеренную блокировку страницы (IS, IU или IX) для внутренних целей.

Операции с индексами в сети

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

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

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

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

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

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

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

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

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

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

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

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

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

Ограничения индекса Columnstore

За исключением параметра REBUILD, индекс columnstore с оптимизацией для памяти xVelocity не может быть изменен. Вместо этого удалите и заново создайте индекс columnstore.

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

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

Оценить состояние сжатия таблицы, индекса или секции можно с помощью хранимой процедуры 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 AdventureWorks2012;
GO
ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD;
GO

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

См. также

Справочник

CREATE INDEX (Transact-SQL)

CREATE SPATIAL INDEX (Transact-SQL)

CREATE XML INDEX (Transact-SQL)

DROP INDEX (Transact-SQL)

sys.dm_db_index_physical_stats (Transact-SQL)

EVENTDATA (Transact-SQL)

Основные понятия

Отключение индексов и ограничений

XML-индексы (SQL Server)

Выполнение операции с индексами в сети

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