CREATE XML INDEX (Transact-SQL)

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

Создает XML-индекс по заданной таблице. Индекс может быть создан до появления данных в таблице. XML-индексы можно создавать на основе таблиц другой базы данных — для этого нужно указать полное имя базы данных.

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

Синтаксис

--Create XML Index   
CREATE [ PRIMARY ] XML INDEX index_name
    ON <object> ( xml_column_name )
    [ USING XML INDEX xml_index_name
        [ FOR { VALUE | PATH | PROPERTY } ] ]
    [ WITH ( <xml_index_option> [ ,...n ] ) ]
[ ; ]

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

<xml_index_option> ::=
{
    PAD_INDEX  = { ON | OFF }
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB = { ON | OFF }
  | IGNORE_DUP_KEY = OFF
  | DROP_EXISTING = { ON | OFF }
  | ONLINE = OFF
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | XML_COMPRESSION = { ON | OFF }
}

Аргументы

[PRIMARY] XML

Создает XML-индекс по заданному столбцу XML. Если присутствует ключевое слово PRIMARY, создается кластеризованный индекс с ключом, образованным из ключа кластеризации таблицы пользователя и идентификатора XML-узла. Для каждой таблицы можно создать до 249 XML-индексов. При создании XML-индекса помните следующее.

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

• Максимальное количество столбцов в ключе кластеризации таблицы пользователя — 15.

• У каждого столбца XML в таблице может быть один первичный XML-индекс и несколько вторичных.

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

• Индекс XML можно создать только для одного столбца XML. Невозможно создать XML-индекс для столбца, не относящегося к типу XML, а также реляционный индекс для столбца XML.

• Невозможно создать первичный или вторичный XML-индекс для столбца XML в представлении для переменной со столбцами типа XML, возвращающей табличное значение, или для переменных типа XML.

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

• Значения параметров SET должны быть теми же, что и для индексированных представлений и индексов вычисляемых столбцов. В частности, при вставке, удалении или обновлении значений в столбце XML необходимо установить значение ON для параметра ARITHABORT.

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

index_name

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

Имена первичных XML-индексов не должны начинаться со следующих символов: #, ##, @ и @@.

xml_column_name

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

USING XML INDEX имя_индекса_XML

Указывает первичный XML-индекс, который должен использоваться при создании вторичного XML-индекса.

FOR { VALUE | PATH | PROPERTY }

Указывает тип вторичного XML-индекса.

ЗНАЧЕНИЕ
Создает вторичный XML-индекс для столбцов, где ключевые столбцы (значение узла и путь) входят в первичный XML-индекс.

ПУТЬ
Создает вторичный XML-индекс для столбцов, построенных на основе значений путей и узлов в первичном XML-индексе. Во вторичном индексе типа PATH значениями путей и узлов являются ключевые столбцы, обеспечивающие эффективный поиск путей.

СВОЙСТВО
Создает вторичный XML-индекс по столбцам первичного XML-индекса (PK, путь и узел), где PK — первичный ключ базовой таблицы.

<object>::=

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

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

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

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

<> xml_index_option ::=

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

PAD_INDEX = { ON | OFF }

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

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

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

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

FILLFACTOR = fillfactor

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

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

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

SORT_IN_TEMPDB = { ON | OFF }

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

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

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

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

IGNORE_DUP_KEY = OFF

Не влияет на XML-индексы, поскольку этот тип индекса никогда не уникален. Не устанавливайте этот параметр в значение ON, иначе произойдет ошибка.

DROP_EXISTING = { ON | OFF }

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

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

ВЫКЛ.
Выдается ошибка, если индекс с указанным именем уже существует.

Тип индекса не может быть изменен с помощью аргумента DROP_EXISTING. Кроме того, первичный XML-индекс не может быть переопределен как вторичный XML-индекс и наоборот.

ONLINE = OFF

Указывает, что базовые таблицы и связанные индексы будут недоступны для запросов и изменения данных во время операций с индексами. В этой версии SQL Server не разрешено применять построение индекса в сети для XML-индексов. Если этому параметру присвоено значение ON для XML-индекса, то возникает ошибка. Не указывайте параметр ONLINE или установите его в значение OFF.

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

ALLOW_ROW_LOCKS = { ON | OFF }

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

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

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

ALLOW_PAGE_LOCKS = { ON | OFF }

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

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

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

MAXDOP = max_degree_of_parallelism

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

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

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

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

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

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

Замечания

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

Для просмотра сведений об XML-индексах можно воспользоваться представлением каталога sys.xml_indexes.

Дополнительные сведения об XML-индексах см. в разделе XML-индексы (SQL Server).

Сжатие XML

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

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

Дополнительные сведения о создании индексов

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

Примеры

Примеры кода Transact-SQL в этой статье используют AdventureWorks2022 базу данных или AdventureWorksDW2022 пример базы данных, которую можно скачать с домашней страницы примеров и проектов сообщества Microsoft SQL Server.

А. Создание первичного XML-индекса

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

IF EXISTS (SELECT * FROM sys.indexes
            WHERE name = N'PXML_ProductModel_CatalogDescription')
    DROP INDEX PXML_ProductModel_CatalogDescription
        ON Production.ProductModel;  
GO  
CREATE PRIMARY XML INDEX PXML_ProductModel_CatalogDescription
    ON Production.ProductModel (CatalogDescription);  
GO

B. Создание первичного XML-индекса со сжатием XML

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

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

IF EXISTS (SELECT * FROM sys.indexes
            WHERE name = N'PXML_ProductModel_CatalogDescription')
    DROP INDEX PXML_ProductModel_CatalogDescription
        ON Production.ProductModel;  
GO  
CREATE PRIMARY XML INDEX PXML_ProductModel_CatalogDescription
    ON Production.ProductModel (CatalogDescription)
    WITH (XML_COMPRESSION = ON);  
GO

C. Создание вторичного XML-индекса

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

IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IXML_ProductModel_CatalogDescription_Path')
    DROP INDEX IXML_ProductModel_CatalogDescription_Path
        ON Production.ProductModel;  
GO  
CREATE XML INDEX IXML_ProductModel_CatalogDescription_Path
    ON Production.ProductModel (CatalogDescription)
    USING XML INDEX PXML_ProductModel_CatalogDescription FOR PATH ;
GO

D. Создание вторичного XML-индекса со сжатием XML

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

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

IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IXML_ProductModel_CatalogDescription_Path')
    DROP INDEX IXML_ProductModel_CatalogDescription_Path
        ON Production.ProductModel;  
GO  
CREATE XML INDEX IXML_ProductModel_CatalogDescription_Path
    ON Production.ProductModel (CatalogDescription)
    USING XML INDEX PXML_ProductModel_CatalogDescription FOR PATH
    WITH (XML_COMPRESSION = ON);
GO

См. также

• ALTER INDEX (Transact-SQL)
• Инструкция CREATE INDEX (Transact-SQL)
• CREATE PARTITION FUNCTION (Transact-SQL)
• CREATE PARTITION SCHEME (Transact-SQL)
• CREATE SPATIAL INDEX (Transact-SQL)
• CREATE STATISTICS (Transact-SQL)
• CREATE TABLE (Transact-SQL)
• Типы данных (Transact-SQL)
• DBCC SHOW_STATISTICS (Transact-SQL)
• DROP INDEX (Transact-SQL)
• XML-индексы (SQL Server)
• sys.indexes (Transact-SQL)
• sys.index_columns (Transact-SQL)
• sys.xml_indexes (Transact-SQL)
• EVENTDATA (Transact-SQL)
• XML-индексы (SQL Server)