CREATE XML INDEX (Transact-SQL)

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.

[!NOTA]

Para crear un índice relacional, vea CREATE INDEX (Transact-SQL). Para obtener más información acerca de cómo crear un índice espacial, vea CREATE SPATIAL INDEX (Transact-SQL).

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 ] . | schema_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
}

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.

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

  • No 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 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, vea Opciones SET que afectan a los resultados.

  Para obtener más información, vea Índices en columnas del tipo de datos XML.

• index_name
  Es 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
  Es la columna xml en la que se basa el índice. Solamente puede especificarse una columna xml en una única definición de índice XML; sin embargo, pueden crearse 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.

  • VALUE
    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>::=

Es el objeto completo o no completo que va a indizarse.

• database_name
  Es el nombre de la base de datos.

• schema_name
  Es el nombre del esquema al que pertenece la tabla.

• table_name
  Es el nombre de la tabla que va a indizarse.

<xml_index_option> ::=

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

• PAD_INDEX = { ON | OFF }
  Especifica el relleno de índice. El valor predeterminado es OFF.

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

  • No se especifica OFF ni fillfactor.
    Las páginas de nivel intermedio se rellenan 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, Database Engine (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 nunca es inferior a dos, independientemente de lo bajo que sea el valor de fillfactor.  

• FILLFACTOR **=**fillfactor
  Especifica un porcentaje que indica cuánto debe rellenar Database Engine (Motor de base de datos) el nivel hoja de cada página de índice cuando se crea o se vuelve a generar un índice. fillfactor debe ser un valor entero comprendido entre 1 y 100. El valor predeterminado es 0. Si fillfactor es 100 ó 0, Database Engine (Motor de base de datos) crea índices con las páginas hoja rellenas al máximo de su capacidad.

  [!NOTA]

  Los valores de factor de relleno (fillfactor) 0 y 100 son idénticos.

  La configuración de FILLFACTOR solamente se aplica cuando se crea o se vuelve a generar el índice. Database Engine (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 (fillfactor), utilice la vista de catálogo sys.indexes.

  Importante
  La creación de un índice clúster con un valor de FILLFACTOR menor que 100 afecta a la cantidad de espacio de almacenamiento que ocupan los datos, porque Database Engine (Motor de base de datos) vuelve a distribuir los datos cuando crea el índice clúster.

  Para obtener más información, vea Factor de relleno.

• SORT_IN_TEMPDB = { ON | OFF }
  Indica si deben almacenarse resultados temporales de orden en tempdb. El valor predeterminado es OFF.

  • ON
    Los resultados de orden intermedio que se utilizan 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 creación del índice.

  • OFF
    Los resultados de ordenación 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 obtener más información, vea tempdb y la creación de í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 generar el índice XML con nombre preexistente. El valor predeterminado es OFF.

  • ON
    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, es posible modificar la definición de índice. Por ejemplo, puede especificar columnas, criterios de ordenación, esquemas de partición u opciones de índice diferentes.

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

  El tipo de índice no puede cambiarse utilizando 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 para crear, volver a crear o quitar un índice XML adquiere un bloqueo de modificación del esquema (Sch-M) de la tabla. Esto impide el acceso de cualquier usuario a la tabla subyacente durante toda la operación.

  [!NOTA]

  Las operaciones de índices en línea únicamente están disponibles en las ediciones Enterprise, Developer y Evaluation de SQL Server.

• ALLOW_ROW_LOCKS = { ON | OFF }
  Especifica si se admiten los bloqueos de fila. El valor predeterminado es ON.

  • ON
    Se admiten bloqueos de fila al obtener acceso al índice. Database Engine (Motor de base de datos) determina cuándo se utilizan los bloqueos de fila.

  • OFF
    No se utilizan bloqueos de fila.

• ALLOW_PAGE_LOCKS = { ON | OFF }
  Especifica si se admiten los bloqueos de página. El valor predeterminado es ON.

  • ON
    Se admiten bloqueos de página al obtener acceso al índice. Database Engine (Motor de base de datos) determina el momento en que se utilizan bloqueos de página.

  • OFF
    No se utilizan bloqueos de página.

• MAXDOP **=**max_degree_of_parallelism
  Invalida la opción de configuración max degree of parallelism (grado máximo de paralelismo) durante la operación de índice. Utilice MAXDOP para limitar el número de procesadores que se utilizan en la ejecución de planes paralelos. El valor máximo es de 64 procesadores.

  Importante
  Aunque la opción MAXDOP se admite sintácticamente para todos los índices XML, para un índice XML primario, CREATE XML INDEX utiliza un solo procesador.

  max_degree_of_parallelism puede ser:

  • 1
    Suprime la generación de planes paralelos.

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

  • 0 (predeterminado)
    Utiliza el número real de procesadores, o un número inferior, en función de la carga de trabajo actual del sistema.

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

  [!NOTA]

  Las operaciones de índices en paralelo únicamente están disponibles en las ediciones Enterprise, Developer y Evaluation de SQL Server.

Notas

Las columnas calculadas derivadas de los tipos de datos xml pueden indizarse 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 puede crear un índice XML principal en una columna xml calculada.

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

Para obtener más información acerca de los índices XML, vea Índices en columnas del tipo de datos XML.

Notas adicionales sobre la creación de índices

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

Ejemplos

A. Crear un índice XML principal

El ejemplo siguiente crea un índice XML principal en la columna CatalogDescription de la tabla Production.ProductModel.

USE AdventureWorks;
GO
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. Crear un índice XML secundario

El ejemplo siguiente crea un índice XML secundario en la columna CatalogDescription de la tabla Production.ProductModel.

USE AdventureWorks;
GO
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

Vea también

Referencia

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)

sys.indexes (Transact-SQL)

sys.index_columns (Transact-SQL)

sys.xml_indexes (Transact-SQL)

EVENTDATA (Transact-SQL)

Conceptos

Directrices generales para diseñar índices

Índices en columnas del tipo de datos XML

Índices en columnas del tipo de datos XML

Otros recursos

Determinar requisitos de espacio en disco del índice

Arquitectura de estructuras de tablas y datos de índices