CREATE INDEX (Transact-SQL)

Применимо:SQL Server База данных SQL Azure Управляемый экземпляр SQL Azureazure Synapse Analytics AnalyticsPlatform System (PDW)

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

Azure Synapse Analytics и Система платформы аналитики (PDW) в настоящее время не поддерживает ограничения уникальности. Любые примеры, ссылающиеся на уникальные ограничения, применимы только к SQL Server, Базе данных SQL Azure и Управляемому экземпляру SQL Azure.

Сведения о рекомендациях по проектированию индексов см. в руководстве по проектированию индексов SQL Server.

Примеры:

1. Создание некластеризованного индекса в таблице или представлении

   CREATE INDEX index1 ON schema1.table1 (column1);
   

2. Создание кластеризованного индекса в таблице и использование имени, состоящего из трех элементов, для таблицы

   CREATE CLUSTERED INDEX index1 ON database1.schema1.table1 (column1);
   

3. Создание некластеризованного индекса с ограничением уникальности и указание порядка сортировки

   CREATE UNIQUE INDEX index1 ON schema1.table1 (column1 DESC, column2 ASC, column3 DESC);
   

Основной сценарий.

Начиная с SQL Server 2016 (13.x), в Базе данных SQL Azure и в Управляемом экземпляре SQL Azure можно использовать некластеризованный индекс в индексе columnstore для повышения производительности запросов хранения данных. Дополнительные сведения см. в разделе индексов Columnstore — хранилище данных.

Дополнительные типы индексов описаны в следующих статьях:

• CREATE XML INDEX
• CREATE SPATIAL INDEX
• CREATE COLUMNSTORE INDEX

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

Синтаксис

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

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
    ON <object> ( column [ ASC | DESC ] [ ,...n ] )
    [ INCLUDE ( column_name [ ,...n ] ) ]
    [ WHERE <filter_predicate> ]
    [ WITH ( <relational_index_option> [ ,...n ] ) ]
    [ ON { partition_scheme_name ( column_name )
         | filegroup_name
         | default
         }
    ]
    [ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]

[ ; ]

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

<relational_index_option> ::=
{
    PAD_INDEX = { ON | OFF }
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB = { ON | OFF }
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | STATISTICS_INCREMENTAL = { ON | OFF }
  | DROP_EXISTING = { ON | OFF }
  | ONLINE = { ON [ ( <low_priority_lock_wait> ) ] | OFF }
  | RESUMABLE = { ON | OFF }
  | MAX_DURATION = <time> [MINUTES]
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE | ROW | PAGE }
     [ ON PARTITIONS ( { <partition_number_expression> | <range> }
     [ , ...n ] ) ]
  | XML_COMPRESSION = { ON | OFF }
     [ ON PARTITIONS ( { <partition_number_expression> | <range> }
     [ , ...n ] ) ]
}

<filter_predicate> ::=
    <conjunct> [ AND ] [ ...n ]

<conjunct> ::=
    <disjunct> | <comparison>

<disjunct> ::=
        column_name IN (constant ,...n)

<comparison> ::=
        column_name <comparison_op> constant

<comparison_op> ::=
    { IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !< }

<low_priority_lock_wait>::=
{
    WAIT_AT_LOW_PRIORITY ( MAX_DURATION = <time> [ MINUTES ] ,
                          ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS } )
}

<range> ::=
<partition_number_expression> TO <partition_number_expression>

Реляционный индекс с обратной совместимостью

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
    ON <object> ( column_name [ ASC | DESC ] [ ,...n ] )
    [ WITH <backward_compatible_index_option> [ ,...n ] ]
    [ ON { filegroup_name | "default" } ]

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

<backward_compatible_index_option> ::=
{
    PAD_INDEX
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB
  | IGNORE_DUP_KEY
  | STATISTICS_NORECOMPUTE
  | DROP_EXISTING
}

Синтаксис для Azure Synapse Analytics и Parallel Data Warehouse

CREATE CLUSTERED COLUMNSTORE INDEX index_name
    ON [ database_name . [ schema ] . | schema . ] table_name
    [ORDER (column[,...n])]
    [WITH ( DROP_EXISTING = { ON | OFF } )]
[;]


CREATE [ CLUSTERED | NONCLUSTERED ] INDEX index_name
    ON [ database_name . [ schema ] . | schema . ] table_name
        ( { column [ ASC | DESC ] } [ ,...n ] )
    WITH ( DROP_EXISTING = { ON | OFF } )
[;]

Аргументы

UNIQUE

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

Ядро СУБД не позволяет создавать уникальный индекс для столбцов, которые уже содержат повторяющиеся значения, независимо от того, задано ли IGNORE_DUP_KEY значение ON. Если эта попытка выполнена, ядро СУБД отображает сообщение об ошибке. Прежде чем создавать уникальный индекс по такому столбцу или столбцам, необходимо удалить все повторяющиеся значения.

UNIQUE Ограничение рассматривается NULL как значение. Если столбец имеет значение NULL и UNIQUE ограничение существует в столбце, допускается не более одной строки.NULL

CLUSTERED

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

Таблица может содержать только один кластеризованный индекс. Если кластеризованный индекс существует в таблице, он содержит все данные в таблице. Таблица без кластеризованного индекса называется кучей.

Представление с уникальным кластеризованным индексом называется индексированным. Индексированного представления может иметь только один кластеризованный индекс. Создание уникального кластеризованного индекса физически материализует представление. Уникальный кластеризованный индекс для представления должен быть создан до того, как для этого же представления будут определены какие-либо другие индексы. Дополнительные сведения см. в разделе "Создание индексированных представлений".

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

Если CLUSTERED не указан, создается некластеризованный индекс.

В некоторых случаях создание кластеризованного индекса может включать ранее отключенные индексы. Дополнительные сведения см. в разделе "Включить индексы и ограничения" , а также отключить индексы и ограничения.

NONCLUSTERED

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

Каждая таблица может содержать до 999 некластеризованных индексов независимо от того, как создаются индексы: неявно с PRIMARY KEYUNIQUE ограничениями или явным образом CREATE INDEX.

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

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

index_name

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

column

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

В один ключ составного индекса могут входить до 32 столбцов. Все столбцы ключа составного индекса должны находиться в одной таблице или одном и том же представлении. Максимально допустимый размер значений составного индекса составляет 900 байтов для кластеризованного индекса или 1700 для некластеризованного индекса. Ограничения — 16 столбцов и 900 байт для версий до База данных SQL и SQL Server 2016 (13.x).

Столбцы с типами данных LOB ntext, text, varchar(max), nvarchar(max), varbinary(max), xml или image нельзя указать в качестве столбцов для индекса. Кроме того, определение индексированного представления не может содержать столбцы ntext, текста или изображения , даже если они не ссылаются в инструкции CREATE INDEX .

Можно создавать индексы на столбцах с определяемым пользователем типом данных CLR, если этот тип поддерживает двоичное упорядочение. Можно также создавать индексы на вычисляемых столбцах, определенных как вызовы методов для столбцов с определяемыми пользователем типами данных, если эти методы помечены как детерминированные и не выполняют операции доступа к данным. Дополнительные сведения об индексировании столбцов определяемых пользователем типов СРЕДЫ CLR см. в разделе о определяемых пользователем типах СРЕДЫ CLR.

[ ASC | DESC ]

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

INCLUDE (column [ ,... n ] )

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

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

Допускаются данные всех типов, за исключением text, ntextи image. Начиная с SQL Server 2012 (11.x), в Базе данных SQL Azure и в Управляемом экземпляре SQL Azure, если один из указанных неключевых столбцов — varchar(max),nvarchar(max), или varbinary(max), индекс можно построить или перестроить с помощью ONLINE этого параметра.

Вычисляемые столбцы, являющиеся детерминированными и точными или неточными, могут быть включенными столбцами. Вычисляемые столбцы, производные от изображения, ntext, text, varchar(max), nvarchar(max), varbinary(max)и xml-типов данных могут быть включены, если тип данных вычисляемого столбца допускается как включенный столбец. Дополнительные сведения см. в разделе Индексы на вычисляемых столбцах.

Сведения о создании XML-индекса см. в описании CREATE XML INDEX.

WHERE <filter_predicate>

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

Предикат фильтра использует простую логику сравнения и не может ссылаться на вычисляемый столбец, определяемый пользователем столбец данных (UDT), столбец типа пространственных данных или столбец типа данных hierarchyid . Сравнения с литералами, NULL использующими операторы сравнения, не допускаются. Используйте вместо них операторы IS NULL и IS NOT NULL.

Далее приведено несколько примеров использования предикатов фильтра для таблицы Production.BillOfMaterials:

WHERE StartDate > '20000101' AND EndDate <= '20000630'

WHERE ComponentID IN (533, 324, 753)

WHERE StartDate IN ('20000404', '20000905') AND EndDate IS NOT NULL

Отфильтрованные индексы не применяются к XML-индексам и полнотекстовым индексам. Для UNIQUE индексов только выбранные строки должны иметь уникальные значения индекса. Отфильтрованные индексы не поддерживают параметр IGNORE_DUP_KEY.

ON partition_scheme_name ( column_name )

Задает схему секционирования, определяющую файловые группы, по которым сопоставляются секции секционированного индекса. Схема секционирования должна быть создана в базе данных путем выполнения инструкции CREATE PARTITION SCHEME или ALTER PARTITION SCHEME. column_name указывает столбец секционирования для индекса. Столбец должен соответствовать по типу данных, длине и точности аргументу функции секционирования, используемой аргументом partition_scheme_name. column_name необязательно должен соответствовать столбцам из определения индекса. Любой столбец в базовой таблице можно указать, за исключением секционирования уникального индекса, column_name необходимо выбрать из тех, которые используются в качестве уникального ключа. Это ограничение позволяет ядро СУБД проверять уникальность значений ключей только в одной секции.

Если partition_scheme_name или filegroup не задан, а таблица секционирована, индекс помещается в ту же схему секционирования и с тем же столбцом секционирования, что и для базовой таблицы.

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

ON filegroup_name

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

ON [по умолчанию]

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

Термин , в этом контексте, не является ключевым словом default. Это идентификатор файловой группы или секционированных схем таблицы или представления и должен быть разделен как в ON "default" или ON [default]. Если "default" задано, QUOTED_IDENTIFIER параметр должен быть ON для текущего сеанса. Этот параметр принимается по умолчанию. Дополнительные сведения см. в описании SET QUOTED_IDENTIFIER.

[ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]

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

Filestream_filegroup_name — это имя файловой группы FILESTREAM. В файловой группе должен быть определен один файл для файловой группы с помощью инструкции CREATE DATABASE или ALTER DATABASE, иначе возникает ошибка.

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

Если таблица не секционирована, столбец FILESTREAM не может быть секционирован. Данные FILESTREAM для этой таблицы необходимо хранить в отдельной файловой группе, указанной в предложении FILESTREAM_ON.

FILESTREAM_ON NULL можно указать в инструкции CREATE INDEX, если создается кластеризованный индекс и таблица не содержит столбец FILESTREAM.

Дополнительные сведения см. в разделе FILESTREAM (SQL Server).

<object>::=

Полное или неполное имя индексируемого объекта.

database_name

Имя базы данных.

schema_name

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

table_or_view_name

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

Чтобы создать индекс в представлении, представление должно быть определено с SCHEMABINDINGпомощью . Прежде чем создавать любой некластеризованный индекс для представления, необходимо создать уникальный кластеризованный индекс. Дополнительные сведения об индексированных представлениях см. в разделе "Примечания".

Начиная с SQL Server 2016 (13.x), объект может быть таблицей, хранящейся с кластеризованным индексом columnstore.

База данных SQL Azure поддерживает формат трех частей <database_name>.<schema_name>.<object_name>, если <database_name> является текущим именем базы данных или <database_name>tempdb и <object_name> начинается с # или ##. Если имя схемы dbo, <schema_name> можно опустить.

< >relational_index_option::=

Указывает параметры, которые должны использоваться при создании индекса.

PAD_INDEX = { ON | OFF }

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

• DNS

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

• ВЫКЛ.

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

Параметр PAD_INDEX полезен только при FILLFACTOR указании, так как PAD_INDEX использует процент, указанный в параметре FILLFACTOR. Если процент, указанный для FILLFACTOR не достаточно большой, чтобы разрешить одну строку, ядро СУБД внутренне переопределяет процент, чтобы обеспечить минимальное значение. Количество строк на промежуточной странице индекса никогда не меньше двух, независимо от того, насколько низко значение FILLFACTOR.

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

FILLFACTOR = fillfactor

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

Аргумент FILLFACTOR действует только при создании или перестройке индекса. Ядро СУБД не сохраняет динамически указанный процентный объем свободного места на страницах.

Чтобы просмотреть параметр коэффициента заполнения, используйте fill_factor столбец в представлении каталога sys.indexes .

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

SORT_IN_TEMPDB = { ON | OFF }

Указывает, следует ли хранить временные сортировки в tempdb. Значение по умолчанию за OFF исключением База данных SQL Azure гипермасштабирования. Для всех операций сборки индекса в гипермасштабировании SORT_IN_TEMPDB всегда ON, если не используется повторная сборка индекса. Для возобновления сборки индекса SORT_IN_TEMPDB всегда OFF.

• DNS

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

• ВЫКЛ.

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

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

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

IGNORE_DUP_KEY = { ON | OFF }

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

• DNS

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

• ВЫКЛ.

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

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

Чтобы просмотреть параметр IGNORE_DUP_KEY индекса, используйте столбец ignore_dup_key в представлении каталога sys.indexes.

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

STATISTICS_NORECOMPUTE = { ON | OFF}

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

• DNS

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

• ВЫКЛ.

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

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

Установка STATISTICS_NORECOMPUTE для ON не предотвращает обновление статистики индекса, которая возникает во время операции перестроения индекса.

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

STATISTICS_INCREMENTAL = { ON | OFF }

применимо к: SQL Server 2014 (12.x) и более поздним версиям, Базе данных SQL Azure и Управляемому экземпляру SQL Azure

При ONсоздании статистики для каждой секции создается статистика. При OFFудалении дерева статистики sql Server повторно вычисляет статистику. Значение по умолчанию — OFF.

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

• Статистики, созданные с индексами, не выровненными по секциям для базовой таблицы.
• Статистики, созданные в доступных для чтения базах данных-получателях AlwaysOn.
• Статистики, созданные в базах данных, доступных только для чтения.
• Статистики, созданные по фильтрованным индексам.
• Статистика, созданная по представлениям.
• Статистики, созданные по внутренним таблицам.
• Статистики, созданные с пространственными индексами или XML-индексами.

DROP_EXISTING = { ON | OFF }

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

• DNS

  Указывает удалить и перестроить существующий индекс, который должен иметь имя, совпадающее с именем параметра index_name.

• ВЫКЛ.

  Указывает не удалять и перестраивать существующий индекс. SQL Server отображает ошибку, если индекс с указанным именем уже существует.

С помощью DROP_EXISTING можно изменить:

• некластеризованный индекс rowstore на кластеризованный индекс rowstore.

В случае DROP_EXISTING нельзя изменить:

• кластеризованный индекс rowstore на некластеризованный индекс rowstore;
• кластеризованный индекс columnstore на индекс rowstore любого типа.

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

ONLINE = { ON | OFF }

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

• DNS

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

  Примечание.

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

• ВЫКЛ.

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

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

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

• XML-индекс
• Индекс локальной временной таблицы
• Исходные уникальные кластеризованные индексы представлений.
• Отключенные кластеризованные индексы.
• Кластеризованные индексы columnstore в SQL Server 2017 (14.x)) и более ранних версиях
• Некластеризованные индексы columnstore в SQL Server 2016 (13.x)) и более ранних версиях
• Кластеризованные индексы, если базовая таблица содержит типы данных LOB: image, ntext, text и пространственные типы данных.
• Столбцы varchar(max) и varbinary(max) не могут быть частью ключа индекса. В SQL Server (начиная с SQL Server 2012 (11.x)) в Базе данных SQL Azure и в Управляемом экземпляре SQL Azure, если таблица содержит столбцы varchar(max) или varbinary(max), кластеризованный индекс, содержащий другие столбцы, можно создать или перестроить с помощью этого ONLINE параметра.
• Некластеризованные индексы в таблице с кластеризованным индексом columnstore

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

RESUMABLE = { ON | OFF }

применяется к: SQL Server 2019 (15.x) и более поздним версиям, Базе данных SQL Azure и Управляемому экземпляру SQL Azure

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

• DNS

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

• ВЫКЛ.

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

MAX_DURATION = time [MINUTES] используется с RESUMABLE = ON (требуется ONLINE = ON)

применяется к: SQL Server 2019 (15.x) и более поздним версиям, Базе данных SQL Azure и Управляемому экземпляру SQL Azure

Указывает, сколько времени, в минутах, повторная операция индекса выполняется до приостановки.

ALLOW_ROW_LOCKS = { ON | OFF }

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

• DNS

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

• ВЫКЛ.

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

ALLOW_PAGE_LOCKS = { ON | OFF }

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

• DNS

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

• ВЫКЛ.

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

OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }

применяется к: SQL Server 2019 (15.x) и более поздним версиям, Базе данных SQL Azure и Управляемому экземпляру SQL Azure

Указывает, следует ли оптимизировать, чтобы избежать конфликтов при вставке последней страницы. Значение по умолчанию — OFF. Дополнительные сведения см. в разделе "Последовательные ключи ".

MAXDOP = max_degree_of_parallelism

Переопределяет параметр конфигурации максимальной степени параллелизма для операции индекса. Дополнительные сведения см. в разделе Настройка параметра конфигурации сервера max degree of parallelism. Используйте MAXDOP, чтобы ограничить степень параллелизма и результирующее потребление ресурсов для операции сборки индекса.

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

• 1

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

• >1

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

• 0 (по умолчанию)

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

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

DATA_COMPRESSION

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

• NONE

  Индекс или указанные секции не сжимаются. Это не относится к индексам columnstore.

• ROW

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

• СТРАНИЦА

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

• COLUMNSTORE

  применимо к: SQL Server 2014 (12.x) и более поздним версиям, Базе данных SQL Azure и Управляемому экземпляру SQL Azure

  Применяется только к индексам columnstore, включая некластеризованные и кластеризованные индексы columnstore.

• COLUMNSTORE_ARCHIVE

  применимо к: SQL Server 2014 (12.x) и более поздним версиям, Базе данных SQL Azure и Управляемому экземпляру SQL Azure

  Применяется только к индексам 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 ] )

Указывает разделы, к которым применяется параметр 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 несколько раз, например следующим образом:

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)
);

Параметр XML_COMPRESSION можно указывать несколько раз, например:

REBUILD WITH
(
XML_COMPRESSION = OFF ON PARTITIONS (1),
XML_COMPRESSION = ON ON PARTITIONS (2, 4, 6 TO 8),
XML_COMPRESSION = OFF ON PARTITIONS (3, 5)
);

Замечания

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

Операция CREATE INDEX может быть минимально зарегистрирована, если для модели восстановления базы данных задано значение bulk-logged или просто.

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

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

Индексы поддерживают расширенные свойства.

CREATE INDEX не поддерживается в Microsoft Fabric.

Кластеризованные индексы

Чтобы создать кластеризованный индекс для таблицы (кучи) или удалить и повторно создать существующий кластеризованный индекс, требуется дополнительная рабочая область в базе данных для сортировки и временного копирования данных исходной таблицы или существующего кластеризованного индекса. Дополнительные сведения о кластеризованных индексах см. в руководстве по созданию кластеризованных индексови архитектуре индекса SQL Server.

Некластеризованные индексы

Начиная с SQL Server 2016 (13.x), в Базе данных SQL Azure и в Управляемом экземпляре SQL Azure можно создать некластеризованный индекс в таблице, сохраненной как кластеризованный индекс columnstore. Если сначала создать некластеризованный индекс в таблице, хранящейся в куче или кластеризованном индексе, индекс сохраняется при последующем преобразовании таблицы в кластеризованный индекс columnstore. Кроме того, необязательно удалять некластеризованный индекс при перестройке кластеризованного индекса columnstore.

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

Уникальные индексы

При наличии уникального индекса ядро СУБД проверяет повторяющиеся значения при каждом добавлении или изменении данных. Операции, которые создают повторяющиеся значения ключей, откатываются, а ядро СУБД возвращает сообщение об ошибке. Это верно, даже если операция добавления или изменения данных изменяет множество строк, но вызывает только один дубликат. Если предпринята попытка вставки строк при наличии уникального индекса с IGNORE_DUP_KEY заданным параметром ON, строки, нарушающие уникальный индекс, игнорируются.

Секционированные индексы

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

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

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

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

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

Отфильтрованные индексы

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

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

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

• Создание фильтруемого индекса.

• Оператор INSERT, UPDATEDELETEили MERGE оператор изменяет данные в отфильтрованном индексе.

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

  параметр SET	Обязательное значение	Значение сервера по умолчанию	Значение OLE DB и ODBC по умолчанию	Значение DB-Library по умолчанию
  ANSI_NULLS	ON	ON	ON	OFF
  ANSI_PADDING	ON	ON	ON	OFF
  ANSI_WARNINGS 1	ON	ON	ON	OFF
  ARITHABORT	ON	ON	OFF	OFF
  CONCAT_NULL_YIELDS_NULL	ON	ON	ON	OFF
  NUMERIC_ROUNDABORT	OFF	OFF	OFF	OFF
  QUOTED_IDENTIFIER	ON	ON	ON	OFF

  ¹ Параметр ANSI_WARNINGS для ON неявно задает ONARITHABORT значение, если для уровня совместимости базы данных задано значение 90 или более поздней версии. Если для уровня совместимости базы данных задано значение 80 или более ранних версий, ARITHABORT параметр должен быть явно задан ON.

SET Если параметры неверны, могут возникнуть следующие условия:

• Создание отфильтрованного индекса завершается сбоем.
• Ядро СУБД создает ошибку и откатывает INSERTоператор , UPDATEDELETEMERGE который изменяет данные в индексе.
• Оптимизатор запросов не учитывает индекс в плане выполнения любой инструкции Transact-SQL.

Дополнительные сведения о отфильтрованных индексах см. в разделе "Создание отфильтрованные индексы " и руководства по архитектуре индекса SQL Server и проектированию.

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

Сведения о пространственных индексах см. в разделе CREATE SPATIAL INDEX and Spatial Indexes overview.

XML-индексы

Сведения об XML-индексах см. в описании CREATE XML INDEX и статье XML-индексы (SQL Server).

Размер ключа индекса

Максимальный размер ключа индекса составляет 900 байт для кластеризованного индекса и 1700 байт для некластеризованного индекса. (До Базы данных SQL и SQL Server 2016 (13.x) ограничение всегда составляло 900 байт.) Индексы столбцов varchar , превышающие ограничение байтов, можно создать, если существующие данные в столбцах не превышают предел во время создания индекса; однако последующие операции вставки или обновления столбцов, которые приводят к сбою общего размера, чем ограничение. Ключ индекса кластеризованного индекса не может содержать столбцы varchar , имеющие существующие данные в единице ROW_OVERFLOW_DATA выделения. Если кластеризованный индекс создается в столбце varchar и существующие данные находится в IN_ROW_DATA единице выделения, последующие операции вставки или обновления в столбце, который приведет к сбою данных вне строки.

Некластеризованные индексы могут включать столбцы, не относящиеся к ключу (включенным) на конечном уровне индекса. Эти столбцы не учитываются ядром СУБД при вычислении размера ключа индекса. Дополнительные сведения см. в разделе "Создание индексов с включенными столбцами " и архитектурой индекса SQL Server и руководством по проектированию.

Вычисляемые столбцы

Индексы могут создаваться в вычисляемых столбцах. Кроме того, вычисляемые столбцы могут иметь свойство PERSISTED. Это означает, что ядро СУБД сохраняет вычисляемые значения в таблице и обновляет их при обновлении любых других столбцов, от которых зависит вычисляемый столбец. Ядро СУБД использует эти сохраненные значения при создании индекса в столбце и при ссылке на индекс в запросе.

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

• Вычисляемые столбцы, основанные на Transact-SQL, функциях CLR и методах определяемых пользователем типов данных CLR, помеченных пользователем как детерминированные.
• Вычисляемые столбцы на основе выражений, детерминированных в соответствии с ядро СУБД, но неточными.

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

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

Вычисляемые столбцы, производные от типов данных image, ntext, text, varchar(max), nvarchar(max), varbinary(max) и xml, могут индексироваться как ключевой или неключевой столбец, если тип данных вычисляемого столбца допускается в качестве ключевого или неключевого столбца индекса. Например, нельзя создать первичный XML-индекс для вычисляемого столбца XML. Если размер ключа индекса превышает 900 байт, выдается предупреждение.

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

Например, в следующей таблице, хотя выражение вычисляемого столбца c , как представляется, приводит к арифметической ошибке при вставке строки, INSERT инструкция работает.

CREATE TABLE t1 (a INT, b INT, c AS a/b);
INSERT INTO t1 VALUES (1, 0);

Однако при создании индекса в вычисляемом столбце cпроисходит сбой той же INSERT инструкции.

CREATE TABLE t1 (a INT, b INT, c AS a/b);
CREATE UNIQUE CLUSTERED INDEX Idx1 ON t1(c);
INSERT INTO t1 VALUES (1, 0);

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

Включенные столбцы в индексах

Неключевые столбцы, называемые "включенными столбцами", могут добавляться на конечный уровень некластеризованного индекса для повышения производительности запроса благодаря тому, что индекс включает все необходимые данные для запроса. Т. е. все столбцы, указанные в запросе, включаются в индекс в качестве ключевых или неключевых столбцов. Это позволяет оптимизатору запросов получать все необходимые сведения из некластеризованного сканирования индекса или поиска; Доступ к данным таблицы или кластеризованного индекса недоступна. Дополнительные сведения см. в разделе "Создание индексов с включенными столбцами " и архитектурой индекса SQL Server и руководством по проектированию.

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

SQL Server 2005 (9.x) представил новые параметры индекса, а также изменил способ указания параметров. В синтаксисе WITH option_name с обратной совместимостью WITH (option_name = ON)эквивалентен . Устанавливая параметры индекса, необходимо соблюдать следующие правила.

• Новые параметры индекса могут быть заданы только с помощью аргумента WITH (<option_name> = <ON | OFF>).
• Нельзя задавать параметры с помощью нового синтаксиса и совместимого старого в одной и той же инструкции. Например, указание WITH (DROP_EXISTING, ONLINE = ON) приводит к сбою инструкции.
• При создании XML-индекса параметры должны указываться с помощью аргумента WITH (<option_name> = <ON | OFF>).

DROP_EXISTING, предложение

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

Если индекс принудительно применяет или UNIQUE ограничиваетPRIMARY KEY, а определение индекса не изменяется каким-либо образом, индекс удаляется и повторно создается, сохраняя существующее ограничение. Но если изменить определение индекса, инструкция вызовет ошибку. Чтобы изменить определение PRIMARY KEY или UNIQUE ограничение, удалите ограничение и добавьте ограничение с новым определением.

Предложение DROP_EXISTING повышает производительность, если повторно создается кластеризованный индекс с тем же самым или другим набором ключей в таблице, имеющей также некластеризованные индексы. Предложение DROP_EXISTING заменяет удаление старого кластеризованного индекса с помощью инструкции DROP INDEX и последующее создание нового кластеризованного индекса с помощью инструкции CREATE INDEX. Некластеризованные индексы перестраиваются один раз, а после этого только в случае, если меняется определение индекса. Предложение DROP_EXISTING не перестраивает некластеризованные индексы, если определение индекса содержит то же самое имя индекса, ключевые столбцы, столбцы секционирования, атрибут уникальности и порядок сортировки, что и исходный индекс.

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

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

ONLINE, параметр

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

• Во время выполнения операций с индексами в сети базовая таблица не может изменяться, усекаться или удаляться.
• Для операций с индексами требуется дополнительное временное место на диске.
• Обработка индексов в сети может выполняться для секционированных индексов, содержащих материализованные вычисляемые столбцы или включенные столбцы.
• Параметр WAIT_AT_LOW_PRIORITY аргумента позволяет решить, как выполняется операция индекса при ожидании Sch-M блокировки. Дополнительные сведения см. в WAIT_AT_LOW_PRIORITY

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

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

применяется к: SQL Server 2019 (15.x) и более поздним версиям, Базе данных SQL Azure и Управляемому экземпляру SQL Azure

Вы можете сделать операцию создания индекса в сети повторной. Это означает, что сборка индекса может быть остановлена и позже перезапущена с точки ее остановки. Чтобы запустить сборку индекса как возобновляемую RESUMABLE = ON , укажите параметр.

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

• Чтобы использовать параметр RESUMABLE, необходимо также использовать параметр ONLINE.
• Параметр RESUMABLE не сохраняется в метаданных заданного индекса и применяется только к длительности текущей инструкции DDL. Таким образом, для включения возобновляемости предложение RESUMABLE = ON должно быть указано явным образом.
• Параметр MAX_DURATION можно указать в двух контекстах:
  • MAX_DURATION RESUMABLE для параметра указывается интервал времени для перестраиваемого индекса. После этого истекает время, и если перестроение индекса по-прежнему выполняется, он приостановлен. Вы решите, когда можно возобновить перестроение приостановленного индекса. Время в минутах для MAX_DURATION должно быть больше 0 минут и меньше или равно одной неделе (7 * 24 * 60 = 10080 минут). Длинная пауза в операции индекса может заметно повлиять на производительность DML в определенной таблице, а также емкость диска базы данных, так как исходный индекс и только что созданный индекс требуют места на диске и должны быть обновлены операциями DML. Если параметр MAX_DURATION опущен, операция индекса продолжается до завершения или до тех пор, пока не произойдет сбой.
  • MAX_DURATION для параметра WAIT_AT_LOW_PRIORITY указывает время ожидания при блокировке с низким приоритетом, если операция индекса заблокирована, прежде чем принимать меры. Дополнительные сведения см. в WAIT_AT_LOW_PRIORITY с операциями с индексами в сети.
• Чтобы немедленно приостановить операцию индекса, можно выполнить команду ALTER INDEX PAUSE или выполнить команду KILL <session_id>.
• Повторное выполнение исходной CREATE INDEX инструкции с теми же параметрами возобновляет приостановленную операцию сборки индекса. Вы также можете возобновить приостановленную операцию сборки индекса, выполнив инструкцию ALTER INDEX RESUME .
• Команда ABORT убивает сеанс, выполняющий сборку индекса, и отменяет операцию индекса. Невозможно возобновить операцию индекса, которая была прервана.

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

Чтобы узнать, выполняется ли операция индекса в качестве повторной операции и чтобы проверить текущее состояние выполнения, используйте представление каталога sys.index_resumable_operations.

Ресурсы

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

• Дополнительное пространство, необходимое для сохранения сборки индекса, включая время приостановки сборки.
• Дополнительная пропускная способность для журналов на период сортировки. Общее потребление пространства для журналов у возобновляемого индекса меньше по сравнению с обычной операцией создания индекса в сети. Кроме того, эта операция поддерживает усечение журнала во время выполнения.
• Инструкции DDL, пытающиеся изменить таблицу, связанную с созданным индексом, пока операция индекса приостановлена, не допускаются.
• Очистка фантомных записей блокируется для встроенных индексов на весь период операции, в том числе пока она приостановлена.
• Если таблица содержит столбцы бизнес-объектов, повторной кластеризованной сборке индекса требуется блокировка изменения схемы (Sch-M) в начале операции.

Существующие функциональные ограничения

Операции создания повторного индекса имеют следующие ограничения:

• После приостановки повторной операции создания индекса в Сети начальное значение MAXDOP невозможно изменить.
• Параметр SORT_IN_TEMPDB = ON не поддерживается для операций возобновления индексов.
• Команда DDL с RESUMABLE = ON не может выполняться внутри явной транзакции.
• Не удается создать возобновление индекса, содержащего:
  • Вычисляемые или timestamp (rowversion) столбцы в качестве ключевых столбцов.
  • Бизнес-столбец в качестве включенного столбца.
• Для возобновления операций с индексами не поддерживаются:
  • Команда ALTER INDEX REBUILD ALL
  • Команда ALTER TABLE REBUILD
  • Индексы колоностора
  • Отфильтрованные индексы
  • Отключенные индексы

WAIT_AT_LOW_PRIORITY с операциями с индексами в режиме "в сети"

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

Если вы не используете WAIT_AT_LOW_PRIORITY этот параметр, все активные блокирующие транзакции с блокировками таблицы или индекса должны завершиться для запуска и завершения операции создания индекса. Когда операция индекса в сети начинается и до завершения, необходимо получить общую (S) или блокировку схемы (Sch-M) в таблице и провести ее в течение короткого времени. Несмотря на то, что блокировка хранится только в течение короткого времени, она может значительно повлиять на пропускную способность рабочей нагрузки, увеличить задержку запроса или вызвать время ожидания выполнения.

Чтобы избежать этих проблем, параметр WAIT_AT_LOW_PRIORITY позволяет управлять поведением S или Sch-M блокировок, необходимых для запуска и завершения операции индекса в Сети, выбрав три варианта. Во всех случаях, если во время ожидания, указанное MAX_DURATION = n [minutes], нет блокировки, которая включает операцию индекса, операция индекса выполняется немедленно.

WAIT_AT_LOW_PRIORITY выполняет ожидание операции индекса в сети с использованием блокировок с низким приоритетом, что позволяет другим операциям использовать обычные блокировки приоритета, которые будут продолжаться в то же время. Пропуск параметра WAIT_AT_LOW_PRIORITY эквивалентен WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE).

MAX_DURATION = время [MINUTES]

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

ABORT_AFTER_WAIT = [NONE | SELF | BLOCKERS ]

• NONE: продолжайте ожидание блокировки с обычным приоритетом.
• SELF. Выход из операции онлайн-индекса, выполняемой в данный момент, без каких-либо действий. Параметр SELF нельзя использовать, если MAX_DURATION равен 0.
• BLOCKERS: убивайте все пользовательские транзакции, которые блокируют операцию онлайн-индекса, чтобы операция продолжалось. Параметр BLOCKERS требует, чтобы субъект, выполняющий инструкцию CREATE INDEX или ALTER INDEX, должен иметь разрешение ALTER ANY CONNECTION.

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

• lock_request_priority_state
• process_killed_by_abort_blockers
• ddl_with_wait_at_low_priority

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

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

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

Последовательные ключи

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

Состязание операций вставки на последнюю страницу — это распространенная проблема, влияющая на производительность. Она возникает, когда множество параллельных запросов пытается вставить строки в индекс с последовательным ключом. Индекс считается последовательным, если ведущий ключевой столбец содержит значения, которые всегда увеличиваются (или уменьшаются), например столбец идентификаторов или даты, в котором по умолчанию используется текущее значение даты и времени. Так как вставляемые ключи являются последовательными, все новые строки вставляются в конце структуры индекса — другими словами, на той же странице. Это приводит к спору для страницы в памяти, которая может наблюдаться, как несколько потоков, ожидающих получения блокировки для страницы в вопросе. Соответствующий тип ожидания — PAGELATCH_EX.

Параметр индекса OPTIMIZE_FOR_SEQUENTIAL_KEY включает оптимизацию в ядре СУБД, что позволяет повысить пропускную способность для операций вставки с высокой степенью параллелизма в индекс. Этот параметр предназначен для индексов с последовательным ключом, из-за которого возникает состояние состязания при операциях вставки на последнюю страницу. Но его также можно применять для индексов с критическими участками в других областях структуры индекса в виде сбалансированного дерева.

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

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

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

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

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

Сжатие XML

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

Многие из соображений сжатия данных применяются к сжатием XML. Необходимо всегда принимать во внимание следующие соображения.

• Если указан список секций, для каждой отдельной секции можно установить сжатие XML. Если список секций не указан, сжатие XML устанавливается для всех секций. При создании индекса или таблицы сжатие XML является отключенным, если не было указано другое. При изменении таблицы сохраняется существующее сжатие, если не было указано иное.
• При указании списка секций или секции, выходящей за пределы диапазона, выдается ошибка.
• При создании кластеризованного индекса в куче кластеризованный индекс наследует состояние сжатия XML кучи, если не указан другой параметр сжатия.
• Чтобы изменить параметры сжатия XML кучи, необходимо перестроить все некластеризованные индексы в таблице. Это обеспечивает наличие в них указателей на новые расположения в куче.
• Включить или отключить сжатие XML можно в оперативном режиме или в режиме вне сети. Включение сжатия для кучи является однопоточным для операции в сети.
• Чтобы определить состояние сжатия XML секций в секционированных таблицах, используйте xml_compression столбец sys.partitions представления каталога.

Статистика индексов

При создании индекса rowstore ядро СУБД также создает статистику по ключевым столбцам индекса. Имя объекта статистики в представлении каталога sys.stats соответствует имени индекса. Для несекционированного индекса статистика создается с помощью полного сканирования данных. Для секционированного индекса статистические данные создаются с помощью алгоритма выборки по умолчанию.

При создании индекса columnstore ядро СУБД также создает объект статистики в sys.stats . Этот объект статистики не содержит статистических данных, таких как гистограмма и вектор плотности. Он используется при создании клона базы данных путем скрипта базы данных. В то время DBCC SHOW_STATISTICSUPDATE STATISTICS ... WITH STATS_STREAM и команды используются для получения метаданных columnstore, таких как сегмент, словарь и разностное хранилище, а также добавление его в статистику индекса columnstore. Эти метаданные получаются динамически во время компиляции запросов для обычной базы данных, но предоставляются объектом статистики для клона базы данных. Команда UPDATE STATISTICS не поддерживается для объекта статистики в индексе columnstore в любом другом сценарии.

Разрешения

ALTER Требуется разрешение на таблицу или представление или членство в фиксированной db_ddladmin роли базы данных.

ограничения

В Azure Synapse Analytics и Система платформы аналитики (PDW) нельзя создать:

• кластеризованный или некластеризованный индекс rowstore в таблице хранилища данных, если индекс columnstore уже существует. Это поведение отличается от SMP SQL Server, что позволяет использовать индексы rowstore и columnstore в одной таблице.
• Нельзя создать индекс для представления.

Метаданные

Чтобы просмотреть сведения о существующих индексах, можно выполнить запрос к представлению каталога sys.indexes.

Заметки о версии

• База данных SQL Azure не поддерживает файловые группы, отличные от PRIMARY.
• База данных SQL Azure и Управляемый экземпляр SQL Azure не поддерживают параметры FILESTREAM.
• Индексы columnstore недоступны до SQL Server 2012 (11.x).
• Возобновление операций с индексами доступно начиная с SQL Server 2017 (14.x), в базе данных SQL Azure и управляемом экземпляре SQL Azure.

Примеры: все версии. Используется база данных AdventureWorks

А. Создание простого некластеризованного индекса rowstore

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

CREATE INDEX IX_VendorID ON ProductVendor (VendorID);
CREATE INDEX IX_VendorID ON dbo.ProductVendor (VendorID DESC, Name ASC, Address DESC);
CREATE INDEX IX_VendorID ON Purchasing..ProductVendor (VendorID);

B. Создание простого некластеризованного составного индекса rowstore

В следующем примере создается некластеризованный составной индекс в столбцах SalesQuota и SalesYTD таблицы Sales.SalesPerson.

CREATE NONCLUSTERED INDEX IX_SalesPerson_SalesQuota_SalesYTD ON Sales.SalesPerson (SalesQuota, SalesYTD);

В. Создание индекса в таблице из другой базы данных

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

CREATE CLUSTERED INDEX IX_ProductVendor_VendorID ON Purchasing..ProductVendor (VendorID);

D. Добавление столбца в индекс

В следующем примере создается индекс IX_FF с двумя столбцами из таблицы dbo.FactFinance. Следующая инструкция перестраивает индекс с еще одним столбцом и сохраняет существующее имя.

CREATE INDEX IX_FF ON dbo.FactFinance (FinanceKey ASC, DateKey ASC);

-- Rebuild and add the OrganizationKey
CREATE INDEX IX_FF ON dbo.FactFinance (FinanceKey, DateKey, OrganizationKey DESC)
  WITH (DROP_EXISTING = ON);

Примеры: SQL Server, база данных SQL Azure

Е. Создание уникального некластеризованного индекса

В следующем примере создается уникальный некластеризованный индекс в столбце Name таблицы Production.UnitMeasure в базе данных AdventureWorks2022. Индекс требует уникальности данных, вставляемых в столбец Name.

CREATE UNIQUE INDEX AK_UnitMeasure_Name
  ON Production.UnitMeasure(Name);

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

-- Verify the existing value.
SELECT Name FROM Production.UnitMeasure WHERE Name = N'Ounces';
GO

INSERT INTO Production.UnitMeasure (UnitMeasureCode, Name, ModifiedDate)
  VALUES ('OC', 'Ounces', GETDATE());

В результате выдается сообщение об ошибке:

Server: Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'UnitMeasure' with unique index 'AK_UnitMeasure_Name'. The statement has been terminated.

F. Использование параметра IGNORE_DUP_KEY

В следующем примере демонстрируется влияние параметра IGNORE_DUP_KEY со значениями ON и OFF на операцию вставки нескольких строк во временную таблицу. В таблицу #Test вставляется одна строка, которая намеренно приведет к появлению повторяющихся значений при выполнении второй многострочной операции вставки INSERT. Счетчик строк таблицы возвращает количество вставленных строк.

CREATE TABLE #Test (C1 NVARCHAR(10), C2 NVARCHAR(50), C3 DATETIME);
GO

CREATE UNIQUE INDEX AK_Index ON #Test (C2)
  WITH (IGNORE_DUP_KEY = ON);
GO

INSERT INTO #Test VALUES (N'OC', N'Ounces', GETDATE());
INSERT INTO #Test SELECT * FROM Production.UnitMeasure;
GO

SELECT COUNT(*) AS [Number of rows] FROM #Test;
GO

DROP TABLE #Test;
GO

Ниже приведены результаты второй инструкции INSERT.

Server: Msg 3604, Level 16, State 1, Line 5 Duplicate key was ignored.

Number of rows
--------------
38

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

Те же инструкции выполняются вновь, но теперь с аргументом IGNORE_DUP_KEY, равным OFF.

CREATE TABLE #Test (C1 NVARCHAR(10), C2 NVARCHAR(50), C3 DATETIME);
GO

CREATE UNIQUE INDEX AK_Index ON #Test (C2)
  WITH (IGNORE_DUP_KEY = OFF);
GO

INSERT INTO #Test VALUES (N'OC', N'Ounces', GETDATE());
INSERT INTO #Test SELECT * FROM Production.UnitMeasure;
GO

SELECT COUNT(*) AS [Number of rows] FROM #Test;
GO

DROP TABLE #Test;
GO

Ниже приведены результаты второй инструкции INSERT.

Server: Msg 2601, Level 14, State 1, Line 5
Cannot insert duplicate key row in object '#Test' with unique index
'AK_Index'. The statement has been terminated.

Number of rows
--------------
1

Обратите внимание, что ни одна из строк таблицы Production.UnitMeasure не была вставлена, хотя ограничение индекса UNIQUE было нарушено только одной строкой.

G. Использование предложения DROP_EXISTING для удаления и повторного создания индекса

В следующем примере удаляется и создается повторно существующий индекс для столбца ProductID таблицы Production.WorkOrder в базе данных AdventureWorks2022 с использованием параметра DROP_EXISTING. Указываются также параметры FILLFACTOR и PAD_INDEX.

CREATE NONCLUSTERED INDEX IX_WorkOrder_ProductID
  ON Production.WorkOrder(ProductID)
    WITH (FILLFACTOR = 80,
      PAD_INDEX = ON,
      DROP_EXISTING = ON);
GO

H. Создание индекса для представления

В следующем примере создаются представление и индекс этого представления. Включено два запроса, использующих созданное индексированное представление.

-- Set the options to support indexed views
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
  QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO

-- Create view with schemabinding
IF OBJECT_ID ('Sales.vOrders', 'view') IS NOT NULL
  DROP VIEW Sales.vOrders;
GO

CREATE VIEW Sales.vOrders
  WITH SCHEMABINDING
AS
  SELECT SUM(UnitPrice * OrderQty * (1.00 - UnitPriceDiscount)) AS Revenue,
    OrderDate, ProductID, COUNT_BIG(*) AS COUNT
  FROM Sales.SalesOrderDetail AS od, Sales.SalesOrderHeader AS o
  WHERE od.SalesOrderID = o.SalesOrderID
  GROUP BY OrderDate, ProductID;
GO

-- Create an index on the view
CREATE UNIQUE CLUSTERED INDEX IDX_V1
  ON Sales.vOrders (OrderDate, ProductID);
GO

-- This query can use the indexed view even though the view is
-- not specified in the FROM clause.
SELECT SUM(UnitPrice * OrderQty * (1.00 - UnitPriceDiscount)) AS Rev,
  OrderDate, ProductID
FROM Sales.SalesOrderDetail AS od
  JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID = o.SalesOrderID
    AND ProductID BETWEEN 700 AND 800
    AND OrderDate >= CONVERT(DATETIME, '05/01/2002', 101)
GROUP BY OrderDate, ProductID
ORDER BY Rev DESC;
GO

-- This query can use the above indexed view
SELECT OrderDate, SUM(UnitPrice * OrderQty * (1.00 - UnitPriceDiscount)) AS Rev
FROM Sales.SalesOrderDetail AS od
  JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID = o.SalesOrderID
    AND DATEPART(mm, OrderDate) = 3
  AND DATEPART(yy, OrderDate) = 2002
GROUP BY OrderDate
ORDER BY OrderDate ASC;
GO

I. Создание индекса с включенными (неключевыми) столбцами

В следующем примере создается некластеризованный индекс с одним ключевым столбцом (PostalCode) и четырьмя неключевыми столбцами (AddressLine1, AddressLine2, City, StateProvinceID). Далее следует запрос, все данные для которого есть в индексе. Чтобы отобразить индекс, выбранный оптимизатором запросов, в меню "Запрос " в СРЕДЕ SQL Server Management Studio выберите "Отобразить фактический план выполнения" перед выполнением запроса.

CREATE NONCLUSTERED INDEX IX_Address_PostalCode
  ON Person.Address (PostalCode)
  INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);
GO

SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address
WHERE PostalCode BETWEEN N'98000' and N'99999';
GO

J. Создание секционированного индекса

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

CREATE NONCLUSTERED INDEX IX_TransactionHistory_ReferenceOrderID
  ON Production.TransactionHistory (ReferenceOrderID)
  ON TransactionsPS1 (TransactionDate);
GO

K. Создание отфильтрованного индекса

В следующем примере создается фильтрованный индекс для таблицы Production.BillOfMaterials в базе данных AdventureWorks2022. Предикат фильтра может включать столбцы, не являющиеся ключевыми в отфильтрованном индексе. Предикат в примере выбирает только те строки, где EndDate не равно NULL.

CREATE NONCLUSTERED INDEX "FIBillOfMaterialsWithEndDate"
  ON Production.BillOfMaterials (ComponentID, StartDate)
  WHERE EndDate IS NOT NULL;

L. Создание сжатого индекса

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

CREATE NONCLUSTERED INDEX IX_INDEX_1
  ON T1 (C2)
  WITH (DATA_COMPRESSION = ROW);
GO

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

CREATE CLUSTERED INDEX IX_PartTab2Col1
  ON PartitionTable1 (Col1)
  WITH (DATA_COMPRESSION = ROW);
GO

Следующий пример демонстрирует создание индекса для секционированной таблицы с помощью сжатия страниц для секции 1 индекса и сжатия строк для секций индекса со 2 по 4.

CREATE CLUSTERED INDEX IX_PartTab2Col1
  ON PartitionTable1 (Col1)
  WITH (
    DATA_COMPRESSION = PAGE ON PARTITIONS(1),
    DATA_COMPRESSION = ROW ON PARTITIONS (2 TO 4)
  );
GO

M. Создание индекса с сжатием XML

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

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

CREATE NONCLUSTERED INDEX IX_INDEX_1
  ON T1 (C2)
  WITH (XML_COMPRESSION = ON);
GO

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

CREATE CLUSTERED INDEX IX_PartTab2Col1
  ON PartitionTable1 (Col1)
  WITH (XML_COMPRESSION = ON);
GO

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

применяется к: SQL Server 2019 (15.x) и более поздним версиям, Базе данных SQL Azure и Управляемому экземпляру SQL Azure

-- Execute a resumable online index create statement with MAXDOP=1
CREATE INDEX test_idx1 ON test_table (col1) WITH (ONLINE = ON, MAXDOP = 1, RESUMABLE = ON);

-- Executing the same command again (see above) after an index operation was paused, resumes automatically the index create operation.

-- Execute a resumable online index creates operation with MAX_DURATION set to 240 minutes. After the time expires, the resumable index create operation is paused.
CREATE INDEX test_idx2 ON test_table (col2) WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 240);

-- Pause a running resumable online index creation
ALTER INDEX test_idx1 ON test_table PAUSE;
ALTER INDEX test_idx2 ON test_table PAUSE;

-- Resume a paused online index creation
ALTER INDEX test_idx1 ON test_table RESUME;
ALTER INDEX test_idx2 ON test_table RESUME;

-- Abort resumable index create operation which is running or paused
ALTER INDEX test_idx1 ON test_table ABORT;
ALTER INDEX test_idx2 ON test_table ABORT;

O. CREATE INDEX с другими параметрами блокировки с низким приоритетом

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

--Kill this session after waiting 5 minutes
CREATE CLUSTERED INDEX idx_1 ON dbo.T2 (a) WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = SELF)));
GO

--Kill blocker sessions
CREATE CLUSTERED INDEX idx_1 ON dbo.T2 (a) WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = BLOCKERS)));
GO

В следующем примере используется параметр RESUMABLE и указывается два значения MAX_DURATION. Первое применяется к параметру ABORT_AFTER_WAIT, второе — к параметру RESUMABLE.

--With resumable option; default locking behavior 
CREATE CLUSTERED INDEX idx_1 ON dbo.T2 (a) WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = NONE)), RESUMABLE = ON, MAX_DURATION = 240 MINUTES);

Примеры: Azure Synapse Analytics и система платформы аналитики (PDW)

P. Основной синтаксис

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

применяется к: SQL Server 2019 (15.x) и более поздним версиям, Базе данных SQL Azure и Управляемому экземпляру SQL Azure

-- Execute a resumable online index create statement with MAXDOP=1
CREATE INDEX test_idx ON test_table WITH (ONLINE = ON, MAXDOP = 1, RESUMABLE = ON);

-- Executing the same command again (see above) after an index operation was paused, resumes automatically the index create operation.

-- Execute a resumable online index creates operation with MAX_DURATION set to 240 minutes. After the time expires, the resumable index create operation is paused.
CREATE INDEX test_idx ON test_table WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 240);

-- Pause a running resumable online index creation
ALTER INDEX test_idx ON test_table PAUSE;

-- Resume a paused online index creation
ALTER INDEX test_idx ON test_table RESUME;

-- Abort resumable index create operation which is running or paused
ALTER INDEX test_idx ON test_table ABORT;

В. Создание некластеризованного индекса для таблицы в текущей базе данных

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

CREATE INDEX IX_ProductVendor_VendorID
  ON ProductVendor (VendorID);

R. Создание индекса для таблицы из другой базы данных

В приведенном ниже примере создается некластеризованный индекс по столбцу VendorID таблицы ProductVendor в базе данных Purchasing.

CREATE CLUSTERED INDEX IX_ProductVendor_VendorID
  ON Purchasing..ProductVendor (VendorID);

S. Создание кластеризованного индекса для таблицы

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

CREATE CLUSTERED COLUMNSTORE INDEX MyOrderedCCI ON MyDB.dbo.T1 
ORDER (c1, c2);

T. Преобразование кластеризованного индекса columnstore в упорядоченный кластеризованный индекс таблицы

В следующем примере существующий кластеризованный индекс columnstore преобразуется в упорядоченный кластеризованный индекс columnstore с именем MyOrderedCCI для столбцов c1 и c2 таблицы T2 в базе данных MyDB.

CREATE CLUSTERED COLUMNSTORE INDEX MyOrderedCCI ON MyDB.dbo.T2
ORDER (c1, c2)
WITH (DROP_EXISTING = ON);

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

• Руководство по архитектуре и разработке индексов SQL Server
• Выполнение операций с индексами в оперативном режиме
• Индексы и инструкция ALTER TABLE
• ALTER INDEX
• CREATE PARTITION FUNCTION
• CREATE PARTITION SCHEME
• CREATE SPATIAL INDEX
• CREATE STATISTICS
• CREATE TABLE
• CREATE XML INDEX
• Data Types
• DBCC SHOW_STATISTICS
• DROP INDEX
• XML-индексы (SQL Server)
• sys.indexes
• sys.index_columns
• sys.xml_indexes
• EVENTDATA