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 que se indizará.

• 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 se van a utilizar 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 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 Motor de base de datos el nivel hoja de cada página del índice cuando se crea o se vuelve a generar un índice. fillfactor debe ser un valor entero entre 1 y 100. El valor predeterminado es 0. Si fillfactor es 100 ó 0, Motor de base de datos crea índices con las páginas hoja llenas al máximo de su capacidad.

  Nota

  Los valores de fill factor 0 y 100 son idénticos.

  La configuración de FILLFACTOR solo se aplica cuando se crea o se vuelve a generar el índice. Motor de base de datos no mantiene dinámicamente el porcentaje especificado de espacio disponible de las páginas. Para ver la configuración de fill factor, 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 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 ordena 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 creación del índice.

  • OFF
    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 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 generarse 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, la definición se puede modificar. Por ejemplo, puede especificar columnas, criterio de ordenación, esquema de particionamiento 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 evita que todos los usuarios tengan acceso a la tabla subyacente durante 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 permiten bloqueos de fila. El valor predeterminado es ON.

  • ON
    Los bloqueos de fila se admiten al obtener acceso al índice. Motor de base de datos determina cuándo se utilizan los bloqueos de fila.

  • OFF
    Los bloqueos de fila no se utilizan.

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

  • ON
    Los bloqueos de página se permiten al obtener acceso al índice. 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 establecer un límite para el número de procesadores utilizados en la ejecución de un plan paralelo. El máximo es de 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 paralelo al número especificado o a un número inferior, en función de la actual carga de trabajo 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.

Comentarios

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