Búsqueda de texto completo útil en SQL Server

Un buscador que ejecuta LIKE '%term%' sobre artículos extensos puede volverse costoso al crecer la colección. La búsqueda de texto completo de SQL Server ofrece otro acceso, pero también cambia el significado de una coincidencia. Indexa unidades lingüísticas, no todas las subcadenas posibles. Sustituir LIKE sin acordar esa diferencia puede acelerar una función que ahora encuentra peor lo que el usuario necesita.

Distingue los requisitos. Un código exacto, una palabra, una frase y un fragmento arbitrario son preguntas diferentes. Para el código puede funcionar mejor una igualdad con índice convencional; el texto completo encaja con palabras y reglas lingüísticas.

Configurar un idioma explícito

El ejemplo crea objetos permanentes de práctica. Usa una base desechable con búsqueda de texto completo instalada y permisos adecuados.

-- 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;

La clave del índice es un identificador único y no nulo. Las dos columnas utilizan inglés, LCID 1033. El idioma afecta a la separación de palabras y a sus formas flexionadas; no es una etiqueta de la interfaz.

Una aplicación multilingüe debe relacionar el idioma de cada columna indexada con los documentos almacenados. Mezclar lenguas distintas bajo una configuración arbitraria puede perjudicar la relevancia. Separar almacenamiento localizado o usar un buscador multilingüe especializado puede ser más apropiado si el requisito supera este modelo.

CHANGE_TRACKING AUTO mantiene el índice de forma asíncrona. Crearlo no significa que haya terminado la población inicial. Una edición confirmada también puede tardar en aparecer. Una consulta vacía inmediatamente después de configurar no demuestra por sí sola un error de sintaxis.

Devolver resultados con orden predecible

FREETEXTTABLE recibe texto de lenguaje natural y devuelve claves con rangos.

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;

Los rangos dependen del contenido y la consulta; no son probabilidades calibradas de relevancia. ArticleId desempata valores iguales. La entrada permanece en inglés deliberadamente porque los documentos de práctica están escritos en inglés.

FREETEXT es útil para palabras corrientes. CONTAINS y CONTAINSTABLE exponen una gramática estructurada para frases, prefijos y combinaciones booleanas. Un prefijo coincide con comienzos de tokens, no con cualquier fragmento interior. La puntuación y los separadores lingüísticos pueden tratar un código técnico de forma diferente a una búsqueda literal.

Pasa la entrada como parámetro SQL. En búsquedas estructuradas, parametrizar evita concatenar SQL, pero no convierte cualquier texto en gramática válida. Valida o construye por separado las formas admitidas y muestra mensajes comprensibles ante errores.

Las listas de palabras irrelevantes pueden eliminar términos comunes. Prueba entradas compuestas solo por esas palabras, frases entre comillas, identificadores con guiones y variantes lingüísticas. Decide qué hace una consulta vacía o no procesable, sin transformarla automáticamente en un recorrido completo sin límites.

Comprobar actualización, filtros y relevancia

Esta inspección distingue falta de instalación de actividad de población.

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

Cuando faltan resultados, examina el estado de población y los diagnósticos correspondientes. Un estado inactivo no demuestra que todos los documentos esperados se indexaran correctamente. Comprueba identificadores recientes y palabras concretas, además de posibles fallos de procesamiento.

Aplica permisos y restricciones de inquilino antes de devolver resultados. Pedir primero unos pocos mejores candidatos globales y filtrarlos después por inquilino puede producir pocas filas aunque haya muchas coincidencias válidas. Revisa integridad y plan de la estrategia real.

Prueba contenido representativo y consultas seleccionadas cuyos resultados útiles sean conocidos. Mide latencia y lecturas, pero revisa también documentos omitidos y coincidencias engañosas. Una respuesta rápida puede seguir sin resolver la necesidad.

Finalmente, explica el comportamiento en términos del producto: si se buscan códigos exactos o lenguaje de artículos y cuánto puede tardar en aparecer un documento guardado. La búsqueda útil combina reglas claras, indexación adecuada y comprobaciones de relevancia.

Referencias técnicas: Microsoft Learn: Full-text search · Microsoft Learn: FREETEXTTABLE · Microsoft Learn: CONTAINS.