ALTER TABLE index_option (Transact-SQL)

  • Статья

Применимо к:SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure

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

Соглашения о синтаксисе Transact-SQL

Синтаксис

{   
    PAD_INDEX = { ON | OFF }  
  | FILLFACTOR = fillfactor  
  | IGNORE_DUP_KEY = { ON | OFF }  
  | STATISTICS_NORECOMPUTE = { ON | OFF }  
  | ALLOW_ROW_LOCKS = { ON | OFF }  
  | ALLOW_PAGE_LOCKS = { ON | OFF } 
  | OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF } 
  | SORT_IN_TEMPDB = { ON | OFF }   
  | MAXDOP = max_degree_of_parallelism  
  | DATA_COMPRESSION = { NONE |ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE }  
      [ ON PARTITIONS ({ <partition_number_expression> | <range> }   
      [ , ...n ] ) ]  
  | XML_COMPRESSION = { ON | OFF }  
      [ ON PARTITIONS ({ <partition_number_expression> | <range> }   
      [ , ...n ] ) ]  
  | ONLINE = { ON | OFF }  
  | RESUMABLE = { ON | OFF }
  | MAX_DURATION = <time> [MINUTES]
}  
  
<range> ::=   
<partition_number_expression> TO <partition_number_expression>  
  
<single_partition_rebuild__option> ::=  
{  
    SORT_IN_TEMPDB = { ON | OFF }  
  | MAXDOP = max_degree_of_parallelism  
  | DATA_COMPRESSION = {NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE } }  
  | ONLINE = { ON [ ( <low_priority_lock_wait> ) ] | OFF }  
}  
  
<low_priority_lock_wait>::=  
{  
    WAIT_AT_LOW_PRIORITY ( MAX_DURATION = <time> [ MINUTES ] ,   
                           ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS } )   
}

Примечание.

Сведения о синтаксисе Transact-SQL для SQL Server 2014 (12.x) и более ранних версиях см . в документации по предыдущим версиям.

Аргументы

PAD_INDEX = { ON | OFF }
Применимо: SQL Server 2008 (10.0.x) и более поздних версий.

Определяет разреженность индекса. Значение по умолчанию — OFF.

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

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

FILLFACTOR =fillfactor
Применимо: SQL Server 2008 (10.0.x) и более поздних версий.

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

Примечание.

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

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

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

ВЫКЛ.
Если в уникальный индекс вставляются повторяющиеся значения ключа, выводится сообщение об ошибке. Выполняется откат всей операции 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.

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

ВЫКЛ.
Автоматическое обновление статистических данных включено.

ALLOW_ROW_LOCKS = { ON | OFF }
Применимо: SQL Server 2008 (10.0.x) и более поздних версий.

Указывает, разрешена ли блокировка строк. Значение по умолчанию — ON.

DNS
Блокировки строк допустимы при доступе к индексу. Ядро СУБД определяет, когда используются блокировки строк.

ВЫКЛ.
Блокировки строк не используются.

ALLOW_PAGE_LOCKS = { ON | OFF }
Применимо: SQL Server 2008 (10.0.x) и более поздних версий.

Указывает, разрешена ли блокировка страниц. Значение по умолчанию — ON.

DNS
Блокировки страниц возможны при доступе к индексу. Ядро СУБД определяет, когда используются блокировки страниц.

ВЫКЛ.
Блокировки страниц не используются.

OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }

Область применения: SQL Server 2019 (15.x) и более поздних версий.

Определяет, следует ли выполнять оптимизацию, связанную с состязанием при операциях вставки на последнюю страницу. Значение по умолчанию — OFF. См. подробнее раздел о последовательных ключах в документации по CREATE INDEX.

SORT_IN_TEMPDB = { ON | OFF }
Применимо: SQL Server 2008 (10.0.x) и более поздних версий.

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

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

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

ONLINE = { ON | OFF }
Применимо: SQL Server 2008 (10.0.x) и более поздних версий.

Определяет, будут ли базовые таблицы и связанные индексы доступны для запросов и изменения данных во время операций с индексами. Значение по умолчанию — OFF. REBUILD может выполняться как операция в режиме ONLINE.

Примечание.

Уникальные некластеризованные индексы нельзя создавать в режиме в сети. К ним относятся индексы, создаваемые из-за ограничений UNIQUE и PRIMARY KEY.

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

Примечание.

Перестроение индекса в режиме "в сети" может задать параметры low_priority_lock_wait, описанные ниже в этом разделе. low_priority_lock_wait управляет приоритетом блокировки S и Sch-M во время операции перестроения индекса в режиме "в сети".

ВЫКЛ.
Блокировки таблиц применяются во время выполнения операций с индексами. Это предотвращает доступ к базовой таблице всех пользователей во время операции. Блокировку изменения схемы (Sch-M) в таблице получает операция с индексами вне сети, которая создает, перестраивает или удаляет кластеризованный индекс либо перестраивает или удаляет некластеризованный индекс. Это предотвращает доступ к базовой таблице всех пользователей во время операции. Операция с индексами вне сети, создающая некластеризованный индекс, получает совмещаемую блокировку (S) в таблице. Это запрещает проводить обновления базовой таблицы, но разрешает проводить операции чтения, например инструкции SELECT.

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

Примечание.

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

RESUMABLE = { ON | OFF}
Область применения: SQL Server 2022 (16.x) и более поздних версий.

Указывает, является ли операция ALTER TABLE ADD CONSTRAINT возобновляемой. Операция добавления табличного ограничения будет возобновляемой при значении ON. Операция добавления табличного ограничения не будет возобновляемой при значении OFF. По умолчанию — OFF. Если параметру RESUMABLE задано значение ON, то требуется параметр ONLINE = ON.

MAX_DURATION при использовании с RESUMABLE = ON (требуется ONLINE = ON) указывает время (целочисленное значение в минутах) для выполнения возобновляемой операции добавления ограничения в online-режиме, пока она не будет приостановлена. Если этот параметр не указан, операция продолжается вплоть до завершения. MAXDOP также поддерживается при RESUMABLE = ON.

Дополнительные сведения об активации и использовании возобновляемых операций ALTER TABLE ADD CONSTRAINT: Возобновляемое добавление табличных ограничений.

MAXDOP =max_degree_of_parallelism
Применимо: SQL Server 2008 (10.0.x) и более поздних версий.

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

Параметр max_degree_of_parallelism может иметь одно из следующих значений:

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

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

Примечание.

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

DATA_COMPRESSION
Применимо: SQL Server 2008 (10.0.x) и более поздних версий.

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

NONE
Таблица или указанные секции не сжимаются. Применяется только к таблицам rowstore; не относится к таблицам columnstore.

ROW
Таблицы или указанные секции сжимаются, используя сжатие строк. Применяется только к таблицам rowstore; не относится к таблицам columnstore.

СТРАНИЦА
Таблицы или указанные секции сжимаются, используя сжатие страниц. Применяется только к таблицам rowstore; не относится к таблицам columnstore.

COLUMNSTORE
Область применения: SQL Server 2014 (12.x) и более поздних версий.

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

COLUMNSTORE_ARCHIVE
Область применения: SQL Server 2014 (12.x) и более поздних версий.

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

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

XML_COMPRESSION
Область применения: SQL Server 2022 (16.x) и более поздних версий, База данных SQL Azure и Управляемый экземпляр SQL Azure.

Задает параметр сжатия XML для всех столбцов с типом данных xml в таблице. Существуют следующие варианты выбора.

DNS
Столбцы, использующие тип данных xml, сжимаются.

ВЫКЛ.
Столбцы, использующие тип данных xml, не сжимаются.

ON PARTITIONS ( { <partition_number_expression> | <range> } [ ,...n ] )
Применимо: SQL Server 2008 (10.0.x) и более поздних версий.

Указывает секции, к которым применяются параметры DATA_COMPRESSION или XML_COMPRESSION. Если таблица не секционирована, аргумент ON PARTITIONS создаст ошибку. Если предложение ON PARTITIONS не указано, параметры DATA_COMPRESSION или XML_COMPRESSION применяется ко всем секциям секционированной таблицы.

<partition_number_expression> можно указать одним из следующих способов:

  • Указать номер секции, например ON PARTITIONS (2).
  • Указать номера нескольких секций через запятые, например ON PARTITIONS (1, 5).
  • Указать диапазоны и отдельные секции, например: ON PARTITIONS (2, 4, 6 TO 8).

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

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

--For rowstore tables  
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)  
)  
  
--For columnstore tables  
REBUILD WITH   
(  
  DATA_COMPRESSION = COLUMNSTORE ON PARTITIONS (1, 3, 5),   
  DATA_COMPRESSION = COLUMNSTORE_ARCHIVE ON PARTITIONS (2, 4, 6 TO 8)  
)

<single_partition_rebuild__option>
В большинстве случаев при перестроении индекса также перестраиваются все секции секционированного индекса. Следующие параметры при применении к одной секции не перестраивают все секции.

  • SORT_IN_TEMPDB
  • MAXDOP
  • DATA_COMPRESSION
  • XML_COMPRESSION

low_priority_lock_wait
Область применения: SQL Server 2014 (12.x) и более поздних версий.

SWITCH или перестроение индекса в сети завершается, как только не остается блокирующих таблицу операций. WAIT_AT_LOW_PRIORITY указывает на то, что если операция SWITCH либо операция перестроения индекса в сети не могут быть выполнены немедленно, то они переводятся в режим ожидания. Операция удерживает блокировки с низким приоритетом, чтобы другие операции, удерживающие блокировки, конфликтующие с инструкцией DDL, могли выполняться дальше. Пропуск параметра WAIT AT LOW PRIORITY эквивалентен WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE).

MAX_DURATION = time [MINUTES]
Время ожидания (целочисленное значение, указанное в минутах) при выполнении команды DDL для операции SWITCH или получаемой блокировки по операции перестроения индекса в режиме "в сети". Будет выполнена попытка немедленного запуска операции SWITCH или перестроения индекса в режиме «в сети». Если операция будет заблокирована на время MAX_DURATION, будет выполнено одно из действий ABORT_AFTER_WAIT. Время MAX_DURATION всегда указывается в минутах, и слово MINUTES можно опустить.

ABORT_AFTER_WAIT = [NONE | SELF | BLOCKERS } ]
NONE
Продолжение операции SWITCH или перестроение индекса в сети без изменения приоритета блокировки (с помощью обычного приоритета).

САМО-
Прекращение операции SWITCH или DDL по перестроению индекса в сети, выполняемой в данный момент, без какого-либо действия.

BLOCKERS
Остановка всех пользовательских транзакций, в данный момент блокирующих операцию SWITCH или операцию DDL по перестроению индекса в сети, чтобы можно было продолжить данную операцию.
BLOCKERS необходимо разрешение ALTER ANY CONNECTION.

Замечания

Полное описание параметров индекса см. в разделе CREATE INDEX (Transact-SQL).

См. также

Инструкция ALTER TABLE (Transact-SQL)
column_constraint (Transact-SQL)
computed_column_definition (Transact-SQL)
table_constraint (Transact-SQL)