CREATE FULLTEXT INDEX (Transact-SQL)

Cria um índice de texto completo em uma tabela ou em uma exibição indexada de um banco de dados. Somente um índice de texto completo é permitido por tabela ou exibição indexada, e cada índice de texto completo se aplica a uma única tabela ou exibição indexada.

O índice de texto completo pode conter até 1024 colunas.

Convenções de sintaxe Transact-SQL

Sintaxe

CREATE FULLTEXT INDEX ON table_name
      [ ( { column_name 
             [ TYPE COLUMN type_column_name ]
             [ LANGUAGE language_term ] 
        } [ ,...n] 
            ) ]
    KEY INDEX index_name 
        [ ON <catalog_filegroup_option> ]
        [ WITH [ ( ] <with_option> [ ,...n] [ ) ] ]
[;]

<catalog_filegroup_option>::=
  {
        fulltext_catalog_name 
  | ( fulltext_catalog_name, FILEGROUP filegroup_name )
  | ( FILEGROUP filegroup_name, fulltext_catalog_name )
  | ( FILEGROUP filegroup_name )
  }

<with_option>::=
  {
   CHANGE_TRACKING [ = ] { MANUAL | AUTO | OFF [, NO POPULATION ] } 
  | STOPLIST [ = ] { OFF | SYSTEM | stoplist_name }
  }

Argumentos

• table_name
  É o nome da tabela ou exibição indexada que contém a coluna ou colunas incluídas no índice de texto completo.

• column_name
  É o nome da coluna incluída no índice de texto completo. Somente colunas do tipo char, varchar, nchar, nvarchar, text, ntext, image, xml, varbinary e varbinary(max) podem ser indexadas para pesquisa de texto completo. Para especificar várias colunas, repita a cláusula column_name da seguinte forma:

  CREATE FULLTEXT INDEX ON table_name (column_name1 […], column_name2 […]) …

• TYPE COLUMN type_column_name
  Especifica o nome de uma coluna de tabela, type_column_name, que é usada para manter o tipo de um documento varbinary, varbinary(max) ou image. Essa coluna, conhecida como coluna de tipo, contém uma extensão de arquivo fornecida pelo usuário (.doc, .pdf, .xls e assim por diante). A coluna de tipo deve ser do tipo char, nchar, varchar ou nvarchar.

  Especifique TYPE COLUMN type_column_name somente se column_name especificar uma coluna varbinary, varbinary(max) ou image, na qual os dados são armazenados como dados binários. Caso contrário, o SQL Server retornará um erro.

  Observação

  Na hora da indexação, o Mecanismo de Texto Completo usa a abreviação na coluna de tipo de cada linha da tabela para identificar qual filtro de pesquisa de texto completo usar para o documento no column_name. O filtro carrega o documento como um fluxo binário, remove as informações sobre formatação e envia o texto do documento para o componente do separador de palavras. Para obter mais informações, consulte Filtros da pesquisa de texto completo.

• LANGUAGE language_term
  É o idioma dos dados armazenados em column_name.

  language_term é opcional e pode ser especificado como uma cadeia de caracteres, um inteiro ou um valor hexadecimal correspondente ao LCID (identificador local) de um idioma. Se nenhum valor for especificado, o idioma padrão da instância do SQL Server será usado.

  Se language_term estiver especificado, o idioma que ele representa será usado para indexar dados armazenados em colunas char, nchar, varchar, nvarchar, text e ntext. Esse será o idioma padrão usado na hora da consulta se language_term não estiver especificado como parte de um predicado de texto completo em relação à coluna.

  Quando especificado como uma cadeia de caracteres, language_term corresponde ao valor da coluna de alias na tabela do sistema syslanguages. A cadeia de caracteres deve estar entre aspas simples, como em 'language_term'. Quando especificado como um inteiro, language_term é o LCID real que identifica o idioma. Quando especificado como um valor hexadecimal, language_term é 0x seguido pelo valor hexa do LCID. O valor hexa não deve exceder oito dígitos, inclusive zeros à esquerda.

  Se o valor estiver no formato DBCS (conjunto de caracteres de dois bytes), o SQL Server o converterá em Unicode.

  Recursos, como separadores e lematizadores de palavras, devem se habilitados para o idioma especificado como language_term. Se esses recursos não oferecerem suporte ao idioma especificado, o SQL Server retornará um erro.

  Use o procedimento armazenado sp_configure para acessar informações sobre o idioma de texto completo da instância MicrosoftSQL Server. Para obter mais informações, consulte sp_configure (Transact-SQL) e Opção default full-text language.

  Para colunas não-BLOB e não-XML que contêm dados de texto em vários idiomas ou casos em que o idioma do texto armazenado na coluna é desconhecido, talvez seja necessário usar o recurso de idioma neutro (0x0). Porém, primeiro você deve compreender as possíveis conseqüências de usar o recurso de idioma neutro (0x0). Para obter informações sobre as possíveis soluções e conseqüências do uso do recurso de idioma neutro (0x0), consulte Práticas recomendadas para escolher um idioma ao criar um índice de texto completo.

  Para documentos armazenados em colunas do tipo XML ou BLOB, a codificação de idioma dentro do documento será usada na hora da indexação. Por exemplo, em colunas XML, o atributo xml:lang em documentos XML identificará o idioma. Na hora da consulta, o valor especificado anteriormente em language_term se torna o idioma padrão usado para consultas de texto completo a menos que language_term esteja especificado como parte de uma consulta de texto completo.

• KEY INDEX index_name
  É o nome do índice de chave exclusiva na table_name. O KEY INDEX deve ser uma coluna exclusiva de chave única, não anulável. Selecione o menor índice de chave exclusiva para a chave exclusiva de texto completo. Para obter o melhor desempenho, recomendamos um tipo de dados de inteiro para a chave de texto completo.

• fulltext_catalog_name
  É o catálogo de texto completo usado para o índice de texto completo. O catálogo já deve existir no banco de dados. Essa cláusula é opcional. Se não estiver especificado, um catálogo padrão será usado. Se não existir nenhum catálogo padrão, o SQL Server retornará um erro.

• FILEGROUP filegroup_name
  Cria o índice de texto completo especificado no grupo de arquivos especificado. O grupo de arquivos já deve existir. Se a cláusula FILEGROUP não for especificada, o índice de texto completo será colocado no mesmo grupo de arquivos que a tabela base, na exibição de uma tabela não particionada ou em um grupo de arquivos primário de uma tabela particionada.

• CHANGE_TRACKING [ = ] { MANUAL | AUTO | OFF [ , NO POPULATION ] }
  Especifica se as alterações (atualizações, exclusões ou inserções) feitas nas colunas da tabela que estão cobertas pelo índice de texto completo serão propagadas pelo SQL Server para o índice de texto completo. As alterações de dados por meio de WRITETEXT e UPDATETEXT não são refletidas no índice de texto completo e não são coletadas com o controle de alterações.

  • MANUAL
    Especifica que as alterações controladas devem ser propagadas manualmente chamando-se a instrução ALTER FULLTEXT INDEX … START UPDATE POPULATION Transact-SQL (população manual). É possível usar o SQL Server Agent para chamar essa instrução Transact-SQL periodicamente.

  • AUTO
    Especifica que as alterações controladas serão propagadas automaticamente conforme os dados forem modificados na tabela base (população automática). Embora as alterações sejam propagadas automaticamente, essas alterações podem não ser refletidas imediatamente no índice de texto completo. AUTO é o padrão.

  • OFF [ , NO POPULATION]
    Especifica que o SQL Server não mantém uma lista de alterações nos dados indexados. Quando a opção NO POPULATION não está especificada, o SQL Server popula o índice completamente depois que ele é criado.

    A opção NO POPULATION pode ser usada apenas quando CHANGE_TRACKING está OFF. Quando a opção NO POPULATION está especificada, o SQL Server não popula um índice depois que ele é criado. O índice somente será preenchido depois que o usuário executar o comando ALTER FULLTEXT INDEX com a cláusula START FULL POPULATION ou START INCREMENTAL POPULATION.

• STOPLIST [ = ] { OFF | SYSTEM | stoplist_name }
  Associa uma lista de palavras irrelevantes de texto completo ao índice. O índice não é populado com nenhum token que faça parte da lista de palavras irrelevantes especificada. Se STOPLIST não estiver especificada, o SQL Server associará a lista de palavras irrelevantes de texto completo ao índice.

  • OFF
    Especifica que nenhuma lista de palavras irrelevantes será associada ao índice de texto completo.

  • SYSTEM
    Especifica que a STOPLIST do sistema de texto completo padrão deve ser usada para este índice de texto completo.

  • stoplist_name
    Especifica o nome da lista de palavras irrelevantes (stoplist) a ser associada ao índice de texto completo.

Comentários

Para obter mais informações sobre os índices de texto completo, consulte Configurando catálogos e índices de texto completo para um banco de dados.

Em colunas xml, você pode criar um índice de texto completo que indexa o conteúdo dos elementos XML, mas ignora a marcação XML. Os valores dos atributos são indexados como texto completo a menos que sejam valores numéricos. Marcas de elemento são usadas como limites do token. Há suporte para documentos XML ou HTML bem formados e fragmentos que contêm vários idiomas. Para obter mais informações, consulte Índice de texto completo em uma coluna XML.

Recomendamos que a coluna de chave de índice seja de um tipo de dados inteiro. Isso otimiza o tempo de execução da consulta.

Interações do controle de alterações e do parâmetro NO POPULATION

O fato de o índice de texto completo ser preenchido depende de o controle de alterações estar habilitado e de WITH NO POPULATION ter sido especificado na instrução ALTER FULLTEXT INDEX. A tabela a seguir resume o resultado da interação.

Controle de alterações	WITH NO POPULATION	Resultado
Não habilitado	Não especificado	Uma população completa é executada no índice.
Não habilitado	Especificado	Não ocorrer nenhuma população do índice até que uma instrução ALTER FULLTEXT INDEX...START POPULATION seja emitida.
Habilitado	Especificado	É gerado um erro e o índice não é alterado.
Habilitado	Não especificado	Uma população completa é executada no índice.

Para obter mais informações sobre população dos índices de texto completo, consulte População do índice de texto completo.

Permissões

O usuário deve ter a permissão REFERENCES no catálogo de texto completo e a permissão ALTER na tabela ou exibição indexada ou ser membro da função de servidor fixa sysadmin ou das funções fixas de banco de dados db_owner ou db_ddladmin.

Se a opção SET STOPLIST estiver especificada, o usuário deve ter permissão REFERENCES na lista de palavras irrelevantes especificada. O proprietário da STOPLIST pode conceder essa permissão.

Observação
O público recebe a permissão REFERENCE para a lista de palavras irrelevantes (stoplist) padrão fornecida com o SQL Server.

Exemplos

A. Criando um índice exclusivo, um catálogo de texto completo e um índice de texto completo

O exemplo a seguir cria um índice exclusivo na coluna JobCandidateID da tabela HumanResources.JobCandidate do banco de dados de exemplo AdventureWorks. Em seguida, o exemplo cria um catálogo de texto completo padrão, ft. Finalmente, o exemplo cria um índice de texto completo na coluna Resume, usando o catálogo ft e a lista de palavras irrelevantes (stoplist) do sistema.

USE AdventureWorks;
GO
CREATE UNIQUE INDEX ui_ukJobCand ON HumanResources.JobCandidate(JobCandidateID);
CREATE FULLTEXT CATALOG ft AS DEFAULT;
CREATE FULLTEXT INDEX ON HumanResources.JobCandidate(Resume) 
   KEY INDEX ui_ukJobCand 
   WITH STOPLIST = SYSTEM;
GO

B. Criando um índice de texto completo em várias colunas da tabela

O exemplo a seguir cria um índice de texto completo nas colunas ReviewerName, EmailAddress e Comments da tabela Production.ProductReview do banco de dados de exemplo AdventureWorks. Para cada coluna, o exemplo especifica o LCID de inglês, 1033 que é o idioma dos dados nas colunas. Esse índice de texto completo usa o catálogo de texto completo padrão e um índice de chave exclusiva existente, PK_ProductReview_ProductReviewID. Conforme recomendado, essa chave de índice está em uma coluna de inteiros, ProductReviewID.

USE AdventureWorks;
GO
CREATE FULLTEXT INDEX ON Production.ProductReview
( 
  ReviewerName
     Language 1033,
  EmailAddress
     Language 1033,
  Comments 
     Language 1033     
 ) 
KEY INDEX PK_ProductReview_ProductReviewID ; 
GO

C. Criando um índice de texto completo sem populá-lo

O exemplo a seguir cria um catálogo de texto completo, documents_catalog, no banco de dados de exemplo AdventureWorks. Em seguida, o exemplo cria um índice de texto completo que usa esse novo catálogo. O índice de texto completo está na coluna Document da tabela Production.Document. O exemplo especifica o LCID de inglês, 1033, que é o idioma dos dados na coluna. Esse índice de texto completo usa o catálogo de texto completo padrão e um índice de chave exclusiva existente, PK_Document_DocumentID. Conforme recomendado, essa chave de índice está em uma coluna de inteiros, DocumentID. O exemplo especifica que o controle de alterações está desativado sem nenhuma população. Posteriormente, fora do horário de pico, o exemplo usa uma instrução ALTER FULLTEXT INDEX para iniciar uma população completa no novo índice e habilitar o controle de alterações automático.

USE AdventureWorks;
GO
CREATE FULLTEXT CATALOG documents_catalog;
GO
CREATE FULLTEXT INDEX ON Production.Document
( Document 
    TYPE COLUMN FileExtension
    Language 1033 )
   KEY INDEX PK_Document_DocumentID
      ON documents_catalog
      WITH CHANGE_TRACKING OFF, NO POPULATION;
   GO

-- Posteriormente, fora do horário de pico, preencha o índice:

ALTER FULLTEXT INDEX ON Production.Document SET CHANGE_TRACKING AUTO;
GO

Histórico de alterações

Conteúdo atualizado
Adicionados mais exemplos à seção "Exemplos".
Adicionada a seção "Interações do controle de alterações e do parâmetro NO POPULATION".

Consulte também

Tarefas

Como iniciar o Assistente para Indexação de Texto Completo (SQL Server Management Studio)

Referência

ALTER FULLTEXT INDEX (Transact-SQL)

DROP FULLTEXT INDEX (Transact-SQL)

GRANT (Transact-SQL)

sys.fulltext_indexes (Transact-SQL)

Conceitos

Pesquisa de texto completo (SQL Server)

Outros recursos

Configurando catálogos e índices de texto completo para um banco de dados