ALTER TABLE index_option (Transact-SQL)

Статья
05/21/2024

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure

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

Полное описание параметров индекса см. в разделе CREATE INDEX.

Соглашения о синтаксисе 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 } )
}

Аргументы

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 }

Отключите или включите параметр автоматического обновления статистики для статистики, AUTO_STATISTICS_UPDATEсвязанной с указанными индексами. Значение по умолчанию — OFF.

DNS

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

Автоматические обновления статистики включены после перестроения индекса.

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

Предупреждение

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

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

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) удерживается в исходной таблице. Это включает запросы или обновления применительно к обрабатываемой базовой таблице и индексам. В начале операции блокировка shared (S) хранится в исходном объекте в течение короткого периода времени. В конце операции в течение короткого периода времени блокировка S (Shared) приобретается в источнике, если создается некластеризованный индекс; или блокировка 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 2022.

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) указывает время (целочисленное значение, указанное в минутах), которое выполняется повторной операцией ограничения в сети перед приостановкой. Если этот параметр не указан, операция продолжается вплоть до завершения. MAXDOP также поддерживается RESUMABLE = ON .

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

MAXDOP = max_degree_of_parallelism

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

Переопределяет параметр конфигурации максимальной степени параллелизма на время выполнения операции с индексами. Дополнительные сведения см. в разделе "Настройка максимальной степени параллелизма" (параметр конфигурации сервера). Используется MAXDOP для ограничения количества процессоров, используемых в параллельном выполнении плана. Максимальное число процессоров — 64.

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

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

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

Примечание.

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

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 = время [ МИНУТы ]

Время ожидания (целочисленное значение, указанное в минутах), SWITCH которое необходимо получить или в сети, ожидает при выполнении команды DDL. Операция SWITCH перестроения индекса в сети выполняется немедленно. Если операция заблокирована в течение MAX_DURATION времени, выполняется одно из ABORT_AFTER_WAIT действий. MAX_DURATION время всегда находится в минутах, и слово MINUTES может быть опущено.

ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS }

NONE

SWITCH Продолжает операцию перестроения индекса в Сети без изменения приоритета блокировки (с помощью регулярного приоритета).
SELF

Завершает SWITCH операцию перестроения DDL или в сети, не выполняя никаких действий.
BLOCKERS

Удаляет все транзакции пользователей, которые блокируют в настоящее время SWITCH операцию перестроения DDL или онлайн-индекса, чтобы операция продолжалось.

BLOCKERSALTER ANY CONNECTION требует разрешения.

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

ALTER TABLE index_option (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 }

ONLINE = { ON | OFF }

RESUMABLE = { ON | OFF}

MAXDOP = max_degree_of_parallelism

DATA_COMPRESSION

XML_COMPRESSION

ON PARTITIONS ( { <partition_number_expression> | <range> } [ ,...n ] )

<single_partition_rebuild__option>

low_priority_lock_wait

MAX_DURATION = время [ МИНУТы ]

ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS }

Обратная связь

Дополнительные ресурсы

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

ALTER TABLE index_option (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 }

ONLINE = { ON | OFF }

RESUMABLE = { ON | OFF}

MAXDOP = max_degree_of_parallelism

DATA_COMPRESSION

XML_COMPRESSION

ON PARTITIONS ( { <partition_number_expression> | <range> } [ ,...n ] )

<single_partition_rebuild__option>

low_priority_lock_wait

MAX_DURATION = время [ МИНУТы ]

ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS }

Связанный контент

Обратная связь

Дополнительные ресурсы