Índices XML (SQL Server)

Se aplica a:SQL ServerAzure SQL DatabaseInstancia administrada de Azure SQLBase de datos SQL en Microsoft Fabric

Es posible crear índices XML en columnas del tipo de datos xml . Indexan todas las etiquetas, los valores y las rutas de las instancias XML de la columna, y mejoran el rendimiento de las consultas. Un índice XML puede afectar positivamente a una aplicación en estas situaciones:

• Las consultas en columnas XML son habituales en su carga de trabajo. Es preciso considerar el costo de mantenimiento del índice XML durante la modificación de datos.

• Los valores XML son relativamente grandes y las partes recuperadas son relativamente pequeñas. La generación del índice evita tener que analizar todo el conjunto de datos en tiempo de ejecución y favorece las búsquedas basadas en índices que permiten un procesamiento más eficiente de las consultas.

A partir de SQL Server 2022 (16.x) y versiones posteriores, así como en Azure SQL Database, SQL Database en Microsoft Fabric y Instancia Administrada de Azure SQL, puede usar la compresión XML para comprimir los datos XML fuera de fila, tanto para columnas XML como para índices. La compresión XML reduce los requisitos de capacidad de almacenamiento de datos.

Los índices XML se dividen en las categorías siguientes:

• Índice XML principal
• Índice XML secundario

El primer índice de la columna de tipo xml debe ser el índice XML principal. Con el índice XML principal, se admiten los siguientes tipos de índices secundarios: PATH, VALUE y PROPERTY. Dependiendo del tipo de consulta, los índices secundarios pueden contribuir a mejorar el rendimiento.

Las instancias XML se almacenan en las columnas de tipo xml como objetos binarios grandes (BLOB). Estas instancias XML pueden ser grandes, y la representación binaria almacenada de instancias de datos de tipo xml puede tener un tamaño de hasta 2 GB. Sin ningún índice, estos objetos binarios grandes se dividen en tiempo de ejecución para evaluar una consulta. Esta trituración puede llevar mucho tiempo. Por ejemplo, considere la siguiente consulta:

;WITH XMLNAMESPACES ('https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS "PD")

SELECT CatalogDescription.query('
  /PD:ProductDescription/PD:Summary
') as Result
FROM Production.ProductModel
WHERE CatalogDescription.exist ('/PD:ProductDescription/@ProductModelID[.="19"]') = 1;

Para seleccionar instancias XML que cumplan la condición de la cláusula WHERE , el objeto binario grande (BLOB) XML de cada fila de la tabla Production.ProductModel se divide en tiempo de ejecución. A continuación, se evalúa la expresión (/PD:ProductDescription/@ProductModelID[.="19"]) en el método exist() . Esta división en tiempo de ejecución puede ser costosa, en función del tamaño y el número de instancias almacenadas en la columna.

Si las consultas de objetos binarios grandes (BLOB) XML son frecuentes en su entorno de aplicación, será útil indexar las columnas de tipo xml . No obstante, el mantenimiento del índice durante la modificación de datos lleva un costo asociado.

Índice XML principal

El índice XML principal incluye todas las etiquetas, los valores y las rutas de acceso de las instancias XML de una columna XML. Para crear un índice XML principal, la tabla que contiene la columna XML, debe tener un índice clúster en la clave principal de la tabla. SQL Server utiliza esta clave principal para correlacionar las filas del índice XML principal con las filas de la tabla que contiene la columna XML.

El índice XML principal es una representación desglosada y persistente de los BLOB XML de la columna del tipo de datos xml. Para cada objeto grande binario (BLOB) XML en la columna, el índice crea varias filas de datos. El número de filas del índice es aproximadamente igual al número de nodos del objeto grande binario XML. Cuando una consulta recupera la instancia XML completa, SQL Server proporciona la instancia a partir de la columna XML. Las consultas dentro de instancias XML usan el índice XML principal y pueden devolver valores escalares o subárboles XML utilizando el propio índice.

Cada fila almacena la siguiente información acerca del nodo:

• Nombre de etiqueta (p. ej., un nombre de elemento o atributo).

• Valor del nodo.

• Tipo de nodo (p. ej., un nodo de elemento, de atributo o de texto).

• Información sobre el orden del documento, representada mediante un identificador de nodo interno.

• Ruta de acceso desde cada nodo a la raíz del árbol XML. En esta columna se buscan expresiones de ruta en la consulta.

• Clave principal de la tabla base. La clave principal de la tabla base está duplicada en el índice XML principal para mantener la combinación con la tabla base, y el número máximo de columnas en la clave principal de la tabla base se limita a 15.

Esta información de nodo se utiliza para evaluar y crear resultados XML para una consulta específica. Con fines de optimización, el nombre de la etiqueta y la información sobre el tipo de nodo se codifican como valores enteros, y la columna Path utiliza la misma codificación. Asimismo, las rutas de acceso se almacenan en orden inverso para permitir rutas de acceso coincidentes cuando solo se conoce el sufijo de la ruta de acceso. Por ejemplo:

• //ContactRecord/PhoneNumber , donde solo se conocen los dos últimos pasos.

O BIEN

• /Book/*/Title donde el carácter comodín * se especifica en mitad de la expresión.

El procesador de consultas usa el índice XML principal para las consultas relacionadas con los xml Data Type Methods y devuelve valores escalares o los subárboles XML del propio índice principal. (Este índice almacena toda la información necesaria para volver a construir la instancia XML).

Por ejemplo, la siguiente consulta devuelve información de resumen almacenada en la columna de CatalogDescription tipo xml de la tabla ProductModel. La consulta devuelve información perteneciente a <Summary> solo para modelos de producto cuya descripción de catálogo también almacena información sobre <Features>.

;WITH XMLNAMESPACES ('https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS "PD")

SELECT CatalogDescription.query('  /PD:ProductDescription/PD:Summary') as Result
FROM Production.ProductModel
WHERE CatalogDescription.exist ('/PD:ProductDescription/PD:Features') = 1

En relación con el índice XML principal, en lugar de descomponer cada instancia de BLOB XML de la tabla base, las filas del índice que corresponden a cada BLOB XML se recorren secuencialmente en busca de la expresión especificada en el método exist(). Si la ruta se encuentra en la columna Path del índice, el elemento <Summary>, junto con sus subárboles, se recupera del índice XML principal y se convierte en un objeto binario grande XML como resultado del método query().

El índice XML principal no se utiliza al recuperar una instancia XML completa. Por ejemplo, la consulta siguiente recupera de la tabla la instancia XML completa que describe las instrucciones de fabricación para un modelo determinado de producto.

USE AdventureWorks2022;

SELECT Instructions
FROM   Production.ProductModel
WHERE  ProductModelID = 7;

Índices XML secundarios

Para mejorar los resultados de las búsquedas, pueden crearse índices XML secundarios. Antes de crear índices secundarios, debe existir un índice XML principal. A continuación, se indican los tipos existentes:

• Índice XML secundario PATH

• Índice XML secundario VALUE

• Propiedad: índice XML secundario

A continuación se incluyen algunas directrices para crear uno o varios índices secundarios:

• Si su carga de trabajo utiliza de forma intensiva expresiones de ruta en columnas XML, es probable que el índice XML secundario PATH acelere su carga de trabajo. El caso más habitual es el uso del método exist() con columnas XML en la cláusula WHERE de Transact-SQL.

• Si la carga de trabajo recupera varios valores a partir de instancias XML individuales empleando expresiones de ruta de acceso, puede resultar útil la agrupación en clústeres de las rutas dentro de cada instancia XML en el índice PROPERTY. Este escenario suele producirse en un escenario de contenedor de propiedades, cuando se recuperan las propiedades de un objeto y se conoce el valor de su clave primaria.

• Si la carga de trabajo implica consultar valores dentro de instancias XML sin conocer los nombres de los elementos o atributos que contienen dichos valores, puede ser útil crear el índice VALUE. Esto suele ocurrir con búsquedas en ejes descendentes, como //author[last-name="Howard"], donde los elementos <author> pueden aparecer en cualquier nivel de la jerarquía. También ocurre en consultas con caracteres comodín, como /book [@* = "novel"], donde la consulta busca elementos <book> que tengan algún atributo con el valor "novel".

Índice XML secundario PATH

Si sus consultas suelen especificar expresiones de ruta de acceso en columnas de tipo xml , un índice secundario PATH podría acelerar la búsqueda. Como se indicó anteriormente en este artículo, el índice principal resulta útil cuando se realizan consultas que especifican el método exist() en la cláusula WHERE. Si agrega un índice secundario PATH, también puede mejorar los resultados de búsqueda en dichas consultas.

Aunque un índice XML principal evita tener que descomponer en tiempo de ejecución los objetos binarios grandes XML, es posible que no proporcione el mejor rendimiento para consultas basadas en expresiones de ruta. Dado que, en el caso de instancias XML grandes, todas las filas del índice XML principal correspondientes a un objeto binario grande XML se examinan secuencialmente, la búsqueda secuencial puede ser lenta. En este caso, incorporar un índice secundario a los valores de ruta de acceso y de nodo del índice principal puede aumentar significativamente la velocidad de búsqueda del índice. En el índice secundario PATH, los valores de ruta y de nodo son columnas de clave que permiten búsquedas más eficaces al buscar rutas. El optimizador de consultas puede utilizar el índice PATH para expresiones como las siguientes:

• /root/Location que solo especifican una ruta

O BIEN

• /root/Location/@LocationID[.="10"], donde se especifican los valores de ruta de acceso y de nodo.

La consulta siguiente muestra en qué lugar es útil el índice PATH:

;WITH XMLNAMESPACES ('https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS "PD")

SELECT CatalogDescription.query('
  /PD:ProductDescription/PD:Summary
') AS Result
FROM Production.ProductModel
WHERE CatalogDescription.exist ('/PD:ProductDescription/@ProductModelID[.="19"]') = 1;

En la consulta, la expresión de ruta de acceso /PD:ProductDescription/@ProductModelID y el valor "19" del método exist() corresponden a los campos clave del índice PATH. Esto permite realizar búsquedas directas en el índice PATH y ofrece mejores resultados que la búsqueda secuencial de valores de ruta de acceso en el índice principal.

Índice XML secundario VALUE

Si las consultas se basan en valores, como, por ejemplo, /Root/ProductDescription/@*[. = "Mountain Bike"] o //ProductDescription[@Name = "Mountain Bike"], y la ruta de acceso no se especifica completamente o incluye un carácter comodín, se pueden obtener resultados más rápidos creando un índice XML secundario que se agregue a los valores de nodo en el índice XML principal.

Las columnas de clave del índice VALUE (valor de nodo y ruta de acceso) pertenecen al índice XML principal. Si la carga de trabajo requiere consultar valores de instancias XML sin conocer los nombres de elemento o atributo que contienen los valores, un índice VALUE puede resultar útil. Por ejemplo, la siguiente expresión se beneficiará del índice VALUE:

• //author[LastName="someName"], donde se conoce el valor del elemento <LastName>, pero el elemento primario <author> puede estar en cualquier lugar.

• /book[@* = "someValue"], donde la consulta busca el elemento <book> que contiene algún atributo con el valor "someValue".

La consulta siguiente devuelve ContactID de la tabla Contact . La cláusula WHERE especifica un filtro que busca valores en la columna AdditionalContactInfo de tipo xml. Los identificadores de contacto se devuelven solo si el correspondiente objeto binario grande XML de información de contacto adicional incluye un número de teléfono específico. Dado que el elemento telephoneNumber puede aparecer en cualquier parte del XML, la expresión de ruta especifica el eje descendiente-o-el-mismo.

;WITH XMLNAMESPACES (
  'https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo' AS CI,
  'https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes' AS ACT
)

SELECT ContactID
FROM Person.Contact
WHERE AdditionalContactInfo.exist('//ACT:telephoneNumber/ACT:number[.="111-111-1111"]') = 1;

En esta situación, se conoce el valor de búsqueda para <number>, pero puede aparecer en cualquier lugar de la instancia XML como elemento secundario del elemento telephoneNumber. Este tipo de consulta puede beneficiarse de una búsqueda de índice basada en un valor específico.

Propiedad índice secundario

Las consultas que recuperan uno o varios valores de instancias XML individuales pueden beneficiarse del índice PROPERTY. Este escenario se produce al recuperar propiedades del objeto usando el método value() del tipo xml y cuando se conoce el valor de clave principal del objeto.

El índice PROPERTY se basa en las columnas (PK, path y node value) del índice XML principal, donde PK es la clave principal de la tabla base.

Por ejemplo, para el modelo de producto 19, la consulta siguiente recupera los valores de los atributos ProductModelID y ProductModelName mediante el método value() . En lugar de utilizar el índice XML principal o los otros índices XML secundarios, el índice PROPERTY puede ofrecer una ejecución más rápida.

;WITH XMLNAMESPACES ('https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS "PD")

SELECT CatalogDescription.value('(/PD:ProductDescription/@ProductModelID)[1]', 'int') AS ModelID,
  CatalogDescription.value('(/PD:ProductDescription/@ProductModelName)[1]', 'varchar(30)') AS ModelName
FROM Production.ProductModel
WHERE ProductModelID = 19;

A excepción de las diferencias descritas más adelante en este artículo, la creación de un índice XML en una columna de tipo xml es similar a la creación de un índice en una columna de tipo no xml . Las siguientes instrucciones DDL Transact-SQL pueden usarse para crear y administrar índices XML:

• CREATE INDEX (Transact-SQL)
• ALTER INDEX (Transact-SQL)
• DROP INDEX (Transact-SQL)

Compresión XML

Se aplica a: SQL Server 2022 (16.x) y versiones posteriores, y en Azure SQL Database, SQL database en Microsoft Fabric e Instancia administrada de Azure SQL.

La habilitación de la compresión XML cambia el formato de almacenamiento físico de los datos asociados con el tipo de datos XML a un formato binario comprimido, pero no cambia la sintaxis ni la semántica de los datos XML. No es necesario realizar cambios en la aplicación cuando una o varias tablas están habilitadas para la compresión XML.

Solo el tipo de datos XML se ve afectado por la compresión XML. Los datos XML se comprimen con el algoritmo de compresión Xpress. Los índices XML existentes se comprimen mediante la compresión de datos. La compresión de datos se habilita internamente para los índices XML cuando se habilita la compresión XML.

La compresión XML se puede habilitar en paralelo con la compresión de datos en las mismas tablas.

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.

Obtener información acerca de los índices XML

Las entradas de índice XML aparecen en la vista de catálogo sys.indexes con el índice type de 3. La columna del nombre contiene el nombre del índice XML.

Los índices XML también se registran en la vista de catálogo sys.xml_indexes. Esta contiene todas las columnas de sys.indexes y algunas específicas que son útiles para índices XML. El valor NULL de la columna secondary_type indica un índice XML principal; los valores P, R y V representan los índices XML secundarios PATH, PROPERTY y VALUE, respectivamente.

El uso del espacio de los índices XML se puede consultar en la función con valores de tabla sys.dm_db_index_physical_stats. Contiene información como el número de páginas de datos ocupadas, el tamaño medio de las filas en bytes y el número de registros de todos los tipos de índice. Se refiere también a los índices XML. Esta información está disponible para cada partición de base de datos. Los índices XML usan el mismo esquema de partición y la misma función de partición que la tabla base.

Pasos siguientes

• Crear índices XML
• ALTER INDEX (Índices XML selectivos)
• sys.dm_db_index_physical_stats (Transact-SQL)
• Datos XML (SQL Server)