Criar índices XML
Aplica-se a:
SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do Azure
Este artigo descreve como criar índices XML primários e secundários.
Criar um índice XML primário
Para criar um índice XML primário, use a instrução DDL Transact-SQL CREATE INDEX (Transact-SQL). Nem todas as opções disponíveis para índices não XML têm suporte em índices XML.
Observe o seguinte ao criar um índice XML:
Para criar um índice XML, a tabela que contém a coluna XML que está sendo indexada, chamada de tabela base, deve ter um índice clusterizado na chave primária. Esse índice agrupado garante que, se a tabela base for particionada, o índice XML primário possa ser particionado usando o mesmo esquema de particionamento e a mesma função de particionamento.
Se houver um índice XML, a chave primária agrupada da tabela não poderá ser modificada. Você terá que remover todos os índices XML da tabela antes de modificar a chave primária.
Um índice XML primário pode ser criado em uma única coluna de tipo xml . Não é possível criar nenhum outro tipo de índice com a coluna do tipo xml como uma coluna-chave. No entanto, você pode incluir a coluna de tipo xml em um índice não XML. Cada coluna de tipo xml em uma tabela pode ter seu próprio índice XML primário. Porém, apenas um índice XML primário por coluna de tipo xml é permitido.
Índices XML existem no mesmo namespace que índices não XML. Portanto, não pode haver um índice XML e um índice não XML na mesma tabela com o mesmo nome.
As opções IGNORE_DUP_KEY e ONLINE sempre estão definidas como OFF para índices XML. Essas opções podem ser especificadas com um valor de OFF.
As informações de grupo de arquivos ou de particionamento da tabela de usuários são aplicadas ao índice XML e não podem ser especificadas separadamente.
A opção de índice DROP_EXISTING pode descartar um índice XML primário e criar um novo índice XML primário ou descartar um índice XML secundário e criar um novo índice XML secundário. No entanto, essa opção não pode remover um índice XML secundário para criar um novo índice XML primário, ou vice-versa.
Nomes de índice XML primário têm as mesmas restrições que nomes de exibição.
Não é possível criar um índice XML em uma coluna do tipo xml em uma exibição, em uma variável com valor de tabela com colunas do tipo xml ou em variáveis do tipo xml.
Para alterar uma coluna de tipo xml de XML sem-tipo para XML com tipo ou vice-versa, usando a opção ALTER TABLE ALTER COLUMN, nenhum índice XML deve existir na coluna. Se existir um índice, ele deve ser descartado antes da tentativa de alterar o tipo de coluna.
A opção ARITHABORT deve ser definida como ON quando um índice XML é criado. Para consultar, inserir, excluir ou atualizar valores na coluna XML usando métodos de tipo de dados xml, a mesma opção deve estar definida na conexão. Se não estiver, os métodos de tipo de dados xml falharão.
Observação
Informações sobre um índice XML podem ser localizadas em exibições do catálogo. No entanto, não há suporte para sp_helpindex. Os exemplos fornecidos mais adiante neste tópico mostram como consultar as exibições do catálogo para localizar informações sobre índices XML.
Ao criar ou recriar um índice XML primário em uma coluna de tipo de dados xml que contém valores de tipos de Esquema XML xs:date ou xs:dateTime (ou quaisquer subtipos desses tipos) que têm um ano menor que 1, haverá falha na criação do índice no SQL Server 2008 (10.0.x) e em versões posteriores. O SQL Server 2005 (9.x) permitia esses valores, portanto, esse problema pode ocorrer ao criar índices em um banco de dados gerado no SQL Server 2005 (9.x). Para obter mais informações, consulte Comparar XML digitado com XML não digitado.
Exemplo: criar um índice XML primário
A tabela T (pk INT PRIMARY KEY, xCol XML) com uma coluna XML não tipada é usada na maioria dos exemplos. Esses podem ser estendidos para XML com tipo de uma maneira direta. Para simplificar, as consultas são descritas para instâncias de dados XML, conforme mostrado no exemplo a seguir:
<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>
A instrução a seguir cria um índice XML, chamado idx_xCol, na coluna XML xCol da tabela T:
CREATE PRIMARY XML INDEX idx_xCol on T (xCol)
Criar um índice XML secundário
Use a instrução DDL Transact-SQL CREATE INDEX (Transact-SQL) para criar índices XML secundários e especificar o tipo do índice XML secundário desejado.
Observe o seguinte ao criar índices XML secundários:
São permitidas todas as opções de indexação que se aplicam a um índice não clusterizado, exceto IGNORE_DUP_KEY e ONLINE, em índices XML secundários. As duas opções sempre devem ser definidas como OFF para índices XML secundários.
Os índices secundários são particionados exatamente como o índice XML primário.
DROP_EXISTING pode descartar um índice secundário e criar outro índice secundário na tabela de usuário.
É possível consultar a exibição de catálogo sys.xml_indexes para recuperar informações do índice XML. A coluna secondary_type_desc na exibição de catálogo sys.xml_indexes fornece o tipo de índice secundário:
SELECT *
FROM sys.xml_indexes;
Os valores retornados na coluna secondary_type_desc podem ser NULL, PATH, VALUE ou PROPERTY. Para o índice XML primário, o valor retornado é NULL.
Exemplo: criar índices XML secundários
O exemplo a seguir ilustra como são criados índices XML secundários. O exemplo também mostra informações sobre os índices XML criados.
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
Você pode consultar a exibição de catálogo sys.xml_indexes para recuperar as informações dos índices XML. A coluna secondary_type_desc fornece o tipo de índice secundário.
SELECT *
FROM sys.xml_indexes;
Também é possível consultar a exibição do catálogo para obter informações de índice.
SELECT *
FROM sys.xml_indexes
WHERE object_id = object_id('T');
É possível adicionar dados de exemplo e revisar as informações do índice 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
Confira também
Comentários
Em breve: Ao longo de 2024, eliminaremos os problemas do GitHub como o mecanismo de comentários para conteúdo e o substituiremos por um novo sistema de comentários. Para obter mais informações, consulte https://aka.ms/ContentUserFeedback.
Enviar e exibir comentários de