Nota
O acesso a esta página requer autorização. Pode tentar iniciar sessão ou alterar os diretórios.
O acesso a esta página requer autorização. Pode tentar alterar os diretórios.
Aplica-se a:
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceBase de dados SQL no Microsoft Fabric
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 CREATE INDEX (Transact-SQL)Transact-SQL DDL. Nem todas as opções disponíveis para índices não XML são suportadas em índices XML.
Observe o seguinte ao criar um índice XML:
Para criar um índice XML primário, 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 clusterizado garante que, se a tabela base for particionada, o índice XML primário poderá ser particionado usando o mesmo esquema de particionamento e função de particionamento.
Se existir um índice XML, a chave primária clusterizada da tabela não poderá ser modificada. Você terá de remover todos os índices XML na tabela antes de modificar a chave primária.
Um índice primário XML pode ser criado numa única coluna do tipo xml . Não é possível criar nenhum outro tipo de índice com a coluna xml type como uma coluna de chave. No entanto, você pode incluir a coluna xml type em um índice não XML. Cada coluna de tipo xml numa tabela pode ter o seu próprio índice XML primário. No entanto, apenas um índice XML primário por coluna do tipo xml é permitido.
Os índices XML existem no mesmo namespace que os índices não-XML. Portanto, você não pode ter um índice XML e um índice não XML na mesma tabela com o mesmo nome.
As opções IGNORE_DUP_KEY e ONLINE estão sempre desligadas para índices XML. Você pode especificar essas opções com um valor OFF.
As informações de particionamento ou grupo de arquivos da tabela do usuário 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 descartar um índice XML secundário para criar um novo índice XML primário ou vice-versa.
Os nomes de índice XML primários têm as mesmas restrições que os nomes de exibição.
Não é possível criar um índice XML em uma coluna xml type em uma vista, em um variável valorada de tabela com colunas do tipo xml, ou em variáveis de tipo xml.
Para alterar uma coluna xml type de untyped para typed XML, ou vice-versa, usando a opção ALTER TABLE ALTER COLUMN, nenhum índice XML na coluna deve existir. Se existir, ele deve ser descartado antes que a alteração de tipo de coluna seja tentada.
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 xml métodos de tipo de dados, a mesma opção deve ser definida na conexão. Se não estiver, os métodos do tipo de dados XML falharão.
Observação
Informações sobre um índice XML podem ser encontradas em exibições de catálogo. No entanto, sp_helpindex não é suportado. Os exemplos fornecidos posteriormente neste tópico mostram como consultar as exibições de catálogo para localizar informações de índice XML.
Ao criar ou recriar um índice XML primário em uma coluna de tipo de dados xml que contenha valores dos tipos de esquema XML xs:date ou xs:dateTime (ou quaisquer subtipos desses tipos) que tenham um ano inferior a 1, a criação do índice falhará no SQL Server 2008 (10.0.x) e versões posteriores. SQL Server 2005 (9.x) permitiu 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 tipado.
Exemplo: Criar um índice XML primário
A tabela T (pk INT PRIMARY KEY, xCol XML) com uma coluna XML sem tipo é usada na maioria dos exemplos. Este exemplo pode ser estendido para XML digitado de forma simples. 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 CREATE INDEX (Transact-SQL)Transact-SQL DDL para criar índices XML secundários e especificar o tipo de índice XML secundário desejado.
Observe o seguinte ao criar índices XML secundários:
Todas as opções de indexação que se aplicam a um índice não clusterizado, exceto IGNORE_DUP_KEY e ONLINE, são permitidas em índices XML secundários. As duas opções devem sempre ser definidas como OFF para índices XML secundários.
Os índices secundários são particionados assim como o índice XML primário.
DROP_EXISTING pode eliminar um índice secundário e criar outro na tabela do usuário.
Você pode consultar a vista de catálogo sys.xml_indexes para recuperar informações de índice XML. A coluna secondary_type_desc na vista 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 índices XML secundários são criados. O exemplo também mostra informações sobre os índices XML que você criou.
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 informações de índices XML. A coluna secondary_type_desc fornece o tipo de índice secundário.
SELECT *
FROM sys.xml_indexes;
Você também pode consultar a exibição de catálogo para obter informações de índice.
SELECT *
FROM sys.xml_indexes
WHERE object_id = object_id('T');
Você pode adicionar dados de exemplo e, em seguida, revisar as informações de í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