Une recherche plein texte SQL Server réellement utile

Un champ de recherche exécutant LIKE '%term%' sur de longs articles peut devenir coûteux à mesure que le contenu augmente. La recherche plein texte SQL Server fournit un autre accès, mais change aussi la notion de correspondance. Elle indexe des unités linguistiques, pas toutes les sous-chaînes de caractères possibles. Un remplacement aveugle peut donc accélérer une recherche tout en la rendant moins utile.

Séparez les besoins. Code produit exact, mot dans un texte, expression et fragment arbitraire représentent quatre questions différentes. Un index classique avec égalité peut mieux servir le code, tandis que le plein texte convient aux mots et aux règles de langue.

Choisir explicitement la langue

L'exemple crée des objets permanents de pratique. Utilisez une base jetable avec le composant plein texte installé et les droits nécessaires.

-- 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 clé plein texte repose sur un identifiant unique non NULL. Les deux colonnes utilisent les règles anglaises, LCID 1033. La langue influence la segmentation des mots et les formes fléchies; ce n'est pas une simple étiquette d'interface.

Une application multilingue doit relier la langue de chaque colonne indexée aux documents qu'elle contient. Mélanger plusieurs langues sous un réglage arbitraire peut diminuer la pertinence. Un stockage localisé séparé ou un moteur multilingue dédié peut être préférable lorsque les besoins dépassent ce modèle.

CHANGE_TRACKING AUTO entretient l'index de façon asynchrone. Sa création ne garantit pas la fin du peuplement initial. Une modification validée peut également attendre avant d'apparaître. Une recherche vide juste après l'installation ne prouve donc pas que son expression est incorrecte.

Classer les résultats de manière prévisible

FREETEXTTABLE accepte une recherche en langage naturel et retourne des clés avec un rang.

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;

Les valeurs de rang dépendent du contenu et de la requête, pas d'une probabilité calibrée de pertinence. ArticleId départage les rangs égaux. La recherche reste volontairement anglaise puisque les documents d'exemple le sont aussi.

FREETEXT convient aux mots saisis normalement. CONTAINS et CONTAINSTABLE proposent une grammaire plus structurée pour expressions, préfixes et combinaisons booléennes. Un préfixe correspond au début d'un token, pas à un fragment situé n'importe où. Ponctuation et segmentation peuvent traiter un identifiant technique autrement qu'une recherche de sous-chaîne.

Passez le texte dans un paramètre SQL. Pour une syntaxe structurée, le paramètre évite la concaténation SQL mais ne transforme pas toute entrée en grammaire valide. Validez ou construisez séparément les formes autorisées et expliquez les erreurs de saisie.

Les listes de mots vides peuvent retirer des termes fréquents. Testez une requête composée uniquement de ces mots, des phrases entre guillemets, des codes avec tirets et des formes linguistiques variées. Décidez du comportement d'une recherche vide sans la transformer automatiquement en lecture illimitée de la table.

Vérifier fraîcheur, filtres et pertinence

Cette inspection aide à distinguer installation absente et activité de peuplement.

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

En cas de documents manquants, examinez le peuplement et les diagnostics plein texte. Un état inactif ne prouve pas que tous les documents attendus ont été correctement traités. Vérifiez des identifiants récents et leurs mots recherchables, puis les erreurs de traitement éventuelles.

Appliquez les autorisations et restrictions de locataire avant toute restitution. Demander un petit ensemble global de meilleurs résultats puis filtrer le locataire peut fournir trop peu de lignes malgré de nombreux documents admissibles. Vérifiez complétude et plan de la stratégie réelle.

Utilisez un corpus représentatif et une sélection de recherches dont les réponses utiles sont connues. Mesurez latence et lectures, mais examinez également omissions et faux résultats pertinents. Une recherche rapide peut encore manquer son objectif.

Décrivez enfin le comportement avec des termes produit: recherche de codes exacts ou de contenu linguistique, et délai éventuel avant visibilité d'un document enregistré. Une recherche utile associe contrat clair, index adapté et vérification de pertinence.

Références techniques: Microsoft Learn: Full-text search · Microsoft Learn: FREETEXTTABLE · Microsoft Learn: CONTAINS.