Índices XML (SQL Server)

Índices XML podem ser criados em xml colunas de tipo de dados. Eles indexam todas as tags, valores e caminhos nas instâncias XML na coluna e beneficiam 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 de 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 analisar todos os dados em tempo de execução e beneficia pesquisas de índice para processamento eficiente de consulta.

Os índices XML se enquadram nas seguintes categorias:

• Índice XML primário

• Índice XML secundário

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

Observação

Você não pode criar ou modificar um índice XML, a menos que as opções de banco de dados sejam definidas corretamente para trabalhar com o xml tipo de dados. Para obter mais informações, consulte Use a Busca Full-Text com Colunas XML.

As instâncias XML são armazenadas como objetos binários grandes (BLOBs) em colunas do tipo xml. Essas instâncias XML podem ser grandes e a representação binária armazenada de instâncias de tipo de xml dados pode ser de até 2 GB. Sem um índice, esses objetos binários grandes são fragmentados em tempo de execução para avaliar uma consulta. Essa fragmentação pode ser demorada. Por exemplo, considere a seguinte 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 selecionar as instâncias XML que atendem à condição na WHERE cláusula, o BLOB (objeto binário grande) XML em cada linha da tabela Production.ProductModel é fragmentado em tempo de execução. Em seguida, a expressão (/PD:ProductDescription/@ProductModelID[.="19"]) no exist() método é avaliada. Essa fragmentação em tempo de execução pode ser dispendiosa, 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, ajuda a indexar as colunas do tipo xml. No entanto, há um custo associado à manutenção do índice durante a modificação de 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 do 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 sobre o nó.

• Nome de tag, como um elemento ou nome de 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é a raiz da árvore XML. Esta coluna é pesquisada em busca de 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 de fundo 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 tag e as informações do tipo de nó são codificados como valores inteiros, e a coluna Caminho usa 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 as duas últimas etapas são conhecidas

OU

• /Book/*/Title em que o caractere curinga (*) é especificado no meio da expressão.

O processador de consulta usa o índice XML primário para consultas que envolvem métodos de tipo de dados xml e retorna valores escalares ou 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 resumidas armazenadas na coluna do tipo CatalogDescription``xml na tabela ProductModel. A consulta retorna <Summary> informações somente para modelos de produto 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 ResultFROM Production.ProductModelWHERE CatalogDescription.exist ('/PD:ProductDescription/PD:Features') = 1  

Em relação ao índice XML primário, em vez de fragmentar cada instância de objeto binário grande XML na tabela base, as linhas no índice que correspondem a cada objeto binário XML grande são pesquisadas sequencialmente pela expressão especificada no exist() método. Se o caminho for encontrado na coluna Path no índice, o <Summary> elemento junto 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.

Observe que 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 AdventureWorks2012;SELECT InstructionsFROM 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 usar 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 método exist() 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 poderá ser útil. Esse cenário normalmente ocorre em um cenário de recipiente de propriedades quando as propriedades de um objeto são buscadas e seu valor de chave primária é conhecido.

• Se a carga de trabalho envolver a consulta de valores em instâncias XML sem saber os nomes de elemento ou atributo que contêm esses valores, talvez você queira criar o índice VALUE. Isso normalmente ocorre com consultas com eixos de descendentes, como //author[sobrenome="Howard"], em <que elementos do autor> podem ocorrer em qualquer nível da hierarquia. Também ocorre em consultas curinga, como /book [@* = "novel"], em que a consulta procura por <elementos book> que têm algum atributo com o valor "novel".

Índice XML secundário PATH

Se suas consultas geralmente especificam expressões de caminho em colunas do tipo xml, um índice secundário PATH poderá acelerar a pesquisa. Conforme descrito anteriormente neste tópico, o índice primário é útil quando você tem consultas que especificam o método exist() 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 fragmentar os objetos binários grandes XML em tempo de execução, ele pode não fornecer o melhor desempenho para consultas com base em expressões de caminho. Como todas as linhas no índice XML primário correspondentes a um objeto binário grande XML são pesquisadas sequencialmente em busca de instâncias XML grandes, a pesquisa sequencial pode ser lenta. Nesse caso, ter um índice secundário baseado nos valores dos caminhos e dos nós no índice primário pode acelerar significativamente a busca no í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 no seguinte:

• /root/Location que especifica apenas um caminho

OU

• /root/Location/@LocationID[.="10"] em que 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 de caminho /PD:ProductDescription/@ProductModelID e o valor "19" no método exist() correspondem aos campos de chave do índice PATH. Isso permite a busca direta no índice PATH e fornece melhor desempenho de pesquisa do que a pesquisa sequencial de 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 for totalmente especificado ou incluir um curinga, você poderá obter resultados mais rápidos criando um índice XML secundário baseado nos valores dos nós no índice XML primário.

As colunas-chave do índice VALUE são o valor e o caminho do nó do índice XML primário. Se a carga de trabalho envolver a consulta de valores de instâncias XML sem saber os nomes de elemento ou atributo que contêm os valores, um índice VALUE poderá 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 elemento <LastName>, mas o elemento pai <author> pode ocorrer em qualquer lugar.

• /book[@* = "someValue"]em que a consulta procura pelo elemento <book> que possui 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 coluna de AdditionalContactInfo``xml tipo. Os IDs de contato são retornados somente se o objeto binário grande XML correspondente de informação de contato adicional incluir um número de telefone específico. Como o elemento <telephoneNumber> 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 <number> é conhecido, mas pode aparecer em qualquer lugar na instância XML como um subelemento do elemento <telephoneNumber>. Esse tipo de consulta pode se beneficiar de uma pesquisa de índice com base em um valor específico.

ÍNDICE SECUNDÁRIO PROPRIEDADE

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 as propriedades do objeto usando o método value() do xml tipo e quando o valor da chave primária do objeto é conhecido.

O índice PROPERTY é criado nas 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 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 tópico, a criação de um índice XML em umaxml coluna de tipo é semelhante à criação de um índice em uma coluna que não seja dexml tipo. As seguintes instruções DDL Transact-SQL podem ser usadas para criar e gerenciar índices XML:

• Criar Índice (Transact-SQL)

• ALTER INDEX (Transact-SQL)

• DROP INDEX (Transact-SQL)

Obtendo informações sobre índices XML

As entradas de índice XML aparecem na visão do catálogo, sys.indexes, com o índice de "tipo" 3. A coluna de nome contém o nome do índice XML.

Índices XML também são registrados na exibição de catálogo, sys.xml_indexes. 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 'P', 'R' e 'V' representam í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 disco 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.

Consulte Também

sys.dm_db_index_physical_stats (Transact-SQL)
Dados XML (SQL Server)