CREATE FULLTEXT INDEX (Transact-SQL)

  • Artigo

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do Azure

Cria um índice de texto completo em uma tabela ou em uma exibição indexada de um banco de dados no SQL Server. 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 de Transact-SQL

Sintaxe

CREATE FULLTEXT INDEX ON table_name
   [ ( { column_name
             [ TYPE COLUMN type_column_name ]
             [ LANGUAGE language_term ]
             [ STATISTICAL_SEMANTICS ]
        } [ , ...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 }
 | SEARCH PROPERTY LIST [ = ] property_list_name
 }

Observação

Para exibir a sintaxe do Transact-SQL para o SQL Server 2014 (12.x) e versões anteriores, confira a Documentação das versões anteriores.

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 char, varchar, nchar, nvarchar, text, ntext, image, xml 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, usada para manter o tipo de um documento para um documento 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(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

No momento da indexação, o Mecanismo de Texto Completo usa a abreviação na coluna de tipo de cada linha da tabela para identificar o filtro de pesquisa de texto completo que deve ser usado para o documento no column_name. O filtro carrega o documento como um fluxo binário, remove as informações de formatação e envia o texto do documento para o componente do separador de palavras. Para obter mais informações, veja Configurar e gerenciar filtros para pesquisa.

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 que corresponda ao LCID (identificador de localidade) 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 nas 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 alias na tabela do sistema sys.syslanguages. A cadeia de caracteres precisa ser colocada entre aspas, como em 'language_term'. Quando especificado como um inteiro, language_term é a LCID real que identifica o idioma. Quando especificado como um valor hexadecimal, language_term é 0x seguido pelo valor hexadecimal da LCID. O valor hexadecimal não deve exceder oito dígitos, incluindo zeros à esquerda.

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

Os recursos, como separadores e lematizadores de palavras, devem estar habilitados para o idioma especificado como language_term. Se tais recursos não aceitarem o 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 do Microsoft SQL Server. Para obter mais informações, confira sp_configure (Transact-SQL).

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 consequências de usar o recurso de idioma neutro (0x0). Para obter informações sobre as possíveis soluções e consequências de usar o recurso neutro de idioma (0x0), veja 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 do documento é usada no momento da indexação. Por exemplo, em colunas XML, o atributo xml:lang em documentos XML identifica o idioma. No momento 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 seja especificado como parte de uma consulta de texto completo.

STATISTICAL_SEMANTICS

Aplica-se a: SQL Server (SQL Server 2012 (11.x) e posterior)

Cria a frase-chave adicional e índices de similaridade de documentos que fazem parte da indexação semântica estatística. Para obter mais informações, confira Pesquisa semântica (SQL Server).

KEY INDEX index_name

O nome do índice de chave exclusiva no 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. Esta 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 precisam ser propagadas manualmente chamando a instrução Transact-SQL ALTER FULLTEXT INDEX … START UPDATE POPULATION (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 são propagadas automaticamente conforme os dados são modificados na tabela base (preenchimento automático). Embora sejam propagadas automaticamente, talvez essas alterações não se reflitam 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.

SET 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 seja associada ao índice de texto completo.

  • SISTEMA

    Especifica que a lista de palavras irrelevantes do sistema de texto completo padrão deve ser usada para esse índice de texto completo.

  • stoplist_name

    Especifica o nome da lista de palavras irrelevantes que deve ser associada ao índice de texto completo.

SEARCH PROPERTY LIST [ = ] property_list_name

Aplica-se a: SQL Server (SQL Server 2012 (11.x) e posterior)

Associa uma lista de propriedades de pesquisa ao índice.

  • OFF

    Especifica que nenhuma lista de propriedades será associada ao índice de texto completo.

  • property_list_name

    Especifica o nome da lista de propriedades de pesquisa para associar ao índice de texto completo.

Comentários

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 de 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 a fragmentos e documentos XML ou HTML bem formados que contêm vários idiomas. Para obter mais informações, veja Usar a pesquisa de texto completo com colunas 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.

CREATE FULLTEXT INDEX não pode ser colocada dentro de uma transação de usuário. Essa instrução deve ser executada em sua própria transação implícita.

Para obter mais informações sobre índices de texto completo, veja Criar e gerenciar índices de texto completo.

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

O fato de o índice de texto completo ser populado 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 Result
Não habilitado Não especificado Uma população completa é executada no índice.
Não habilitado Especificado Não ocorre 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 o preenchimento de índices de texto completo, veja Preencher índices 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 de banco de dados fixas 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 padrão fornecida com o SQL Server.

Exemplos

R. Criar 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 AdventureWorks2022. 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.

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. Criar um índice de texto completo em várias colunas da tabela

O exemplo a seguir cria um catálogo de texto completo, production_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á nas colunas ReviewerName, EmailAddress e Comments de Production.ProductReview. 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 um índice de chave exclusiva existente, PK_ProductReview_ProductReviewID. Conforme recomendado, essa chave de índice está em uma coluna de inteiros, ProductReviewID.

CREATE FULLTEXT CATALOG production_catalog;
GO

CREATE FULLTEXT INDEX ON Production.ProductReview (
    ReviewerName LANGUAGE 1033,
    EmailAddress LANGUAGE 1033,
    Comments LANGUAGE 1033
) KEY INDEX PK_ProductReview_ProductReviewID ON production_catalog;
GO

C. Criar um índice de texto completo com uma lista de propriedades de pesquisa sem populá-lo

O índice de texto completo está nas colunas Title, DocumentSummary e Document da tabela Production.Document. 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_Document_DocumentID. Conforme recomendado, essa chave de índice está em uma coluna de inteiros, DocumentID.

O exemplo especifica a lista de palavras irrelevantes do sistema. Também especifica uma lista de propriedades de pesquisa, DocumentPropertyList; para obter um exemplo que crie esta lista de propriedades, veja CREATE SEARCH PROPERTY LIST (Transact-SQL).

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.

CREATE FULLTEXT INDEX ON Production.Document (
    Title LANGUAGE 1033,
    DocumentSummary LANGUAGE 1033,
    Document TYPE COLUMN FileExtension LANGUAGE 1033
) KEY INDEX PK_Document_DocumentID
WITH STOPLIST = SYSTEM,
    SEARCH PROPERTY LIST = DocumentPropertyList,
    CHANGE_TRACKING OFF,
    NO POPULATION;
GO

Posteriormente, fora do horário de pico, o índice é populado:

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

Confira também