CREATE XML INDEX (Transact-SQL)

Se aplica a:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Crea un índice XML en una tabla especificada. Se puede crear un índice antes de que la tabla posea datos. Es posible crear índices XML sobre tablas de otra base de datos si se especifica un nombre de base de datos completo.

Convenciones de sintaxis de Transact-SQL

Sintaxis

--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 }
}

Argumentos

[PRIMARY] XML

Crea un índice XML en la columna xml especificada. Cuando se especifica PRIMARY, se crea un índice clúster con la clave clúster formada a partir de la clave de agrupación en clústeres de la tabla de usuario y un identificador del nodo XML. Cada tabla puede tener hasta 249 índices XML. Observe lo siguiente cuando cree un índice XML:

• Debe existir un índice clúster en la clave principal de la tabla de usuario.

• La clave de agrupación en clústeres de la tabla de usuario tiene un límite de 15 columnas.

• Cada columna xml de una tabla puede tener un índice XML principal y varios índices XML secundarios.

• Debe existir un índice XML principal en una columna xml para poder crear un índice XML secundario en la columna.

• Solo puede crearse un índice xml en una única columna de xml. No se puede crear un índice XML en una columna que no sea xml, así como tampoco se puede crear un índice relacional en una columna xml.

• No se puede crear un índice XML, ya sea principal o secundario, en una columna xml en una vista, en una variable con valores de tabla con columnas xml o en variables de tipo xml.

• No se puede crear un índice XML principal en una columna xml calculada.

• La configuración de la opción SET debe ser la misma que la requerida para vistas indizadas e índices de columnas calculadas. Concretamente, la opción ARITHABORT debe establecerse en ON cuando se crea un índice XML y cuando se insertan, eliminan o actualizan valores en la columna xml.

Para obtener más información, consulte Índices XML (SQL Server).

index_name

El nombre del índice. Los nombres de índice deben ser únicos en una tabla, pero no es necesario que sean únicos en una base de datos. Los nombres de índice deben seguir las reglas de los identificadores.

Los nombres de índices XML principales no pueden comenzar por los siguientes caracteres: #, ##, @ o @@.

xml_column_name

La columna xml en la que se basa el índice. Solamente puede especificarse una columna xml en una única definición de índice XML, pero se pueden crear varios índices XML secundarios en una columna xml.

USING XML INDEX xml_index_name

Especifica el índice XML principal que se usará para la creación de un índice XML secundario.

FOR { VALUE | PATH | PROPERTY }

Especifica el tipo de índice XML secundario.

VALOR
Crea un índice XML secundario en las columnas en las que se encuentran las columnas de clave (ruta y valor del nodo) del índice XML principal.

PATH
Crea un índice XML secundario en las columnas generadas a partir de valores de ruta y de nodo del índice XML principal. En el índice secundario PATH, los valores de ruta y de nodo son las columnas de clave que permiten exploraciones eficaces en la búsqueda de rutas.

PROPERTY
Crea un índice XML secundario en las columnas (PK, y valor de ruta y de nodo) del índice XML principal, donde PK es la clave principal de la tabla base.

<object>::=

El objeto completo o no completo que se indexará.

database_name
El nombre de la base de datos.

schema_name
El nombre del esquema al que pertenece la tabla.

table_name
El nombre de la tabla que se va a indexar.

<xml_index_option> ::=

Especifica las opciones que se van a utilizar en la creación del índice.

PAD_INDEX = { ON | OFF }

Especifica el relleno del índice. El valor predeterminado es OFF.

ACTIVAR
El porcentaje de espacio disponible especificado por fillfactor se aplica a páginas de nivel intermedio del índice.

No se especifica OFF ni fillfactor
Las páginas de nivel intermedio se llenan casi al máximo de su capacidad y dejan espacio suficiente para al menos una fila del tamaño máximo que puede tener el índice, considerando el conjunto de claves incluidas en las páginas de nivel intermedio.

La opción PAD_INDEX solamente resulta útil si también se especifica FILLFACTOR, porque PAD_INDEX utiliza el mismo porcentaje especificado por FILLFACTOR. Si el porcentaje especificado para FILLFACTOR no es lo suficientemente grande como para admitir una fila, Motor de base de datos invalida internamente el porcentaje para permitir el valor mínimo. El número de filas de una página de nivel intermedio del índice no es nunca inferior a dos, independientemente de lo bajo que sea el valor de fillfactor.

FILLFACTOR = fillfactor

Especifica un porcentaje que indica cuánto debe llenar el Motor de base de datos el nivel hoja de cada página de índice durante la creación o nueva generación de los índices. fillfactor debe ser un valor entero comprendido entre 1 y 100. El valor predeterminado es 0. Si fillfactor es 100 o 0, el Motor de base de datos crea índices con las páginas hoja llenas al máximo de su capacidad.

La configuración de FILLFACTOR solo se aplica cuando se crea o se vuelve a generar el índice. El motor de base de datos no mantiene dinámicamente el porcentaje especificado de espacio disponible de las páginas. Para ver la configuración del factor de relleno, use la vista de catálogo sys.indexes.

Para obtener más información, vea Especificar el factor de relleno para un índice.

SORT_IN_TEMPDB = { ON | OFF }

Indica si deben almacenarse resultados temporales de orden en tempdb. El valor predeterminado es OFF.

ACTIVAR
Los resultados de ordenación intermedios utilizados para generar el índice se almacenan en tempdb. Esto puede reducir el tiempo necesario para crear un índice si tempdb y la base de datos de usuarios están en conjuntos de discos distintos. Sin embargo, esto aumenta la cantidad de espacio en disco utilizado durante la generación del índice.

Apagado
Los resultados de orden intermedios se almacenan en la misma base de datos que el índice.

Además del espacio necesario en la base de datos del usuario para crear el índice, tempdb debe tener la misma cantidad de espacio adicional para almacenar los resultados de orden intermedio. Para más información, vea Opción SORT_IN_TEMPDB para índices.

IGNORE_DUP_KEY = OFF

No tiene ningún efecto sobre los índices XML porque el tipo de índice nunca es único. No establezca esta opción en ON porque, de lo contrario, se producirá un error.

DROP_EXISTING = { ON | OFF }

Especifica que se quite y vuelva a generarse el índice XML con nombre preexistente. El valor predeterminado es OFF.

ACTIVAR
El índice existente se quita y se vuelve a generar. El nombre de índice especificado debe ser el mismo que el de un índice actualmente existente; sin embargo, la definición se puede modificar. Por ejemplo, puede especificar columnas, criterio de ordenación, esquema de particionamiento u opciones de índice diferentes.

Apagado
Se muestra un error si ya existe el nombre de índice especificado.

El tipo de índice no puede cambiarse mediante DROP_EXISTING. Asimismo, un índice XML principal no puede volver a definirse como un índice XML secundario, o viceversa.

ONLINE = OFF

Especifica que las tablas subyacentes y los índices asociados no están disponibles para la realización de consultas y modificaciones de datos durante la operación del índice. En esta versión de SQL Server, no se admiten generaciones de índices en línea para los índices XML. Si esta opción se establece en ON para un índice XML, se produce un error. Omita la opción ONLINE o establezca ONLINE en OFF.

Una operación de índice sin conexión que crea, recompila o quita un índice XML adquiere un bloqueo de modificación del esquema (Sch-M) de la tabla. Esto evita que todos los usuarios tengan acceso a la tabla subyacente durante la operación.

ALLOW_ROW_LOCKS = { ON | OFF }

Especifica si se permiten los bloqueos de fila. El valor predeterminado es ON.

ACTIVAR
Los bloqueos de fila se admiten al obtener acceso al índice. El Motor de base de datos determina cuándo se usan los bloqueos de fila.

Apagado
No se usan bloqueos de fila.

ALLOW_PAGE_LOCKS = { ON | OFF }

Especifica si se permiten bloqueos de página. El valor predeterminado es ON.

ACTIVAR
Los bloqueos de página se permiten al obtener acceso al índice. Motor de base de datos determina el momento en que se usan los bloqueos de página.

Apagado
No se usan bloqueos de página.

MAXDOP = max_degree_of_parallelism

Reemplaza la opción Establecer la opción de configuración del servidor Grado máximo de paralelismo durante la operación de índice. Utilice MAXDOP para establecer un límite para el número de procesadores utilizados en la ejecución de un plan paralelo. El máximo es 64 procesadores.

max_degree_of_parallelism puede tener estos valores:

1
Suprime la generación de planes paralelos.

>1
Restringe el número máximo de procesadores utilizados en una operación de índice paralelo al número especificado o a un número inferior, en función de la actual carga de trabajo del sistema.

0 (predeterminado)
Usa el número real de procesadores o menos, según la carga de trabajo actual del sistema.

Para obtener más información, vea Configurar operaciones de índice en paralelo.

Comentarios

Las columnas calculadas derivadas de los tipos de datos xml se puede indizar como una columna de clave o como una columna sin clave incluida, siempre que el tipo de datos de la columna calculada esté disponible como columna de clave de índice o columna sin clave. No se puede crear un índice XML principal en una columna xml calculada.

Para ver información sobre los índices XML, use la vista de catálogo sys.xml_indexes.

Para más información sobre los índices XML, vea Índices XML (SQL Server).

Compresión XML

Se aplica a SQL Server 2022 (16.x) y versiones posteriores, Azure SQL Database y Azure SQL Managed Instance.

• Los índices XML no heredan la propiedad de compresión de la tabla. Para comprimir índices, debe habilitar explícitamente la compresión XML en índices XML.
• Los índices XML secundarios no heredan la propiedad de compresión del índice XML principal.
• De forma predeterminada, el valor de compresión XML de índices XML se establece en OFF cuando se crea el índice.

Notas adicionales sobre la creación de índices

Para más información sobre la creación de índices, vea la sección "Comentarios" de CREATE INDEX (Transact-SQL).

Ejemplos

Este artículo requiere la base de datos de ejemplo AdventureWorks2022 que se puede descargar de la página principal de ejemplos y proyectos de la comunidad de Microsoft SQL Server.

A Crear un índice XML principal

El ejemplo siguiente crea un índice XML principal en la columna CatalogDescription de la tabla 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. Creación de un índice XML principal con compresión XML

Se aplica a SQL Server 2022 (16.x) y versiones posteriores, Azure SQL Database y Azure SQL Managed Instance.

El ejemplo siguiente crea un índice XML principal en la columna CatalogDescription de la tabla 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. Crear un índice XML secundario

El ejemplo siguiente crea un índice XML secundario en la columna CatalogDescription de la tabla 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. Creación de un índice XML secundario con compresión XML

Se aplica a SQL Server 2022 (16.x) y versiones posteriores, Azure SQL Database y Azure SQL Managed Instance.

El ejemplo siguiente crea un índice XML secundario en la columna CatalogDescription de la tabla 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

Consulte también

• 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)
• Tipos de datos (Transact-SQL)
• DBCC SHOW_STATISTICS (Transact-SQL)
• DROP INDEX (Transact-SQL)
• Índices XML (SQL Server)
• sys.indexes (Transact-SQL)
• sys.index_columns (Transact-SQL)
• sys.xml_indexes (Transact-SQL)
• EVENTDATA (Transact-SQL)
• Índices XML (SQL Server)