Crear índices XML

Se aplica a: SQL Server Azure SQL Database Azure SQL Managed Instance

En este artículo se describe cómo crear índices XML principales y secundarios.

Crear un índice XML principal

Para crear un índice XML principal, use la instrucción DDL Transact-SQL CREATE INDEX (Transact-SQL). Los índices XML no admiten todas las opciones que están disponibles para índices que no son XML.

Tenga en cuenta lo siguiente cuando cree un índice XML:

• Para crear un índice XML principal, la tabla que contiene la columna XML que se va a indizar, llamada tabla base, debe tener un índice clúster en la clave principal. Este índice agrupado asegura que si la tabla básica está particionada, el índice XML primario se pueda particionar usando el mismo esquema de partición y la misma función de partición.

• Si ya existe un índice XML, la clave principal agrupada de la tabla no puede modificarse. Antes de modificar la clave principal, deberá quitar todos los índices XML de la tabla.

• Un índice XML principal puede crearse en una sola columna de tipo xml . No es posible crear ningún otro índice con una columna de tipo xml como columna de clave. Pero puede incluir la columna de tipo xml en un índice que no sea XML. Cada columna de tipo xml de una tabla puede tener su propio índice XML principal. No obstante, solo se admite un índice XML principal por cada columna de tipo xml .

• Los índices XML existen en el mismo espacio de nombres que los índices que no son XML. Por tanto, no puede tener un índice XML y otro que no lo sea en la misma tabla y con el mismo nombre.

• Las opciones IGNORE_DUP_KEY y ONLINE siempre se establecen en OFF para los índices XML. Puede especificar estas opciones con el valor OFF.

• La información sobre el grupo de archivos o la partición de la tabla de usuario se aplica al índice XML y no puede especificarse por separado.

• La opción de índice DROP_EXISTING permite quitar un índice XML principal y crear uno nuevo o efectuar la misma operación con un índice XML secundario. No obstante, esta opción no puede quitar un índice XML secundario para crear un índice XML principal ni viceversa.

• Los nombres de índice XML principal tienen las mismas restricciones que los nombres de vista.

  No se puede crear un índice XML sobre una columna de tipo xml en una vista, en una variable valorada en tabla con columnas de tipo xml, o variables de tipo xml.

• Para cambiar una columna de tipo xml de XML con tipo a XML sin tipo, o viceversa, con la opción ALTER TABLE ALTER COLUMN, la columna no debe incluir ningún índice XML. Si existe alguno, debe quitarse antes de intentar cambiar el tipo de columna.

• Al crear un índice XML, la opción ARITHABORT debe configurarse en ON. Para consultar, insertar, eliminar o actualizar valores en la columna xml usando métodos de tipo de datos XML, debe establecerse la misma opción en la conexión. De lo contrario, los métodos de tipo de datos xml darán error.

  Nota:

  Las vistas de catálogo incluyen información acerca de los índices XML. Pero no se admite sp_helpindex . Los ejemplos que se ofrecen más adelante en este tema muestran cómo consultar las vistas de catálogo para encontrar información de índices XML.

Al crear o recrear un índice XML primario en una columna de tipo de datos xml que contenga valores de los tipos esquema XML xs:date o xs:dateTime (o cualquier subtipo de estos tipos) que tengan un año inferior a 1, la creación del índice fallará en SQL Server 2008 (10.0.x) y versiones posteriores. SQL Server 2005 (9.x) permitía estos valores, por lo que este problema puede producirse al crear índices en una base de datos generada en SQL Server 2005 (9.x). Para obtener más información, vea Comparar XML con tipo y XML sin tipo.

Ejemplo: crear un índice XML primario

En la mayoría de los ejemplos se utiliza una tabla T (pk INT PRIMARY KEY, xCol XML) con una columna XML sin tipo. Este ejemplo puede ampliarse a XML con tipo de forma directa. Para simplificar, las consultas se describen para instancias de datos XML como se muestra en el siguiente ejemplo:

<book genre="security" publicationdate="2002" ISBN="0-7356-1588-2">
   <title>Writing Secure Code</title>
   <author>
      <first-name>Michael</first-name>
      <last-name>Howard</last-name>
   </author>
   <author>
      <first-name>David</first-name>
      <last-name>LeBlanc</last-name>
   </author>
   <price>39.99</price>
</book>

La siguiente instrucción crea un índice XML, denominado idx_xCol , en la columna XML xCol de la tabla T:

CREATE PRIMARY XML INDEX idx_xCol on T (xCol)

Crear un índice XML secundario

Use la instrucción DDL Transact-SQL CREATE INDEX (Transact-SQL)para crear índices XML secundarios y especifique el tipo de índice XML secundario que desea.

Cuando cree índices XML secundarios, tenga en cuenta lo siguiente:

• Todas las opciones de indización que se aplican a un índice no clúster, salvo IGNORE_DUP_KEY y ONLINE, se admiten para los índices XML secundarios. Las dos opciones deben establecerse siempre en OFF para los índices XML secundarios.

• Los índices secundarios se dividen en particiones al igual que el índice XML principal.

• DROP_EXISTING permite quitar un índice secundario de la tabla de usuario y crear otro en la misma tabla.

Puede consultar la vista de catálogo sys.xml_indexes para recuperar información de índices XML. La columna secondary_type_desc de la vista de catálogo sys.xml_indexes proporciona el tipo de índice secundario:

SELECT  *
FROM    sys.xml_indexes;

Los valores que se devuelven en la columna secondary_type_desc pueden ser NULL, PATH, VALUE o PROPERTY. El valor devuelto para el índice XML principal es NULL.

Ejemplo: crear índices XML secundarios

El ejemplo siguiente muestra cómo crear índices XML secundarios. En el ejemplo también se muestra información acerca de los índices XML que ha creado.

CREATE TABLE T (Col1 INT PRIMARY KEY, XmlCol XML);
GO
-- Create primary index.
CREATE PRIMARY XML INDEX PIdx_T_XmlCol
ON T(XmlCol);
GO
-- Create secondary indexes (PATH, VALUE, PROPERTY).
CREATE XML INDEX PIdx_T_XmlCol_PATH ON T(XmlCol)
USING XML INDEX PIdx_T_XmlCol
FOR PATH;
GO
CREATE XML INDEX PIdx_T_XmlCol_VALUE ON T(XmlCol)
USING XML INDEX PIdx_T_XmlCol
FOR VALUE;
GO
CREATE XML INDEX PIdx_T_XmlCol_PROPERTY ON T(XmlCol)
USING XML INDEX PIdx_T_XmlCol
FOR PROPERTY;
GO

Puede consultar la vista de catálogo sys.xml_indexes para recuperar información sobre índices XML. La columna secondary_type_desc proporciona el tipo de índice secundario.

SELECT  *
FROM    sys.xml_indexes;

También puede consultar la vista de catálogo para obtener información de índice.

SELECT *
FROM sys.xml_indexes
WHERE object_id = object_id('T');

Puede agregar datos de ejemplo y, a continuación, revisar la información de índices XML.

INSERT INTO T VALUES (1,
'<doc id="123">
<sections>
<section num="2">
<heading>Background</heading>
</section>
<section num="3">
<heading>Sort</heading>
</section>
<section num="4">
<heading>Search</heading>
</section>
</sections>
</doc>');
GO
-- Check XML index information.
SELECT *
FROM   sys.dm_db_index_physical_stats (db_id(), object_id('T'), NULL, NULL, 'DETAILED');
GO
-- Space usage of primary XML index
DECLARE @index_id int;
SELECT  @index_id = i.index_id
FROM    sys.xml_indexes i
WHERE   i.name = 'PIdx_T_XmlCol' and object_name(i.object_id) = 'T';

SELECT *
FROM sys.dm_db_index_physical_stats (db_id(), object_id('T') , @index_id, DEFAULT, 'DETAILED');
GO
--- Space usage of secondary XML index (for example PATH secondary index)  PIdx_T_XmlCol_PATH
DECLARE @index_id int;
SELECT  @index_id = i.index_id
FROM    sys.xml_indexes i
WHERE  i.name = 'PIdx_T_XmlCol_PATH' and object_name(i.object_id) = 'T';

SELECT *
FROM sys.dm_db_index_physical_stats (db_id(), object_id('T') , @index_id, DEFAULT, 'DETAILED');
GO

-- Space usage of all secondary XML indexes for a particular table
SELECT i.name, object_name(i.object_id), stats.*
FROM   sys.dm_db_index_physical_stats (db_id(), object_id('T'), NULL, DEFAULT, 'DETAILED') stats
JOIN sys.xml_indexes i ON (stats.object_id = i.object_id and stats.index_id = i.index_id)
WHERE secondary_type is not null;
-- Drop secondary indexes.
DROP INDEX PIdx_T_XmlCol_PATH ON T;
GO
DROP INDEX PIdx_T_XmlCol_VALUE ON T;
GO
DROP INDEX PIdx_T_XmlCol_PROPERTY ON T;
GO
-- Drop primary index.
DROP INDEX PIdx_T_XmlCol ON T;
-- Drop table T.
DROP TABLE T;
GO

Consulte también

• Índices XML (SQL Server)
• Datos XML (SQL Server)