• Article

Mise en route avec la recherche en texte intégral

Les bases de données dans SQL Server prennent en charge le texte intégral par défaut. Cependant, pour utiliser l'index de recherche en texte intégral dans une table, vous devez configurer l'outil d'indexation de texte intégral dans les colonnes des tables auxquelles vous souhaitez accéder via le moteur de texte intégral.

Cette opération implique les étapes fondamentales suivantes :

  1. Créez un catalogue de texte intégral pour stocker les index de texte intégral.

    Chaque index de recherche en texte intégral doit appartenir à un catalogue de texte intégral. Vous pouvez créer un catalogue de texte séparé pour chaque index de recherche en texte intégral ou associer plusieurs index de recherche en texte intégral à un catalogue donné.

    Notes

    À compter de SQL Server 2008, un catalogue de texte intégral est un objet virtuel qui n'appartient à aucun groupe de fichiers. Un catalogue de texte intégral est un concept logique qui fait référence à un groupe d'index de texte intégral.

  2. Créez un index de recherche en texte intégral sur la table ou vue indexée.

    Un index de recherche en texte intégral est un type spécial d'index fonctionnel par jeton qui est construit et géré par le Moteur d'indexation et de recherche en texte intégral. Pour que la création d'une recherche en texte intégral soit possible sur une table ou une vue, celles-ci doivent avoir un index unique ne comportant qu'une seule colonne et n'acceptant pas les valeurs Null. Le Moteur d'indexation et de recherche en texte intégral utilise cet index unique pour mapper chaque ligne de la table à une clé compressible unique. Un index de recherche en texte intégral peut inclure les colonnes char, varchar, nchar, nvarchar, text, ntext, image, xml, varbinary et varbinary(max). Pour plus d'informations, consultez Vue d'ensemble de l'indexation de texte intégral.

Avant d'apprendre à créer des index de recherche en texte intégral, il est important de déterminer en quoi ils diffèrent des index SQL Server classiques. Le tableau suivant répertorie les différences.

Index de recherche en texte intégral

Index SQL Server classiques

Un seul index de recherche en texte intégral est autorisé par table.

Plusieurs index classiques sont autorisés par table.

L'ajout de données aux index de recherche en texte intégral, opération que l'on appelle remplissage, doit être demandé, soit dans le cadre de la planification, soit par le biais d'une requête spécifique, ou peut intervenir automatiquement lors de l'ajout de nouvelles données.

Sont mis à jour automatiquement lorsque les données sur lesquelles ils sont fondés sont modifiées, mises à jour ou supprimées.

Sont groupés à l'intérieur d'une même base de données dans un ou plusieurs catalogues de texte intégral.

Ne sont pas groupés.

Considérations relatives à la création d'un index de recherche en texte intégral

Cette section couvre ce qui suit :

  • Sélection du langage de la colonne

  • Sélection d'un groupe de fichiers pour un index de recherche en texte intégral

  • Affectation de l'index de recherche en texte intégral à un catalogue de texte intégral

  • Association d'une liste de mots vides à l'index de recherche en texte intégral

  • Mise à jour d'un index de recherche en texte intégral

Sélection de la langue de la colonne

Pour plus d'informations sur les éléments à prendre en considération lors du choix de la langue de la colonne, consultez Méthodes conseillées pour le choix d'une langue lors de la création d'un index de recherche en texte intégral.

Choix d'un groupe de fichiers pour un index de recherche en texte intégral

Le processus de création d'un index de recherche en texte intégral est une opération assez intensive en matière d'entrées/sorties (à haut niveau, elle consiste à lire les données de SQL Server, puis à propager les données filtrées vers l'index de recherche en texte intégral). Comme meilleure pratique recommandée, localisez un index de recherche en texte intégral dans le groupe de fichiers de base de données qui convient le mieux pour optimiser les performances d'E/S ou localisez les index de recherche en texte intégral dans un groupe de fichiers différent sur un autre volume.

Lorsque la facilité de gestion représente un critère important pour vous, nous vous recommandons de stocker les données de table et tout catalogue de texte intégral affilié dans le même groupe de fichiers. Quelquefois, en raison de critères de performance, vous pouvez souhaiter avoir les données de table et l'index de recherche en texte intégral dans des groupes de fichiers différents stockés sur des volumes différents afin d'optimiser le parallélisme d'E/S.

Affectation de l'index de recherche en texte intégral à un catalogue de texte intégral

Il est important de planifier le placement d'index de texte intégral pour les tables des catalogues de texte intégral.

Nous vous recommandons d'associer les tables ayant les mêmes caractéristiques de mise à jour (par exemple avec peu de modifications ou, au contraire, beaucoup de modifications, ou les tables fréquemment modifiées à un moment donné de la journée) dans le même catalogue de texte intégral. Cette manière de configurer la planification du remplissage du catalogue de texte intégral permet d'assurer la synchronisation perpétuelle des index de texte intégral et des tables, sans nuire à l'utilisation des ressources du serveur de base de données pendant les périodes de forte activité.

Lorsque vous affectez une table à un catalogue de texte intégral, prenez en considération les directives suivantes :

  • Sélectionnez systématiquement le plus petit index unique disponible comme clé unique de texte intégral Il s'agira idéalement d'un index de quatre octets, basé sur des entiers. Ainsi, les ressources exigées par le service Microsoft Search dans le système de fichiers sont réduites de manière significative. Si la clé primaire est volumineuse (plus de 100 octets), pensez à choisir un autre index unique pour la table (ou créez-le) comme clé unique de texte intégral. Dans le cas contraire, si la taille de la clé unique de texte intégral dépasse la taille maximale autorisée (900 octets), le remplissage de texte intégral est impossible.

  • Si vous indexez une table de plusieurs millions de lignes, affectez-la à un catalogue de texte intégral qui lui est propre.

  • Prenez en compte la quantité de modifications apparaissant dans les tables soumises à l'indexation de texte intégral ainsi que le nombre total de lignes concernées dans ces tables. Si le nombre total de lignes modifiées, auquel s'ajoute le nombre de lignes de table présentes au cours du dernier remplissage de texte intégral, s'élève à plusieurs millions, affectez la table à un catalogue de texte intégral qui lui est propre.

Association d'une liste de mots vides à l'index de recherche en texte intégral

SQL Server 2008 introduit les listes de mots vides. Une liste de mots vides est une liste contenant des mots vides, également appelés mots parasites. Une liste de mots vides est associée à chaque index de recherche en texte intégral, et les mots contenus dans cette liste de mots vides s'appliquent aux requêtes de texte intégral sur cet index. Par défaut, la liste de mots vides système est associée à un nouvel index de recherche en texte intégral. Toutefois, vous pouvez créer et utiliser à la place votre propre liste de mots vides. Pour plus d'informations, consultez Mots vides et listes de mots vides.

Par exemple, l'instruction suivante CREATE FULLTEXT STOPLISTTransact-SQL crée une nouvelle liste de mots vides de texte intégral nommée myStoplist3 en copiant à partir de la liste de mots vides système :

CREATE FULLTEXT STOPLIST myStoplist FROM SYSTEM STOPLIST;
GO

L'instruction suivante ALTER FULLTEXT STOPLISTTransact-SQL illustre la modification d'une liste de mots vides nommée myStoplist par l'ajout du mot 'en' en premier pour l'espagnol puis pour le français :

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

Mise à jour d'un index de recherche en texte intégral

À l'instar des index SQL Server classiques, les index de texte intégral peuvent être automatiquement mis à jour à mesure que les données sont modifiées dans les tables associées. Il s'agit du comportement par défaut. Vous pouvez, également, garder manuellement vos index de recherche en texte intégral à jour ou à intervalles planifiés spécifiés. L'alimentation d'un index de recherche en texte intégral peut être exigeante en termes de temps et de ressources ; par conséquent, la mise à jour de l'index est effectuée habituellement en mode de processus asynchrone qui s'exécute en arrière-plan et conserve l'index de recherche en texte intégral à jour après les modifications opérées dans la table de base. La mise à jour immédiate d'un index de recherche en texte intégral après chaque modification dans la table de base peut être gourmande en ressources. Par conséquent, si vous avez un taux de mise à jour/insertion/suppression très élevé, vous pouvez constater une baisse dans les performances des requêtes. Si cela se produit, pensez à planifier de temps à autre des mises à jour manuelles du suivi des modifications afin de ne pas perdre trace des nombreuses modifications, au lieu de mettre des requêtes en concurrence pour les ressources.

Pour analyser l'état du remplissage, utilisez les fonctions FULLTEXTCATALOGPROPERTY ou OBJECTPROPERTYEX. Pour connaître l'état du remplissage du catalogue, exécutez l'instruction suivante :

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

En règle générale, si un remplissage complet est en cours, le résultat retourné est 1.

Exemple : création d'une recherche en texte intégral (Transact-SQL)

L'exemple en deux parties ci-dessous crée un catalogue de texte intégral nommé AdvWksDocFTCat sur la base de données AdventureWorks2008R2, puis crée un index de recherche en texte intégral sur la table Document dans AdventureWorks2008R2. Cette instruction permet de créer le catalogue de texte intégral dans le répertoire par défaut spécifié durant l'installation. Le dossier nommé AdvWksDocFTCat se trouve dans le répertoire par défaut.

  1. Pour créer un catalogue de texte intégral nommé AdvWksDocFTCat, l'exemple utilise une instruction CREATE FULLTEXT CATALOG :

    USE AdventureWorks2008R2;
GO
CREATE FULLTEXT CATALOG AdvWksDocFTCat;

  2. Avant de créer un index de recherche en texte intégral sur la table Document, assurez-vous que la table dispose d'un index unique qui n'accepte pas les valeurs NULL et ne comporte qu'une seule colonne. L'instruction suivante CREATE INDEX crée un index unique, ui_ukDoc, sur la colonne DocumentID de la table Document :

    CREATE UNIQUE INDEX ui_ukDoc ON Production.Document(DocumentID);

  3. Une fois que vous avez une clé unique, vous pouvez créer un index de recherche en texte intégral sur la table Document en utilisant l'instruction CREATE FULLTEXT INDEX.

    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

    Le paramètre TYPE COLUMN défini dans cet exemple spécifie la colonne de type dans la table qui contient le type du document dans chaque ligne de la colonne Document (lequel est de type binaire). La colonne de type stocke l'extension de fichier fournie par l'utilisateur (.doc, .xls, etc.) du document dans une ligne donnée. Le Moteur d'indexation et de recherche en texte intégral utilise l'extension de fichier dans une ligne donnée afin d'appeler le filtre approprié pour l'analyse des données de cette ligne. Une fois que le filtre a analysé les données binaires de la ligne, l'analyseur lexical spécifié analyse le contenu (dans cet exemple, c'est l'analyseur lexical pour l'anglais britannique qui est utilisé). Notez que le processus de filtrage se produit uniquement au moment de l'indexation ou si un utilisateur insère ou met à jour une colonne dans la table de base alors que le suivi automatique des modifications est activé pour l'index de recherche en texte intégral. Pour plus d'informations, consultez Filtres de recherche en texte intégral.

Pour afficher des informations sur l'index de recherche en texte intégral

Vue catalogue ou vue de gestion dynamique

Description

sys.fulltext_index_catalog_usages (Transact-SQL)

Renvoie une ligne pour chaque catalogue de texte intégral vers une référence d'index de texte intégral.

sys.fulltext_index_columns (Transact-SQL)

Contient une ligne pour chaque colonne qui fait partie d'un index de recherche en texte intégral.

sys.fulltext_index_fragments (Transact-SQL)

Un index de recherche en texte intégral utilise des tables internes appelées fragments d'index de recherche en texte intégral pour stocker les données d'index inversées. Cette vue permet d'interroger les métadonnées relatives à ces fragments. Cette vue contient une ligne pour chaque fragment d'index de recherche en texte intégral dans chaque table qui contient un index.

sys.fulltext_indexes (Transact-SQL)

Contient une ligne par index de texte intégral d'un objet tabulaire.

sys.dm_fts_index_keywords (Transact-SQL)

Retourne des informations sur le contenu d'un index de recherche en texte intégral pour la table spécifiée.

sys.dm_fts_index_keywords_by_document (Transact-SQL)

Retourne des informations sur le contenu de niveau document d'un index de recherche en texte intégral pour la table spécifiée. Un mot clé donné peut apparaître dans plusieurs documents.

sys.dm_fts_index_population (Transact-SQL)

Retourne des informations sur les remplissages d'index de texte intégral actuellement en cours.