Índices XML (SQL Server)

Aplica-se a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceBase de dados SQL no Microsoft Fabric

Os índices XML podem ser criados em colunas de tipo de dados xml . Eles indexam todas as tags, valores e caminhos nas instâncias XML na coluna e melhoram o desempenho das consultas. Seu aplicativo pode se beneficiar de um índice XML nas seguintes situações:

• Consultas em colunas XML são comuns em sua carga de trabalho. O custo de manutenção do índice XML durante a modificação dos dados deve ser considerado.

• Seus valores XML são relativamente grandes e as partes recuperadas são relativamente pequenas. A criação do índice evita a análise de todos os dados em tempo de execução e beneficia pesquisas de índice para um processamento de consulta eficiente.

A partir do SQL Server 2022 (16.x) e versões posteriores, e no Azure SQL Database, SQL Database no Microsoft Fabric e Azure SQL Managed Instance, pode usar compressão XML para comprimir dados XML fora da linha tanto para colunas como para índices XML. A compactação XML reduz os requisitos de capacidade de armazenamento de dados.

Os índices XML se enquadram nas seguintes categorias:

• Índice XML primário
• Índice XML secundário

O primeiro índice na coluna de tipo xml deve ser o índice XML primário. Usando o índice XML primário, os seguintes tipos de índices secundários são suportados: PATH, VALUE e PROPERTY. Dependendo do tipo de consultas, esses índices secundários podem ajudar a melhorar o desempenho da consulta.

Observação

Não é possível criar ou modificar um índice XML, a menos que as opções do banco de dados estejam definidas corretamente para trabalhar com o tipo de dados xml . Para obter mais informações, consulte Usar Full-Text pesquisa com colunas XML.

As instâncias XML são armazenadas em colunas de tipo xml como BLOBs (objetos binários grandes). Essas instâncias XML podem ser grandes e a representação binária armazenada de instâncias de tipo de dados xml pode ser de até 2 GB. Sem um índice, esses objetos binários grandes são destruídos em tempo de execução para avaliar uma consulta. Esta trituração pode ser demorada. Por exemplo, considere a consulta seguinte:

;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 selecionar as instâncias XML que satisfazem a condição na WHERE cláusula, o BLOB (objeto binário grande) XML em cada linha da tabela Production.ProductModel é destruído em tempo de execução. Em seguida, a expressão (/PD:ProductDescription/@ProductModelID[.="19"]) no exist() método é avaliada. Essa eliminação durante a execução pode ser cara, dependendo do tamanho e do número de instâncias armazenadas na coluna.

Se a consulta de BLOBs (objetos binários grandes) XML for comum em seu ambiente de aplicativo, isso ajudará a indexar as colunas de tipo xml . No entanto, há um custo associado à manutenção do índice durante a modificação dos dados.

Índice XML primário

O índice XML primário indexa todas as marcas, valores e caminhos dentro das instâncias XML em uma coluna XML. Para criar um índice XML primário, a tabela na qual a coluna XML ocorre deve ter um índice clusterizado na chave primária da tabela. O SQL Server usa essa chave primária para correlacionar linhas no índice XML primário com linhas na tabela que contém a coluna XML.

O índice XML primário é uma representação fragmentada e persistente dos BLOBs XML na coluna de tipo de dados xml . Para cada BLOB (objeto binário grande) XML na coluna, o índice cria várias linhas de dados. O número de linhas no índice é aproximadamente igual ao número de nós no objeto binário XML grande. Quando uma consulta recupera a instância XML completa, o SQL Server fornece a instância da coluna XML. As consultas em instâncias XML usam o índice XML primário e podem retornar valores escalares ou subárvores XML usando o próprio índice.

Cada linha armazena as seguintes informações de nó:

• Nome de etiqueta, como o nome de um elemento ou atributo.

• Valor do nó.

• Tipo de nó, como um nó de elemento, nó de atributo ou nó de texto.

• Informações de ordem do documento, representadas por um identificador de nó interno.

• Caminho de cada nó até à raiz da árvore XML. Esta coluna é pesquisada por expressões de caminho na consulta.

• Chave primária da tabela base. A chave primária da tabela base é duplicada no índice XML primário para uma junção posterior com a tabela base, e o número máximo de colunas na chave primária da tabela base é limitado a 15.

Essas informações de nó são usadas para avaliar e construir resultados XML para uma consulta especificada. Para fins de otimização, o nome da etiqueta e as informações de tipo de nó são codificados como valores inteiros, e a coluna Caminho utiliza a mesma codificação. Além disso, os caminhos são armazenados em ordem inversa para permitir caminhos correspondentes quando apenas o sufixo de caminho é conhecido. Por exemplo:

• //ContactRecord/PhoneNumber onde apenas os dois últimos passos são conhecidos

OU

• /Book/*/Title onde o caractere * curinga é especificado no meio da expressão.

O processador de consultas usa o índice XML primário para consultas que envolvem métodos de tipo de dados xml e retorna valores escalares ou as subárvores XML do próprio índice primário. (Esse índice armazena todas as informações necessárias para reconstruir a instância XML.)

Por exemplo, a consulta a seguir retorna informações de resumo armazenadas na coluna de CatalogDescription tipo xml na ProductModel tabela. A consulta retorna <Summary> informações somente para modelos de produtos cuja descrição do catálogo também armazena a <Features> descrição.

;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

Em relação ao índice XML primário, em vez de destruir cada instância de objeto binário grande XML na tabela base, as linhas no índice que correspondem a cada objeto binário grande XML são pesquisadas sequencialmente para a expressão especificada no exist() método. Se o caminho for encontrado na coluna Path no índice, o <Summary> elemento juntamente com suas subárvores será recuperado do índice XML primário e convertido em um objeto binário XML grande como resultado do query() método.

O índice XML primário não é usado ao recuperar uma instância XML completa. Por exemplo, a consulta a seguir recupera da tabela toda a instância XML que descreve as instruções de fabricação para um modelo de produto específico.

USE AdventureWorks2022;

SELECT Instructions
FROM   Production.ProductModel
WHERE  ProductModelID = 7;

Índices XML secundários

Para melhorar o desempenho da pesquisa, você pode criar índices XML secundários. Um índice XML primário deve existir primeiro antes que você possa criar índices secundários. Estes são os tipos:

• Índice XML secundário PATH

• Índice XML secundário VALUE

• Índice XML secundário PROPERTY

A seguir estão algumas diretrizes para criar um ou mais índices secundários:

• Se sua carga de trabalho usa expressões de caminho significativamente em colunas XML, o índice XML secundário PATH provavelmente acelerará sua carga de trabalho. O caso mais comum é o uso do exist() método em colunas XML na cláusula WHERE do Transact-SQL.

• Se a sua carga de trabalho recuperar vários valores de instâncias XML individuais usando expressões de caminho, agrupar caminhos dentro de cada instância XML no índice PROPERTY pode ser útil. Esse cenário normalmente ocorre em um cenário de conjunto de propriedades quando as propriedades de um objeto são buscadas e seu valor de chave primária é conhecido.

• Se sua carga de trabalho envolver a consulta de valores em instâncias XML sem conhecer os nomes de elementos ou atributos que contêm esses valores, convém criar o índice VALUE. Isso normalmente ocorre com pesquisas de eixos descendentes, como //author[last-name="Howard"], onde <author> os elementos podem ocorrer em qualquer nível da hierarquia. Também ocorre em consultas curinga, como /book [@* = "novel"], onde a consulta procura <book> elementos que tenham algum atributo com o valor "novel".

Índice XML secundário PATH

Se suas consultas geralmente especificam expressões de caminho em colunas de tipo xml , um índice secundário PATH pode ser capaz de acelerar a pesquisa. Conforme descrito anteriormente neste artigo, o índice primário é útil quando você tem consultas que especificam exist() o método na cláusula WHERE. Se você adicionar um índice secundário PATH, também poderá melhorar o desempenho da pesquisa nessas consultas.

Embora um índice XML primário evite ter que destruir os objetos binários XML grandes em tempo de execução, ele pode não fornecer o melhor desempenho para consultas baseadas em expressões de caminho. Como todas as linhas no índice XML primário correspondente a um objeto binário XML grande são pesquisadas sequencialmente para instâncias XML grandes, a pesquisa sequencial pode ser lenta. Nesse caso, ter um índice secundário construído sobre os valores de caminho e de nó no índice primário pode acelerar significativamente a pesquisa de índice. No índice secundário PATH, os valores de caminho e nó são colunas-chave que permitem buscas mais eficientes ao pesquisar caminhos. O otimizador de consulta pode usar o índice PATH para expressões como as mostradas a seguir:

• /root/Location que especificam apenas um caminho

OU

• /root/Location/@LocationID[.="10"] onde o caminho e o valor do nó são especificados.

A consulta a seguir mostra onde o índice PATH é útil:

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

Na consulta, a expressão /PD:ProductDescription/@ProductModelID de caminho e o valor "19" no exist() método correspondem aos campos-chave do índice PATH. Isso permite a busca direta no índice PATH e fornece melhor desempenho de pesquisa do que a pesquisa sequencial por valores de caminho no índice primário.

Índice XML secundário VALUE

Se as consultas forem baseadas em valor, por exemplo, /Root/ProductDescription/@*[. = "Mountain Bike"] ou //ProductDescription[@Name = "Mountain Bike"], e o caminho não estiver totalmente especificado ou incluir um curinga, você poderá obter resultados mais rápidos criando um índice XML secundário criado com base em valores de nó no índice XML primário.

As colunas principais do índice VALUE, que são o valor do nó e o caminho, pertencem ao índice XML primário. Se sua carga de trabalho envolver a consulta de valores de instâncias XML sem conhecer os nomes de elementos ou atributos que contêm os valores, um índice VALUE pode ser útil. Por exemplo, a expressão a seguir se beneficiará de ter um índice VALUE:

• //author[LastName="someName"] onde você sabe o valor do <LastName> elemento, mas o <author> pai pode ocorrer em qualquer lugar.

• /book[@* = "someValue"] onde a consulta procura o <book> elemento que tem algum atributo com o valor "someValue".

A consulta a seguir retorna ContactID da Contact tabela. A WHERE cláusula especifica um filtro que procura valores na AdditionalContactInfo coluna de tipo xml. Os IDs de contacto são retornados somente se as informações adicionais de contacto correspondentes incluírem um objeto binário grande XML com um número de telefone específico. Como o telephoneNumber elemento pode aparecer em qualquer lugar no XML, a expressão de caminho especifica o eixo descendente ou próprio.

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

Nessa situação, o valor de pesquisa para <number> é conhecido, mas pode aparecer em qualquer lugar na instância XML como um filho do telephoneNumber elemento . Esse tipo de consulta pode se beneficiar de uma pesquisa de índice com base em um valor específico.

Índice secundário de propriedade

As consultas que recuperam um ou mais valores de instâncias XML individuais podem se beneficiar de um índice PROPERTY. Esse cenário ocorre quando você recupera propriedades de objeto usando o value() método do tipo xml e quando o valor da chave primária do objeto é conhecido.

O índice PROPERTY é construído em colunas (PK, caminho e valor do nó) do índice XML primário, onde PK é a chave primária da tabela base.

Por exemplo, para o modelo de produto 19, a consulta a seguir recupera os valores dos atributos ProductModelID e ProductModelName usando o método value(). Em vez de usar o índice XML primário ou outros índices XML secundários, o índice PROPERTY pode fornecer uma execução mais 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;

Exceto pelas diferenças descritas posteriormente neste artigo, a criação de um índice XML em uma coluna de tipo xml é semelhante à criação de um índice em uma coluna de tipo não-xml . As seguintes instruções DDL Transact-SQL podem ser usadas para criar e gerenciar índices XML:

• CRIAR ÍNDICE (Transact-SQL)
• ALTERAR ÍNDICE (Transact-SQL)
• ELIMINAR ÍNDICE (Transact-SQL)

Compactação XML

Aplica-se a: SQL Server 2022 (16.x) e versões posteriores, no Azure SQL Database, SQL Database no Microsoft Fabric e Azure SQL Managed Instance.

A habilitação da compactação XML altera o formato de armazenamento físico dos dados associados ao tipo de dados XML para um formato binário compactado, mas não altera a sintaxe ou a semântica dos dados XML. As alterações no aplicativo não são necessárias quando uma ou mais tabelas estão habilitadas para compactação XML.

Somente o tipo de dados XML é afetado pela compactação XML. Os dados XML são compactados com o algoritmo de compactação Xpress. Todos os índices XML existentes são compactados usando a compactação de dados. A compactação de dados é habilitada internamente para índices XML quando a compactação XML está habilitada.

A compactação XML pode ser habilitada lado a lado com a compactação de dados nas mesmas tabelas.

Os índices XML não herdam a propriedade de compactação da tabela. Para compactar índices, você deve habilitar explicitamente a compactação XML em índices XML.

Os índices XML secundários não herdam a propriedade de compactação do índice XML primário.

Por padrão, a configuração de compactação XML para índices XML é definida como OFF quando o índice é criado.

Obter informações sobre índices XML

As entradas de índice XML aparecem na exibição sys.indexes de catálogo com o índice type de 3. A coluna de nome contém o nome do índice XML.

Os índices XML também são registrados na exibição sys.xml_indexesde catálogo. Isso contém todas as colunas de sys.indexes e algumas específicas que são úteis para índices XML. O valor NULL na coluna secondary_type indica um índice XML primário, os valores Pe RV representam os índices XML secundários PATH, PROPERTY e VALUE, respectivamente.

O uso de espaço de índices XML pode ser encontrado na função com valor de tabela sys.dm_db_index_physical_stats. Ele fornece informações, como o número de páginas de dados ocupadas, o tamanho médio da linha em bytes e o número de registros, para todos os tipos de índice. Isso também inclui índices XML. Essas informações estão disponíveis para cada partição de banco de dados. Os índices XML usam o mesmo esquema de particionamento e função de particionamento da tabela base.

Próximos passos

• Criar índices XML
• ALTER INDEX (Índices XML seletivos)
• sys.dm_db_index_physical_stats (Transact-SQL)
• Dados XML (SQL Server)