Créer et gérer des index de recherche en texte intégral

Article
07/15/2024

S’applique à : SQL Server Azure SQL Database Azure SQL Managed Instance

Cet article décrit comment créer, remplir et gérer des index de recherche en texte intégral dans SQL Server.

Création d'un catalogue de texte intégral

Avant de pouvoir créer un index de recherche en texte intégral, vous devez disposer d’un catalogue de texte intégral. Le catalogue est un conteneur virtuel pour un ou plusieurs index de recherche en texte intégral. Pour plus d’informations, consultez Créer et gérer des catalogues de texte intégral.

Créer, modifier ou supprimer un index de recherche en texte intégral

Remplir un index de recherche en texte intégral

Le processus de création et de gestion d’un index de recherche en texte intégral est appelé alimentation (également appelé analyse). Il existe trois types de remplissage d’index de recherche en texte intégral :

Alimentation complète
Alimentation basée sur le suivi des modifications
Remplissage incrémentiel basé sur un horodatage.

Pour plus d’informations, consultez Alimenter des index de recherche en texte intégral.

Afficher les propriétés d’un index de recherche en texte intégral

Afficher les propriétés d’un index de recherche en texte intégral avec Transact-SQL

Vue catalogue ou vue de gestion dynamique	Description
sys.fulltext_index_catalog_usages (Transact-SQL)	Retourne une ligne pour chaque catalogue de texte intégral vers une référence d'index de recherche en 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 recherche en 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.

Afficher les propriétés d’un index de recherche en texte intégral avec SQL Server Management Studio

Remarque

Pour afficher les propriétés des index de recherche en texte intégral pour les bases de données Azure SQL, utilisez Transact-SQL.

Dans SQL Server Management Studio, dans l’Explorateur d’objets, développez le serveur.
Développez Bases de données, puis la base de données qui contient l’index de recherche en texte intégral.
Développez Tables.
Cliquez avec le bouton droit sur la table sur laquelle l’index de recherche en texte intégral est défini, sélectionnez Index de recherche en texte intégral et, dans le menu contextuel Index de recherche en texte intégral, sélectionnez Propriétés. La boîte de dialogue Propriétés d’index de recherche en texte intégral s’affiche.

Dans le volet Sélectionner une page , vous pouvez sélectionner l’une des pages suivantes :

Page	Description
Général	Affiche les propriétés de base de l'index de recherche en texte intégral. Il s'agit de plusieurs propriétés modifiables et non modifiables telles que le nom de la base de données, le nom de la table et le nom de la colonne clé de recherche en texte intégral. Les propriétés modifiables sont les suivantes : Liste de mots vides de l’index de recherche en texte intégral Indexation de texte intégral activée Suivi des modifications Liste de propriétés de recherche
Colonnes	Affiche les colonnes de table qui sont disponibles pour l'indexation de texte intégral. La ou les colonnes sélectionnées sont indexées en texte intégral. Vous pouvez sélectionner autant de colonnes disponibles que vous souhaitez inclure dans l'index de recherche en texte intégral. Pour plus d’informations, consultez Alimenter des index de recherche en texte intégral.
Planifications	Utilisez cette page afin de créer ou gérer des planifications pour un travail de l'Agent SQL Server qui démarre un remplissage incrémentiel de la table pour remplir l'index de recherche en texte intégral. Pour plus d’informations, consultez Alimenter des index de recherche en texte intégral. Remarque : Une fois que vous avez fermé la boîte de dialogue Propriétés d’index de recherche en texte intégral , la planification que vous venez de créer est associée à un travail de SQL Server Agent (Démarrer le remplissage incrémentiel de la table sur nom_base_de_données.nom_table).

Sélectionnez OK pour enregistrer vos modifications et fermer la boîte de dialogue Propriétés d’index de recherche en texte intégral.

Afficher les propriétés des colonnes et tables indexées

Vous pouvez faire appel à plusieurs fonctions Transact-SQL, comme OBJECTPROPERTYEX, pour vous procurer la valeur de diverses propriétés d'indexation de texte intégral. Ces informations sont utiles pour administrer la recherche en texte intégral et résoudre les problèmes qui la concernent.

Le tableau ci-après recense les propriétés en texte intégral liées aux colonnes et tables indexées, ainsi que les fonctions Transact-SQL qui leur sont associées.

Propriété	Description	Fonction
FullTextTypeColumn	TYPE COLUMN de la table qui contient les informations sur le type de document de la colonne.	COLUMNPROPERTY
IsFulltextIndexed	Indique si une colonne a été activée pour l'indexation de texte intégral.	COLUMNPROPERTY
IsFulltextKey	Indique si l'index représente la clé de texte intégral d'une table.	INDEXPROPERTY
TableFulltextBackgroundUpdateIndexOn	Indique si une table possède une indexation de mise à jour d'arrière-plan de texte intégral.	OBJECTPROPERTYEX
TableFulltextCatalogId	ID du catalogue de texte intégral dans lequel résident les données d'indexation de texte intégral de la table.	OBJECTPROPERTYEX
TableFulltextChangeTrackingOn	Indique si le suivi des modifications de texte intégral est activé pour la table.	OBJECTPROPERTYEX
TableFulltextDocsProcessed	Nombre de lignes traitées depuis le démarrage de l'indexation de texte intégral.	OBJECTPROPERTYEX
TableFulltextFailCount	Nombre de lignes que la recherche en texte intégral n'a pas indexées.	OBJECTPROPERTYEX
TableFulltextItemCount	Nombre de lignes dont l'indexation de texte intégral a réussi.	OBJECTPROPERTYEX
TableFulltextKeyColumn	ID de la colonne clé unique de texte intégral.	OBJECTPROPERTYEX
TableFullTextMergeStatus	Indique s'il s'agit d'une table qui a un index de recherche en texte intégral qui est en cours de fusion.	OBJECTPROPERTYEX
TableFulltextPendingChanges	Nombre d'entrées de suivi des modifications en attente de traitement.	OBJECTPROPERTYEX
TableFulltextPopulateStatus	État de remplissage de la table de texte intégral.	OBJECTPROPERTYEX
TableHasActiveFulltextIndex	Indique si une table possède un index de recherche en texte intégral actif.	OBJECTPROPERTYEX

Obtenir des informations sur la colonne de clés de texte intégral

En général, le résultat des fonctions d'ensemble de lignes CONTAINSTABLE ou FREETEXTTABLE doit être joint avec la table de base. Dans ce cas-là, vous devez connaître le nom de la colonne clé unique. Vous pouvez déterminer si un index unique donné est utilisé comme clé de texte intégral et obtenir l'identificateur de la colonne clés de texte intégral.

Déterminer si un index unique donné est utilisé comme colonne de clés de texte intégral

Utilisez une instruction SELECT pour appeler la fonction INDEXPROPERTY. Lors de l’appel de fonction, utilisez la fonction OBJECT_ID pour convertir le nom de la table (table_name) en ID de table, spécifiez le nom d’un index unique pour la table et indiquez la propriété d’index IsFulltextKey, comme suit :

SELECT INDEXPROPERTY(OBJECT_ID('table_name'), 'index_name',  'IsFulltextKey');

L'instruction retourne la valeur 1 si l'index est utilisé pour garantir l'unicité de la colonne clé de texte intégral, et la valeur 0 dans le cas contraire.

Exemple

L'exemple suivant permet de déterminer si l'index PK_Document_DocumentNode est utilisé pour garantir l'unicité de la colonne clé de texte intégral, comme suit :

USE AdventureWorks2022;
GO
SELECT INDEXPROPERTY(OBJECT_ID('Production.Document'), 'PK_Document_DocumentNode',  'IsFulltextKey');

Cet exemple retourne la valeur 1 si l'index PK_Document_DocumentNode est utilisé pour garantir l'unicité de la colonne clés de texte intégral. Si tel n'est pas le cas, la valeur 0 ou une valeur Null est retournée. La valeur NULL signifie que vous utilisez un nom d'index non valide, que le nom d'index ne correspond pas à la table, que la table n'existe pas, etc.

Rechercher l’identificateur de la colonne de clés de texte intégral

Chaque table activée pour la recherche en texte intégral comporte une colonne qui est utilisée pour garantir l’unicité des lignes de la table (colonne à clé unique). La propriété TableFulltextKeyColumn, obtenue à l’aide de la fonction OBJECTPROPERTYEX, contient l’ID de la colonne clé unique.

Pour obtenir cet identificateur, vous pouvez utiliser une instruction SELECT afin d'appeler la fonction OBJECTPROPERTYEX. Utilisez la fonction OBJECT_ID pour convertir le nom de la table (nom_table) en ID de table et spécifiez la propriété TableFulltextKeyColumn , comme suit :

SELECT OBJECTPROPERTYEX(OBJECT_ID('table_name'), 'TableFulltextKeyColumn' ) AS 'Column Identifier';

Exemples

L'exemple ci-après retourne l'identificateur de la colonne clé de texte intégral ou une valeur Null. La valeur NULL signifie que vous utilisez un nom d'index non valide, que le nom d'index ne correspond pas à la table, que la table n'existe pas, etc.

USE AdventureWorks2022;
GO
SELECT OBJECTPROPERTYEX(OBJECT_ID('Production.Document'), 'TableFulltextKeyColumn');
GO

L'exemple ci-après explique comment utiliser l'identificateur de la colonne clé unique pour obtenir le nom de la colonne.

USE AdventureWorks2022;
GO

DECLARE @key_column SYSNAME

SET @key_column = COL_NAME(OBJECT_ID('Production.Document'),
   OBJECTPROPERTYEX(OBJECT_ID('Production.Document'), 'TableFulltextKeyColumn'));

SELECT @key_column AS 'Unique Key Column';
GO

Cet exemple retourne une colonne de jeu de résultats appelée Unique Key Column, qui contient une seule ligne indiquant le nom de la colonne clé unique de la table Document, DocumentNode. Si cette requête contenait un nom d'index non valide, si le nom d'index ne correspondait pas à la table, si la table n'existait pas, etc., une valeur NULL serait retournée.

Colonnes varbinary(max) et XML d’index

Si une colonne varbinary(max), varbinaryou xml est indexée en texte intégral, elle peut faire l’objet d’une requête à l’aide des prédicats de texte intégral (CONTAINS et FREETEXT) et des fonctions de texte intégral (CONTAINSTABLE et FREETEXTTABLE), au même titre que n’importe quelle autre colonne indexée en texte intégral.

Données varbinary(max) ou varbinary d’index

Une seule colonne varbinary(max) ou varbinary peut stocker de nombreux types de documents. SQL Server prend en charge tout type de document pour lequel un filtre est installé et disponible dans le système d'exploitation. Le type d'un document est identifié par l'extension de fichier de celui-ci. Par exemple, pour une extension de fichier .doc, la recherche en texte intégral utilise le filtre qui prend en charge les documents Microsoft Word. Pour obtenir la liste des types de documents disponibles, interrogez l’affichage catalogue sys.fulltext_document_types .

Le Moteur d'indexation et de recherche en texte intégral peut utiliser des filtres installés dans le système d'exploitation. Avant de pouvoir utiliser des filtres de système d'exploitation, des analyseurs lexicaux et des générateurs de formes dérivées, vous devez les charger dans l'instance de serveur, comme suit :

EXEC sp_fulltext_service @action = 'load_os_resources', @value = 1;

Pour créer un index de recherche en texte intégral sur une colonne varbinary(max) , le moteur d’indexation et de recherche en texte intégral a besoin d’accéder aux extensions de fichier des documents dans la colonne varbinary(max) . Ces informations doivent être stockées dans une colonne de table, appelée colonne de type, qui doit être associée à la colonne varbinary(max) dans l’index de recherche en texte intégral. Lors de l'indexation d'un document, le Moteur d'indexation et de recherche en texte intégral utilise l'extension de fichier indiquée dans la colonne de type pour identifier le filtre à utiliser.

Données XML d’index

Une colonne de type de données xml stocke uniquement des documents et des fragments XML, et seul le filtre XML est utilisé pour les documents. Par conséquent, une colonne de type est inutile. Sur les colonnes xml , l’index de recherche en texte intégral indexe le contenu des éléments XML, sans prendre en compte les balises XML. Les valeurs d'attributs sont indexées en texte intégral, à moins qu'il ne s'agisse de valeurs numériques. Des balises d'éléments sont utilisées comme limites de jeton. Les fragments et les documents XML ou HTML correctement formés contenant plusieurs langues sont pris en charge.

Pour plus d’informations sur l’indexation et l’interrogation sur une colonne xml, consultez Utiliser la recherche en texte intégral avec des colonnes XML.

Désactiver ou réactiver l’indexation de texte intégral pour une table

Dans SQL Server, par défaut, toutes les bases de données créées par les utilisateurs sont activées pour la recherche en texte intégral. De plus, une table individuelle est automatiquement activée pour l'indexation de texte intégral dès qu'un index de recherche en texte intégral est créé sur cette table et qu'une colonne est ajoutée à l'index. Une table est automatiquement désactivée pour l'indexation de recherche en texte intégral lorsque la dernière colonne est supprimée de son index de texte intégral.

Dans une table à index de recherche en texte intégral, vous pouvez désactiver ou réactiver manuellement une table pour l'indexation de recherche en texte intégral en utilisant SQL Server Management Studio.

Développez le groupe de serveurs, Bases de données, puis la base de données qui contient la table à activer pour l’indexation de texte intégral.
Développez Tableset cliquez avec le bouton droit sur la table que vous souhaitez désactiver ou réactiver pour l’indexation de texte intégral.
Sélectionnez Index de recherche en texte intégral, puis cliquez sur Disable Full-Text index (Désactiver l’index de recherche en texte intégral) ou Enable Full-Text index(Activer l’index de recherche en texte intégral).

Supprimer un index de recherche en texte intégral d’une table

Dans l'Explorateur d'objets, cliquez avec le bouton droit sur la table dotée de l'index de recherche en texte intégral à supprimer.
Sélectionnez Supprimer l’index de recherche en texte intégral.
Quand le système vous y invite, cliquez sur OK pour confirmer la suppression de l’index de recherche en texte intégral.

Partage via