Iniciar a pesquisa de texto completo

Artigo
06/13/2017

Os bancos de dados em SQL Server são habilitados por texto completo por padrão. No entanto, para usar um índice de texto completo em uma tabela, você deve configurar o recurso de indexação de texto completo nas colunas das tabelas que deseja acessar usando o Mecanismo de Texto Completo.

Configurando um banco de dados para pesquisa de texto completo

Em qualquer cenário, um administrador de banco de dados executa as seguintes etapas básicas para configurar as colunas de tabela de um banco de dados para pesquisa de texto completo:

Criar um catálogo de texto completo.
Em cada tabela que você deseja pesquisar, crie um índice de texto completo da seguinte maneira:
1. Identifique cada coluna de texto a ser incluída no índice de texto completo.
2. Se uma determinada coluna contiver documentos armazenados como dados binários (varbinary(max)ou image dados), você deverá especificar uma coluna de tabela (a coluna de tipo) que identifica o tipo de cada documento na coluna que está sendo indexada.
3. Especifique o idioma que a pesquisa de texto completo deverá usar nos documentos da coluna.
4. Escolha o mecanismo de controle de alterações que você deseja usar no índice de texto completo para controlar alterações na tabela base e em suas colunas.

A pesquisa de texto completo dá suporte a vários idiomas através do uso dos seguintes componentes linguísticos: separadores de palavras e lematizadores, listas de palavras irrelevantes (stoplists) (também conhecidas como palavras de ruído) e arquivos de dicionário de sinônimos. Os arquivos de dicionário de sinônimos e, em alguns casos, as listas de palavras irrelevantes exigem configuração pelo administrador de banco de dados. Um dado arquivo de dicionário de sinônimos oferece suporte a todos os índices de texto completo que usam o idioma correspondente, e uma determinada lista de palavras irrelevantes (stoplist) pode ser associada aos índices de texto completo que você desejar.

Configurando um índice e um catálogo de texto completo

Isso envolve as seguintes etapas básicas:

Criar um catálogo de texto completo para armazenar índices de texto completo.

Cada índice de texto completo deve pertencer a um catálogo de texto completo. Você pode criar um catálogo de texto completo separado para cada índice de texto completo ou associar vários índices de texto completo a um determinado catálogo. Um catálogo de texto completo é um objeto virtual; ele não pertence a nenhum grupo de arquivos. O catálogo é um conceito lógico que faz referência a um grupo de índices de texto completo.
Crie um índice de texto completo na tabela ou exibição indexada.

Um índice de texto completo consiste em um tipo especial de índice funcional com base em token que é criado e mantido pelo Mecanismo de Texto Completo. Para criar uma pesquisa de texto completo em uma tabela ou exibição, ele deve ter um índice não nulo exclusivo de uma só coluna. O Mecanismo de Texto Completo exige que esse índice exclusivo mapeie cada linha da tabela para uma chave exclusiva e compactável. Um índice de texto completo pode incluir as colunas char, varchar, nchar, nvarchar, text, ntext, image, xml, varbinary e varbinary(max). Para obter mais informações, veja Criar e gerenciar índices de texto completo.

Antes de aprender a criar índices de texto completo, é importante considerar como eles diferem dos índices de SQL Server regulares. A tabela a seguir lista as diferenças.

Índices de texto completo	Índices regulares do SQL Server
Só é permitido um índice de texto completo por tabela.	Vários índices regulares são permitidos por tabela.
A adição de dados a índices de texto completo, chamada de população, pode ser solicitada através de uma agenda ou de uma solicitação específica e pode ocorrer automaticamente com a inclusão de novos dados.	Atualizados automaticamente quando os dados nos quais eles se baseiam são inseridos, atualizados ou excluídos.
Agrupados no mesmo banco de dados em um ou mais catálogos de texto completo.	Não agrupado.

Escolhendo opções para um índice de texto completo

Esta seção aborda os seguintes tópicos:

Escolhendo o idioma da coluna
Escolhendo um grupo de arquivos para um índice de texto completo
Atribuindo o índice de texto completo a um catálogo de texto completo
Associando uma lista de palavras irrelevantes (stoplist) ao índice de texto completo
Atualizando um índice de texto completo

Escolhendo o idioma das colunas

Para obter mais informações sobre aspectos a considerar na escolha do idioma das colunas, consulte Escolher um idioma ao criar um índice de texto completo.

Escolhendo um grupo de arquivos para um índice de texto completo

O processo de criação de um índice de texto completo é bastante intensivo de E/S (em um alto nível, ele consiste em ler dados de SQL Server e propagar os dados filtrados para o índice de texto completo). Como prática recomendada, localize um índice de texto completo no grupo de arquivos do banco de dados que seja o mais apropriado para maximizar o desempenho de E/S ou localize os índices de texto completo em outro grupo de arquivos de outro volume.

Se a facilidade de gerenciamento é um aspecto importante para você, é recomendável armazenar dados de tabela e qualquer catálogo de texto completo afiliado no mesmo grupo de arquivos. Às vezes, por motivo de desempenho, os dados de tabela e o índice de texto completo possivelmente precisarão estar em diferentes grupos de arquivos, armazenados em diferentes volumes, para maximizar o paralelismo de E/S.

Atribuindo o índice de texto completo a um catálogo de texto completo

É importante planejar o posicionamento de índices de texto completo de tabelas em catálogos de texto completo.

Recomendamos associar tabelas com as mesmas características de atualização (como um número pequeno de alterações versus muitas alterações ou tabelas que são alteradas com frequência durante um determinado período do dia) juntas no mesmo catálogo de texto completo. Através da definição de agendas de população de catálogo de texto completo, os índices de texto completo permanecem sincronizados com as tabelas, sem afetar de modo negativo o uso de recursos do servidor de banco de dados durante períodos de alta atividade do banco de dados.

Ao atribuir uma tabela a um catálogo de texto completo, considere as seguintes diretrizes:

Sempre selecione o menor índice exclusivo disponível para sua chave exclusiva de texto completo. (Um índice baseado em inteiro de 4 bytes é ideal.) Isso reduz significativamente os recursos exigidos pela Microsoft serviço Pesquisa no sistema de arquivos. Se a chave primária for grande (mais de 100 bytes), considere escolher outro índice exclusivo na tabela (ou criar outro índice exclusivo) como a chave exclusiva de texto completo. Caso contrário, se o tamanho da chave exclusiva de texto completo exceder o tamanho máximo permitido (900 bytes), a população de texto completo não poderá prosseguir.
Se você estiver indexando uma tabela que tem milhões de linhas, atribua a tabela a seu próprio catálogo de texto completo.
Considere o volume de alterações ocorridas nas tabelas que estão sendo indexadas com texto completo, bem como o total de linhas. Se o total de linhas que estiverem sendo alteradas, junto com o número de linhas da tabela presentes durante a última população de texto completo, representar milhões de linhas, atribua a tabela a seu próprio catálogo de texto completo.

Associando uma lista de palavras irrelevantes (stoplist) ao índice de texto completo

SQL Server 2008 apresenta listas de palavras irrelevantes. Uma lista de palavras irrelevantes também é conhecida como lista de palavras de ruído. Uma lista de palavras irrelevantes é associada a cada índice de texto completo, e as palavras dessa lista são aplicadas a consultas de texto completo nesse índice. Por padrão, a lista de palavras irrelevantes do sistema é associada a um novo índice de texto completo. Porém, você pode criar e usar sua própria lista de palavras irrelevantes. Para obter mais informações, veja Configurar e gerenciar palavras irrelevantes e listas de palavras irrelevantes para pesquisa de texto completo.

Por exemplo, a seguinte instrução TRANSact-SQL CREATE FULLTEXT STOPLISTcria uma nova lista de palavras irrelevantes de texto completo chamada myStoplist3 copiando da lista de palavras irrelevantes do sistema:

CREATE FULLTEXT STOPLIST myStoplist FROM SYSTEM STOPLIST;  
GO

A seguinte instrução Transact-SQL ALTER FULLTEXT STOPLISTaltera uma lista de palavras irrelevantes chamada myStoplist, adicionando a palavra 'en', primeiro para espanhol e depois para francês:

ALTER FULLTEXT STOPLIST MyStoplist ADD 'en' LANGUAGE 'Spanish';  
ALTER FULLTEXT STOPLIST MyStoplist ADD 'en' LANGUAGE 'French';  
GO

Atualizando um índice de texto completo

Como os índices normais do SQL Server , os índices de texto completo podem ser atualizados automaticamente à medida que dados são modificados nas tabelas associadas. Esse é o comportamento padrão. Se preferir, você pode manter seus índices de texto completo atualizados manualmente ou em intervalos agendados específicos. Preencher um índice de texto completo pode ser um procedimento demorado e usar muitos recursos, por isso a atualização de índices geralmente é feita como um processo assíncrono em segundo plano e mantém o índice de texto completo atualizado após modificações na tabela base. Atualizar um índice de texto completo imediatamente depois de cada alteração na tabela base pode demandar muitos recursos. Por isso, se você tiver uma taxa de atualização/inserção/exclusão muito alta, poderá observar alguma diminuição no desempenho de consulta. Se isso ocorrer, considere agendar atualizações manuais do controle de alterações para acompanhar o ritmo das inúmeras alterações periódicas, em vez de competir por recursos com as consultas.

Para monitorar o status de população, use as funções FULLTEXTCATALOGPROPERTY ou OBJECTPROPERTYEX. Para obter o status de população de catálogo, execute a seguinte instrução:

SELECT FULLTEXTCATALOGPROPERTY('AdvWksDocFTCat', 'Populatestatus');

Geralmente, se uma população completa estiver em andamento, o resultado retornado será 1.

Exemplo: Configurando a pesquisa de texto completo

O exemplo de duas partes a Document seguir cria um catálogo de texto completo chamado AdvWksDocFTCat no banco de dados AdventureWorks e cria um índice de texto completo na tabela no AdventureWorks2012. Essa instrução cria o catálogo de texto completo no diretório padrão especificado durante a instalação. A pasta nomeada AdvWksDocFTCat está no diretório padrão.

Para criar um catálogo de texto completo denominado AdvWksDocFTCat, o exemplo usa uma instrução CREATE FULLTEXT CATALOG :
```
USE AdventureWorks;  
GO  
CREATE FULLTEXT CATALOG AdvWksDocFTCat;  
```
Para você poder criar um índice de texto completo na tabela Document, verifique se a tabela tem um índice exclusivo, de uma só coluna e não anulável. A seguinte instrução CREATE INDEX cria um índice exclusivo, ui_ukDoc, na coluna DocumentID da tabela Documento:
```
CREATE UNIQUE INDEX ui_ukDoc ON Production.Document(DocumentID);  
```
Depois que você tiver uma chave exclusiva, poderá criar um índice de texto completo na tabela Document por meio da instrução CREATE FULLTEXT INDEX a seguir.
```
CREATE FULLTEXT INDEX ON Production.Document  
(  
    Document                         --Full-text index column name   
        TYPE COLUMN FileExtension    --Name of column that contains file type information  
        Language 2057                 --2057 is the LCID for British English  
)  
KEY INDEX ui_ukDoc ON AdvWksDocFTCat --Unique index  
WITH CHANGE_TRACKING AUTO            --Population type;  
GO  
```
TYPE COLUMN definido neste exemplo especifica a coluna de tipo da tabela que contém o tipo do documento em cada linha da coluna 'Document' (que é do tipo binário). A coluna type armazena a extensão de arquivo fornecida pelo usuário".doc", ".xls" e assim por diante do documento em uma determinada linha. O Mecanismo de Texto Completo usa a extensão de arquivo de uma dada linha para chamar o filtro correto a ser usado para analisar os dados contidos nessa linha. Depois que o filtro analisar os dados binários da linha, o separador de palavras especificado analisará o conteúdo (neste exemplo, é usado o separador de palavras do inglês britânico). Observe que o processo de filtragem ocorre apenas durante a indexação ou se um usuário inserir ou atualizar uma coluna na tabela base enquanto o controle automático de alterações está habilitado para o índice de texto completo. Para obter mais informações, veja Configurar e gerenciar filtros para pesquisa.

Tarefas comuns

Para criar um catálogo de texto completo

Para visualizar os índices de uma tabela (ou exibição)

sys.indexes (Transact-SQL)

Para criar um índice exclusivo

Para criar um índice de texto completo

Para exibir informações sobre um índice de texto completo

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.

Consulte Também

CREATE FULLTEXT CATALOG (Transact-SQL)
CREATE FULLTEXT INDEX (Transact-SQL)
CREATE FULLTEXT STOPLIST (Transact-SQL)
CREATE TABLE (Transact-SQL)
Popular índices de texto completo
FULLTEXTCATALOGPROPERTY (Transact-SQL)
OBJECTPROPERTYEX (Transact-SQL)

Compartilhar via