Busca de texto completo útil no SQL Server

Uma busca que executa LIKE '%term%' sobre artigos extensos pode ficar cara conforme o acervo cresce. A busca de texto completo do SQL Server oferece outro acesso, mas também altera o significado de correspondência. Ela indexa unidades linguísticas, não todas as possíveis sequências de caracteres. Substituir LIKE sem acertar essa diferença pode tornar a busca mais rápida e menos útil.

Separe as necessidades. Código exato, palavra no texto, frase e fragmento arbitrário são quatro perguntas distintas. Uma igualdade em índice comum pode ser melhor para o código, enquanto texto completo atende palavras e regras de idioma.

Configurar o idioma explicitamente

O exemplo cria objetos permanentes de prática. Utilize uma base descartável com Full-Text Search instalado e permissões de configuração.

-- Run only in a disposable database with Full-Text Search installed.
CREATE TABLE dbo.SearchArticleDemo (
    ArticleId int NOT NULL CONSTRAINT PK_SearchArticleDemo PRIMARY KEY,
    Title nvarchar(200) NOT NULL,
    Body nvarchar(max) NOT NULL
);
INSERT dbo.SearchArticleDemo VALUES
(1, N'Index design', N'An index can reduce reads for selective queries.'),
(2, N'Backup planning', N'Restore tests verify that backups are usable.'),
(3, N'Query performance', N'Query performance depends on access paths and data.');
CREATE FULLTEXT CATALOG SearchArticleDemoCatalog;
CREATE FULLTEXT INDEX ON dbo.SearchArticleDemo
    (Title LANGUAGE 1033, Body LANGUAGE 1033)
KEY INDEX PK_SearchArticleDemo
ON SearchArticleDemoCatalog
WITH CHANGE_TRACKING AUTO;

A chave do índice usa um identificador único e não nulo. As duas colunas seguem inglês, LCID 1033. O idioma influencia a separação de palavras e suas flexões; não é apenas uma descrição na interface.

Uma aplicação multilíngue deve relacionar o idioma de cada coluna indexada aos documentos armazenados. Misturar idiomas diferentes sob uma configuração arbitrária pode reduzir a relevância. Armazenamento localizado separado ou um mecanismo multilíngue especializado pode ser melhor quando a necessidade ultrapassa esse modelo.

CHANGE_TRACKING AUTO mantém mudanças de maneira assíncrona. Criar o índice não significa que sua população inicial terminou. Uma edição confirmada também pode demorar a aparecer. Portanto, uma consulta sem resultados logo após a configuração não comprova que a expressão está errada.

Entregar resultados em ordem previsível

FREETEXTTABLE recebe texto em linguagem natural e retorna chaves com valores de ranking.

DECLARE @Search nvarchar(4000) = N'index performance';
SELECT TOP (10) a.ArticleId, a.Title, ft.[RANK]
FROM FREETEXTTABLE(
    dbo.SearchArticleDemo, (Title, Body), @Search, LANGUAGE 1033
) AS ft
JOIN dbo.SearchArticleDemo AS a ON a.ArticleId = ft.[KEY]
ORDER BY ft.[RANK] DESC, a.ArticleId;

Os valores dependem do conteúdo e da consulta, não de uma probabilidade calibrada de relevância. ArticleId desempata rankings iguais. A entrada continua intencionalmente em inglês porque os documentos de prática também estão em inglês.

FREETEXT atende palavras digitadas normalmente. CONTAINS e CONTAINSTABLE oferecem gramática estruturada para frases, prefixos e combinações booleanas. Um prefixo corresponde ao começo de um token, não a qualquer trecho no meio dele. Pontuação e separadores linguísticos podem tratar códigos técnicos de forma diferente de uma busca por substring.

Passe a entrada como parâmetro SQL. Para sintaxe estruturada, parametrizar evita concatenação SQL, mas não transforma qualquer texto em gramática válida. Valide ou construa separadamente as formas permitidas e apresente mensagens claras para entradas inválidas.

Stoplists podem remover palavras comuns. Teste buscas contendo somente essas palavras, frases entre aspas, códigos com hífen e formas específicas do idioma. Defina o comportamento de entradas vazias ou não pesquisáveis sem convertê-las automaticamente em uma leitura irrestrita da tabela.

Validar atualização, filtros e relevância

A inspeção ajuda a separar ausência do componente de atividade de população.

SELECT FULLTEXTSERVICEPROPERTY('IsFullTextInstalled') AS Installed,
       OBJECTPROPERTYEX(OBJECT_ID(N'dbo.SearchArticleDemo'),
           'TableFulltextPopulateStatus') AS PopulationStatus;

Quando faltam resultados, examine população e diagnósticos de texto completo. Um estado ocioso não prova que todos os documentos esperados foram processados corretamente. Confira identificadores recentes, palavras específicas e eventuais falhas de processamento.

Aplique autorização e restrições de tenant antes de retornar resultados. Solicitar poucos candidatos com melhor ranking global e filtrar pelo tenant depois pode entregar poucas linhas apesar de existirem muitas correspondências válidas. Verifique completude e plano da estratégia efetivamente usada.

Teste conteúdo representativo e uma coleção de consultas com respostas úteis conhecidas. Meça latência e leituras, mas também examine documentos ausentes e resultados enganosos. Atender a uma meta de velocidade não comprova que a busca resolve a tarefa.

Por fim, descreva o comportamento em termos do produto: pesquisa de códigos exatos ou linguagem dos artigos e eventual demora para um documento recém-salvo aparecer. Uma busca útil depende de contrato claro, indexação adequada e avaliação de relevância em conjunto.

Referências técnicas: Microsoft Learn: Full-text search · Microsoft Learn: FREETEXTTABLE · Microsoft Learn: CONTAINS.