CREATE XML INDEX (Transact-SQL)

Aplica-se a: SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure

Cria um índice XML em uma tabela especificada. Um índice pode ser criado antes que haja dados na tabela. Os índices XML podem ser criados em tabelas em outro banco de dados especificando um nome de banco de dados qualificado.

Convenções de sintaxe de Transact-SQL

Sintaxe

--Create XML Index   
CREATE [ PRIMARY ] XML INDEX index_name
    ON <object> ( xml_column_name )
    [ USING XML INDEX xml_index_name
        [ FOR { VALUE | PATH | PROPERTY } ] ]
    [ WITH ( <xml_index_option> [ ,...n ] ) ]
[ ; ]

<object> ::=
{ database_name.schema_name.table_name | schema_name.table_name | table_name }

<xml_index_option> ::=
{
    PAD_INDEX  = { ON | OFF }
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB = { ON | OFF }
  | IGNORE_DUP_KEY = OFF
  | DROP_EXISTING = { ON | OFF }
  | ONLINE = OFF
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | XML_COMPRESSION = { ON | OFF }
}

Argumentos

[PRIMARY] XML

Cria um índice XML na coluna xml especificada. Quando PRIMARY é especificado, um índice clusterizado é criado com a chave clusterizada formada pela chave de cluster da tabela do usuário e um identificador de nó XML. Cada tabela pode ter até 249 índices XML. Observe o seguinte quando for criar um índice XML:

• Um índice clusterizado deve existir na chave primária da tabela do usuário.

• A chave de cluster da tabela do usuário é limitada a 15 colunas.

• Cada coluna xml em uma tabela pode ter um índice XML primário e vários índices XML secundários.

• Deve existir um índice XML primário em uma coluna xml antes que um índice XML secundário possa ser criado na coluna.

• Um índice XML apenas pode ser criado em uma única coluna xml. Não é possível criar um índice XML em uma coluna não xml nem criar um índice relacional em uma coluna xml.

• Não é possível criar um índice XML, seja primário ou secundário, em uma coluna xml em uma exibição, em uma variável com valor de tabela com colunas xml ou variáveis do tipo xml.

• Não é possível criar um índice XML primário em uma coluna xml computada.

• As configurações da opção SET devem ser iguais às necessárias para exibições indexadas e índices de coluna computada. Especificamente, a opção ARITHABORT deve ser definida como ON quando um índice XML é criado e ao inserir, excluir ou atualizar valores na coluna xml.

Para obter mais informações, confira Índices XML (SQL Server).

index_name

O nome do índice. Os nomes de índice devem ser exclusivos em uma tabela, mas não precisam ser exclusivos em um banco de dados. Os nomes de índice precisam seguir as regras para identificadores.

Nomes de índice XML primários não podem começar com os seguintes caracteres: #, ##, @ ou @@.

xml_column_name

A coluna xml na qual o índice se baseia. Somente uma coluna xml pode ser especificada em uma única definição de índice XML; no entanto, vários índices XML secundários podem ser criados em uma coluna xml.

USING XML INDEX xml_index_name

Especifica o índice XML primário a ser usado na criação de um índice XML secundário.

FOR { VALUE | PATH | PROPERTY }

Especifica o tipo de índice XML secundário.

VALUE
Cria um índice XML secundário em colunas nas quais as colunas de chave são (valor do nó e caminho) do índice XML primário.

PATH
Cria um índice XML secundário em colunas criadas em valores de caminho e de nó no índice XML primário. No índice secundário PATH, os valores de caminho e nó são colunas de chave que permitem buscas eficientes ao pesquisar caminhos.

PROPERTY
Cria um índice XML secundário em colunas (PK, caminho e valor do nó) do índice XML primário em que PK é a chave primária da tabela base.

<object>::=

O objeto totalmente qualificado ou não totalmente qualificado a ser indexado.

database_name
O nome do banco de dados.

schema_name
O nome do esquema ao qual a tabela pertence.

table_name
O nome da tabela a ser indexada.

<xml_index_option> ::=

Especifica as opções a serem usadas ao criar o índice.

PAD_INDEX = { ON | OFF }

Especifica o preenchimento do índice. O padrão é OFF.

ATIVADO
O percentual de espaço livre especificado por fillfactor é aplicado às páginas de nível intermediário do índice.

OFF ou fillfactor não está especificado
As páginas de nível intermediário são preenchidas até próximo de sua capacidade, deixando espaço suficiente para pelo menos uma linha do tamanho máximo que o índice pode ter, considerando o conjunto de chaves em páginas intermediárias.

A opção PAD_INDEX só é útil quando FILLFACTOR é especificado, porque PAD_INDEX usa a porcentagem especificada por FILLFACTOR. Se a porcentagem especificada para FILLFACTOR não for grande o suficiente para permitir uma linha, o Mecanismo de Banco de Dados substituirá a porcentagem internamente para permitir o valor mínimo. O número de linhas em uma página de índice intermediária nunca é menor do que dois, independentemente de quão baixo seja o valor de fillfactor.

FILLFACTOR = fillfactor

Especifica uma porcentagem que indica quanto o Mecanismo de Banco de Dados deve preencher o nível folha de cada página de índice durante a criação ou recriação do índice. fillfactor deve ser um valor inteiro de 1 a 100. O padrão é 0. Se fillfactor for 100 ou 0, o Mecanismo de Banco de Dados criará índices com páginas folha preenchidas até a capacidade máxima.

A configuração FILLFACTOR se aplica somente quando o índice é criado ou recriado. O Mecanismo de Banco de Dados não mantém dinamicamente a porcentagem especificada de espaço vazio nas páginas. Para exibir a configuração do fator de preenchimento, use a exibição do catálogo sys.indexes.

Para obter mais informações, veja Especificar fator de preenchimento para um índice.

SORT_IN_TEMPDB = { ON | OFF }

Especifica se os resultados de classificação temporários devem ser armazenados no tempdb. O padrão é OFF.

ATIVADO
Os resultados de classificação intermediários usados para criar o índice são armazenados no tempdb. Isso poderá reduzir o tempo necessário para criar um índice se tempdb estiver em um conjunto de discos diferente do banco de dados de usuário. Entretanto, isso aumenta o espaço em disco usado durante a criação do índice.

OFF
Os resultados intermediários de classificação são armazenados no mesmo banco de dados que o índice.

Além do espaço necessário no banco de dados de usuário para criar o índice, tempdb deve ter aproximadamente a mesma quantidade de espaço adicional para conter os resultados de classificação intermediários. Para obter mais informações, consulte a Opção SORT_IN_TEMPDB para índices.

IGNORE_DUP_KEY = OFF

Não tem nenhum efeito para índices XML porque o tipo de índice nunca é exclusivo. Não defina essa opção como ON; caso contrário, um erro será gerado.

DROP_EXISTING = { ON | OFF }

Especifica que o índice XML nomeado preexistente deve ser removido e recriado. O padrão é OFF.

ATIVADO
O índice existente é removido e recriado. O nome de índice especificado deve ser igual ao índice existente atualmente; no entanto, a definição de índice pode ser modificada. Por exemplo, você pode especificar colunas, ordens de classificação, esquemas de partição ou opções de índice diferentes.

OFF
Um erro será exibido se o nome de índice especificado já existir.

O tipo de índice não pode ser alterado com DROP_EXISTING. Além disso, um índice XML primário não pode ser redefinido como um índice XML secundário ou vice-versa.

ONLINE = OFF

Especifica que as tabelas subjacentes e os índices associados não estão disponíveis para consultas e modificação de dados durante a operação de índice. Nesta versão do SQL Server, não há suporte para criações de índice online para índices XML. Se essa opção for definida como ON para um índice XML, um erro será gerado. Omita a opção ONLINE ou defina ONLINE como OFF.

Uma operação de índice offline que cria, recria ou remove um índice XML adquire um bloqueio de Modificação de esquema (Sch-M) na tabela. Isso evita o acesso de todos os usuários à tabela subjacente enquanto durar a operação.

ALLOW_ROW_LOCKS = { ON | OFF }

Especifica se bloqueios de linha são permitidos. O padrão é ON.

ATIVADO
Bloqueios de linha são permitidos ao acessar o índice. O Mecanismo de Banco de Dados determina quando os bloqueios de linha são usados.

OFF
Bloqueios de linha não são usados.

ALLOW_PAGE_LOCKS = { ON | OFF }

Especifica se bloqueios de página são permitidos. O padrão é ON.

ATIVADO
Bloqueios de página são permitidos ao acessar o índice. O Mecanismo de Banco de Dados determina quando os bloqueios de página são usados.

OFF
Bloqueios de página não serão usados.

MAXDOP = max_degree_of_parallelism

Substitui a opção de configuração Configurar a opção max degree of parallelism de configuração do servidor durante a operação de índice. Use MAXDOP para limitar o número de processadores usados em uma execução de plano paralelo. O máximo é de 64 processadores.

max_degree_of_parallelism pode ser:

1
Suprime a geração de plano paralelo.

>1
Restringe o número máximo de processadores usados em uma operação de índice paralela ao número especificado, ou menos, com base na carga de trabalho atual do sistema.

0 (padrão)
Usa o número real de processadores, ou menos, com base na carga de trabalho atual do sistema.

Para obter mais informações, consulte Configurar operações de índice paralelo.

Comentários

As colunas computadas derivadas de tipos de dados xml podem ser indexadas como uma coluna de chave ou como uma coluna não chave incluída, desde que o tipo de dados da coluna computada seja permitido como uma coluna de chave de índice ou coluna não chave. Não é possível criar um índice XML primário em uma coluna xml computada.

Para exibir informações sobre índices XML, use a exibição do catálogo sys.xml_indexes.

Para obter mais informações sobre índices XML, confira Índices XML (SQL Server).

Compactação de XML

Aplicável a: SQL Server 2022 (16.x) e versões posteriores, Banco de Dados SQL do Azure e Instância Gerenciada de SQL do Azure.

• Índices XML não herdam a propriedade de compactação da tabela. Para compactar índices, é preciso habilitar explicitamente a compactação XML em índices XML.
• Í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 de índices é definida como OFF quando o índice é criado.

Comentários adicionais sobre criação de índices

Para obter mais informações sobre a criação de índices, confira a seção “Comentários” em CREATE INDEX (Transact-SQL).

Exemplos

Os exemplos de código do Transact-SQL deste artigo usa o banco de dados de exemplo AdventureWorks2022 ou AdventureWorksDW2022, que pode ser baixado da home page Microsoft SQL Server Samples and Community Projects.

R. Criando um índice XML primário

O exemplo a seguir cria um índice XML primário na coluna CatalogDescription da tabela Production.ProductModel.

IF EXISTS (SELECT * FROM sys.indexes
            WHERE name = N'PXML_ProductModel_CatalogDescription')
    DROP INDEX PXML_ProductModel_CatalogDescription
        ON Production.ProductModel;  
GO  
CREATE PRIMARY XML INDEX PXML_ProductModel_CatalogDescription
    ON Production.ProductModel (CatalogDescription);  
GO

B. Criando um índice XML primário com compactação XML

Aplicável a: SQL Server 2022 (16.x) e versões posteriores, Banco de Dados SQL do Azure e Instância Gerenciada de SQL do Azure.

O exemplo a seguir cria um índice XML primário na coluna CatalogDescription da tabela Production.ProductModel.

IF EXISTS (SELECT * FROM sys.indexes
            WHERE name = N'PXML_ProductModel_CatalogDescription')
    DROP INDEX PXML_ProductModel_CatalogDescription
        ON Production.ProductModel;  
GO  
CREATE PRIMARY XML INDEX PXML_ProductModel_CatalogDescription
    ON Production.ProductModel (CatalogDescription)
    WITH (XML_COMPRESSION = ON);  
GO

C. Criando um índice XML secundário

O exemplo a seguir cria um índice XML secundário na coluna CatalogDescription da tabela Production.ProductModel.

IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IXML_ProductModel_CatalogDescription_Path')
    DROP INDEX IXML_ProductModel_CatalogDescription_Path
        ON Production.ProductModel;  
GO  
CREATE XML INDEX IXML_ProductModel_CatalogDescription_Path
    ON Production.ProductModel (CatalogDescription)
    USING XML INDEX PXML_ProductModel_CatalogDescription FOR PATH ;
GO

D. Criando um índice XML secundário com compactação XML

Aplicável a: SQL Server 2022 (16.x) e versões posteriores, Banco de Dados SQL do Azure e Instância Gerenciada de SQL do Azure.

O exemplo a seguir cria um índice XML secundário na coluna CatalogDescription da tabela Production.ProductModel.

IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IXML_ProductModel_CatalogDescription_Path')
    DROP INDEX IXML_ProductModel_CatalogDescription_Path
        ON Production.ProductModel;  
GO  
CREATE XML INDEX IXML_ProductModel_CatalogDescription_Path
    ON Production.ProductModel (CatalogDescription)
    USING XML INDEX PXML_ProductModel_CatalogDescription FOR PATH
    WITH (XML_COMPRESSION = ON);
GO

Confira também

• ALTER INDEX (Transact-SQL)
• CREATE INDEX (Transact-SQL)
• CREATE PARTITION FUNCTION (Transact-SQL)
• CREATE PARTITION SCHEME (Transact-SQL)
• CREATE SPATIAL INDEX (Transact-SQL)
• CREATE STATISTICS (Transact-SQL)
• CREATE TABLE (Transact-SQL)
• Tipos de dados (Transact-SQL)
• DBCC SHOW_STATISTICS (Transact-SQL)
• DROP INDEX (Transact-SQL)
• Índices XML (SQL Server)
• sys.indexes (Transact-SQL)
• sys.index_columns (Transact-SQL)
• sys.xml_indexes (Transact-SQL)
• EVENTDATA (Transact-SQL)
• Índices XML (SQL Server)