Criar e gerenciar índices de texto completo

Artigo
09/03/2023

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

Este artigo descreve como criar, popular e gerenciar índices de texto completo no SQL Server.

Criar um catálogo de texto completo

Antes de criar um índice de texto completo, é necessário ter um catálogo de texto completo. O catálogo é um contêiner virtual de um ou mais índices de texto completo. Para obter mais informações, consulte Criar e gerenciar catálogos de texto completo.

Criar, alterar ou descartar um índice de texto completo

Popular um índice de texto completo

O processo de criar e manter um índice de texto completo é chamado de população (também conhecido como rastreamento). Há três tipos de população de índice de texto completo:

População completa
População com base em controle de alterações
População incremental com base em um carimbo de data/hora.

Para obter mais informações, veja Popular índices de texto completo.

Exibir as propriedades de um índice de texto completo

Exibir as propriedades de um índice de texto completo com o Transact-SQL

Exibição de catálogo ou de gerenciamento dinâmico	Descrição
sys.fulltext_index_catalog_usages (Transact-SQL)	Retorna uma linha para cada catálogo de texto completo para referência de índice de texto completo.
sys.fulltext_index_columns (Transact-SQL)	Contém uma linha para cada coluna que faz parte de um índice de texto completo.
sys.fulltext_index_fragments (Transact-SQL)	Um índice de texto completo usa tabelas internas, chamadas de fragmentos de índice de texto completo, para armazenar os dados de índices invertidos. Esta exibição pode ser usada para consultar os metadados sobre estes fragmentos. Esta exibição contém uma linha para cada fragmento de índice de texto completo em toda tabela que contém um índice de texto completo.
sys.fulltext_indexes (Transact-SQL)	Contém uma linha por índice de texto completo de um objeto tabular.
sys.dm_fts_index_keywords (Transact-SQL)	Retorna informações sobre o conteúdo de um índice de texto completo da tabela especificada.
sys.dm_fts_index_keywords_by_document (Transact-SQL)	Retorna informações sobre o conteúdo no nível de documento de um índice de texto completo da tabela especificada. Uma determinada palavra-chave pode aparecer em vários documentos.
sys.dm_fts_index_population (Transact-SQL)	Retorna informações sobre as populações de índice de texto completo que estão em andamento.

Exibir as propriedades de um índice de texto completo com o SQL Server Management Studio

Observação

Para exibir propriedades de índices de texto completo para bancos de dados SQL do Azure, use Transact-SQL.

No SQL Server Management Studio, no Pesquisador de Objetos, expanda o servidor.
Expanda Bancos de Dadose expanda o banco de dados que contém o índice de texto completo.
Expanda Tabelas.
Clique com o botão direito do mouse na tabela em que o índice de texto completo está definido, escolha Índice de texto completo e, no menu de contexto Índice de texto completo, escolha Propriedades. Este procedimento abre a caixa de diálogo Propriedades do Índice de Texto Completo .

No painel Selecionar uma página , você pode selecionar qualquer uma das seguintes páginas:

Página	Descrição
Geral	Exibe as propriedades básicas do índice de texto completo. Isso inclui várias propriedades modificáveis e muitas propriedades inalteráveis, como nome do banco de dados, nome da tabela e nome da coluna de chave de texto completo. As propriedades modificáveis são: Lista de palavras irrelevantes de índice de texto completo Indexação de texto completo habilitada Controle de Alterações Lista de propriedades de pesquisa
Colunas	Exibe as colunas da tabela que estão disponíveis para indexação de texto completo. A(s) coluna(s) selecionada(s) tem(têm) índice de texto completo. Você pode selecionar tantas colunas disponíveis quantas desejar para incluí-las no índice de texto completo. Para obter mais informações, consulte Popular índices de texto completo.
Agendas	Use esta página para criar ou gerenciar agendas para executar um trabalho do SQL Server Agent que inicia uma população incremental de tabela para as populações de índice de texto completo. Para obter mais informações, consulte Popular índices de texto completo. Observação: depois que você sair da caixa de diálogo Propriedades do Índice de Texto Completo , qualquer agenda recém-criada será associada a um trabalho do SQL Server Agent (Iniciar População Incremental da Tabela em database_name.table_name).

Escolha OK para salvar as alterações e sair da caixa de diálogo Propriedades do índice de texto completo.

Exibir as propriedades de colunas e tabelas indexadas

Muitas funções Transact-SQL, como OBJECTPROPERTYEX, podem ser usadas para obter o valor de diversas propriedades de indexação de texto completo. Essas informações são úteis para administrar e solucionar problemas de pesquisa de texto completo.

A tabela a seguir lista as propriedades de texto completo relacionadas a colunas e tabelas indexadas e suas funções Transact-SQL relacionadas.

Propriedade	Descrição	Função
FullTextTypeColumn	TYPE COLUMN na tabela que armazena as informações de tipo de documento da coluna.	COLUMNPROPERTY
IsFulltextIndexed	Se uma coluna foi habilitada para indexação de texto completo.	COLUMNPROPERTY
IsFulltextKey	Se o índice é a chave de texto completo de uma tabela.	INDEXPROPERTY
TableFulltextBackgroundUpdateIndexOn	Se uma tabela tem indexação de atualização de texto completo em segundo plano.	OBJECTPROPERTYEX
TableFulltextCatalogId	ID do catálogo de texto completo no qual residem os dados de índice de texto completo da tabela.	OBJECTPROPERTYEX
TableFulltextChangeTrackingOn	Se o controle de alterações de texto completo está habilitado em uma tabela.	OBJECTPROPERTYEX
TableFulltextDocsProcessed	Número de linhas processadas desde o início da indexação de texto completo.	OBJECTPROPERTYEX
TableFulltextFailCount	Número de linhas que a pesquisa de texto completo não indexou.	OBJECTPROPERTYEX
TableFulltextItemCount	Número de linhas que foram indexadas com texto completo com êxito.	OBJECTPROPERTYEX
TableFulltextKeyColumn	A ID de coluna da coluna de chave exclusiva de texto completo.	OBJECTPROPERTYEX
TableFullTextMergeStatus	Se uma tabela que tem um índice de texto completo está sendo mesclada.	OBJECTPROPERTYEX
TableFulltextPendingChanges	Número de entradas de controle de alterações pendentes a serem processadas.	OBJECTPROPERTYEX
TableFulltextPopulateStatus	Status de população de uma tabela de texto completo.	OBJECTPROPERTYEX
TableHasActiveFulltextIndex	Se uma tabela tem um índice de texto completo ativo.	OBJECTPROPERTYEX

Obtenha informações sobre a coluna de chave de texto completo

Normalmente, o resultado de funções com valor de conjunto de linhas CONTAINSTABLE ou FREETEXTTABLE precisam ser unidas à tabela base. Nesses casos, você precisa saber o nome da coluna de chave exclusiva. Você pode perguntar se um dado índice exclusivo é usado como chave de texto completo e pode obter o identificador da coluna de chave de texto completo.

Determinar se um dado índice exclusivo é usado como a coluna de chave de texto completo

Use uma instrução SELECT para chamar a função INDEXPROPERTY. Na chamada de função, use a função OBJECT_ID para converter o nome da tabela (table_name) na ID de tabela, especifique o nome de um índice exclusivo para a tabela e especifique a propriedade de índice IsFulltextKey desta maneira:

SELECT INDEXPROPERTY(OBJECT_ID('table_name'), 'index_name',  'IsFulltextKey');

Esta instrução retornará 1, se o índice for usado para impor a exclusividade da coluna de chave de texto completo, e 0 ,se o índice não for usado para realizar essa imposição.

Exemplo

O exemplo a seguir pergunta se o índice PK_Document_DocumentNode é usado para impor a exclusividade da coluna de chave de texto completo, da seguinte maneira:

USE AdventureWorks2022;
GO
SELECT INDEXPROPERTY(OBJECT_ID('Production.Document'), 'PK_Document_DocumentNode',  'IsFulltextKey');

Este exemplo retornará 1 se o índice PK_Document_DocumentNode for usado para impor a exclusividade da coluna de chave de texto completo. Caso contrário, retornará 0 ou NULL. NULL implica que você está usando um nome de índice inválido, que o nome de índice não corresponde à tabela, que a tabela não existe etc.

Localizar o identificador da coluna de chave de texto completo

Cada tabela habilitada para texto completo tem uma coluna que é usada para impor linhas exclusivas da tabela (a coluna de chave exclusiva). A propriedade TableFulltextKeyColumn , obtida da função OBJECTPROPERTYEX, contém a ID de coluna da coluna de chave exclusiva.

Para obter esse identificador, você pode usar uma instrução SELECT para chamar a função OBJECTPROPERTYEX. Use a função OBJECT_ID para converter o nome da tabela (table_name) na ID de tabela e especifique a propriedade TableFulltextKeyColumn da seguinte maneira:

SELECT OBJECTPROPERTYEX(OBJECT_ID('table_name'), 'TableFulltextKeyColumn' ) AS 'Column Identifier';

Exemplos

O próximo exemplo retorna o identificador da coluna de chave de texto completo ou NULL. NULL implica que você está usando um nome de índice inválido, que o nome de índice não corresponde à tabela, que a tabela não existe etc.

USE AdventureWorks2022;
GO
SELECT OBJECTPROPERTYEX(OBJECT_ID('Production.Document'), 'TableFulltextKeyColumn');
GO

O exemplo a seguir mostra como usar o identificador da coluna de chave exclusiva para obter o nome da coluna.

USE AdventureWorks2022;
GO

DECLARE @key_column SYSNAME

SET @key_column = COL_NAME(OBJECT_ID('Production.Document'),
   OBJECTPROPERTYEX(OBJECT_ID('Production.Document'), 'TableFulltextKeyColumn'));

SELECT @key_column AS 'Unique Key Column';
GO

Este exemplo retorna um conjunto de resultados chamado Unique Key Column, que exibe uma única linha contendo o nome da coluna de chave exclusiva da tabela Document, DocumentNode. Se a consulta continha um nome de índice inválido, se o nome de índice não correspondia à tabela, se a tabela não existia etc., é retornado NULL.

Indexar colunas varbinary(max) e XML

Se uma coluna varbinary(max), varbinaryou xml tiver um índice de texto completo, ela poderá ser consultada usando os predicados (CONTAINS e FREETEXT) e as funções (CONTAINSTABLE e FREETEXTTABLE) de texto completo, como qualquer outra coluna indexada de texto completo.

Indexar dados varbinary(max) ou varbinary

Uma única coluna varbinary (max) ou varbinary pode armazenar vários tipos de documentos. O SQL Server dá suporte a qualquer tipo de documento para o qual um filtro está instalado e disponível no sistema operacional. O tipo de cada documento é identificado pela extensão de arquivo do documento. Por exemplo, no caso de uma extensão de arquivo .doc, a pesquisa de texto completo usa o filtro que dá suporte a documentos do Microsoft Word. Para obter uma lista dos tipos de documento disponíveis, veja a exibição de catálogo sys.fulltext_document_types .

O mecanismo de texto completo pode usar os filtros existentes instalados no sistema operacional. Para que você possa usar filtros do sistema operacional, separadores de palavras e lematizadores, carregue-os na instância do servidor da seguinte maneira:

EXEC sp_fulltext_service @action = 'load_os_resources', @value = 1;

Para criar um índice de texto completo em uma coluna varbinary(max) , o Mecanismo de Texto Completo precisa de acesso às extensões de arquivo dos documentos na coluna varbinary(max) . Esta informação deve ser armazenada em uma coluna de tabela, chamada de coluna de tipo, que deve estar associada à coluna varbinary(max) no índice de texto completo. Ao indexar um documento, o Mecanismo de Texto Completo utiliza a extensão de arquivo na coluna de tipo para identificar o filtro a ser usado.

Indexar dados XML

Uma coluna de tipo de dados xml armazena apenas fragmentos e documentos XML, e somente o filtro XML é usado para os documentos. Portanto, uma coluna de tipo é desnecessária. Em colunas xml , o índice de texto completo 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 sobre como indexar e fazer consultas em uma coluna xml, consulte Usar a pesquisa de texto completo com colunas XML.

Desabilitar ou reabilitar indexação de texto completo para uma tabela

No SQL Server, todos os bancos de dados criados pelo usuário são habilitados para texto completo por padrão. Além disso, uma tabela individual está automaticamente habilitada para indexação de texto completo desde que o índice de texto completo seja criado nela e uma coluna seja adicionada ao índice. Uma tabela está automaticamente desabilitada para indexação de texto completo quando a última coluna é descartada de seu índice de texto completo.

Em uma tabela que tenha um índice de texto completo, é possível desabilitar manualmente ou desabilitar de novo uma tabela para indexação de texto completo usando o SQL Server Management Studio.

Expanda o grupo do servidor, expanda Bancos de Dadose expanda o banco de dados que contém a tabela desejada para habilitação da indexação de texto completo.
Expanda Tabelase clique com o botão direito do mouse na tabela que você quer desabilitar ou habilitar novamente para indexação de texto completo.
Selecione Índice de texto completo e depois Desabilitar índice de texto completo ou Habilitar índice de texto completo.

Remover um índice de texto completo de uma tabela

No Pesquisador de Objetos, clique com o botão direito do mouse na tabela com o índice de texto completo a ser excluído.
Selecione Excluir Índice de Texto Completo.
Quando solicitado, escolha OK para confirmar que você deseja excluir o índice de texto completo.

Partilhar via