CREATE XML INDEX (Transact-SQL)

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.

Observação
Para criar um índice relacional, consulte CREATE INDEX (Transact-SQL). Para obter informações sobre como criar um índice espacial, consulte CREATE SPATIAL INDEX (Transact-SQL).

Convenções de sintaxe 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 ] . | schema_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
}

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 para que um índice XML secundário possa ser criado na coluna.

  • Um índice XML só 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 for criado e ao inserir, excluir ou atualizar valores na coluna xml. Para obter mais informações, consulte Opções SET que afetam os resultados.

  Para obter mais informações, consulte Índices em colunas de tipo de dados XML.

• 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 devem seguir as regras de 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 que 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.

  • ON
    A porcentagem de espaço livre especificada por fillfactor é aplicada à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 de folha preenchidas até a capacidade máxima.

  Observação
  Os valores de fator de preenchimento 0 e 100 são iguais em todos os aspectos.

  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.

  Importante
  A criação de um índice clusterizado com FILLFACTOR inferior a 100 afeta a quantidade de espaço de armazenamento ocupado pelos dados, porque o Mecanismo de Banco de Dados redistribui os dados quando cria o índice clusterizado.

  Para obter mais informações, consulte Fator de preenchimento.

• SORT_IN_TEMPDB = { ON | OFF }
  Especifica se os resultados de classificação temporários devem ser armazenados em tempdb. O padrão é OFF.

  • ON
    Os resultados de classificação intermediários usados para criar o índice são armazenados em tempdb. Isso pode 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 a quantidade de espaço em disco usado durante a criação do índice.

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

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

• 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, senão ocorrerá um erro.

• DROP_EXISTING = { ON | OFF }
  Especifica que o índice XML nomeado preexistente deve ser removido e recriado. O padrão é OFF.

  • ON
    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.

  Observação
  As operações de índice online estão disponíveis somente nas edições Enterprise, Developer e Evaluation do SQL Server.

• ALLOW_ROW_LOCKS = { ON | OFF }
  Especifica se bloqueios de linha são permitidos. O padrão é ON.

  • ON
    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.

  • ON
    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 são usados.

• MAXDOP **=**max_degree_of_parallelism
  Substitui a opção de configuração grau máximo de paralelismo enquanto durar 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 é 64 processadores.

  Importante
  Embora a opção MAXDOP tenha suporte sintaticamente para todos os índices XML, para um índice XML primário, CREATE XML INDEX usa apenas um único processador.

  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 Configurando operações de índice paralelo.

  Observação
  As operações de índice paralelas estão disponíveis somente nas edições Enterprise, Developer e Evaluation do SQL Server.

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, consulte Índices em colunas de tipo de dados XML.

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

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

Exemplos

A. Criando um índice XML primário

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

USE AdventureWorks2008R2;
GO
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 secundário

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

USE AdventureWorks2008R2;
GO
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

Consulte também

Referência

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)

sys.indexes (Transact-SQL)

sys.index_columns (Transact-SQL)

sys.xml_indexes (Transact-SQL)

EVENTDATA (Transact-SQL)

Conceitos

Diretrizes para criação de índice geral

Índices em colunas de tipo de dados XML

Índices em colunas de tipo de dados XML

Outros recursos

Determinando requisitos de espaço em disco de índice

Arquitetura de estruturas de dados de índice e tabela