Créer des index filtrés

  • Article

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Cet article explique comment créer un index filtré à l’aide de SQL Server Management Studio (SSMS) ou de Transact-SQL. Un index filtré est un index non-cluster rowstore sur disque optimisé qui convient particulièrement aux requêtes qui effectuent des sélections dans un sous-ensemble de données bien défini. Il utilise un prédicat de filtre pour indexer une partie des lignes de la table. Un index filtré bien conçu peut améliorer les performances des requêtes et réduire les coûts de maintenance et de stockage des index par rapport aux index de table entière.

Les index filtrés peuvent présenter les avantages suivants par rapport aux index de table entière :

  1. Meilleures performances des requêtes et qualité de plan améliorée.

    Un index filtré bien conçu améliore les performances des requêtes et la qualité du plan d'exécution car il est plus petit qu'un index non cluster de table entière et contient des statistiques filtrées. Les statistiques filtrées sont plus précises que les statistiques de table entière car elles couvrent uniquement les lignes de l'index filtré.

  2. Coûts réduits de maintenance des index.

    La maintenance d'un index intervient uniquement lorsque les instructions de langage de manipulation de données (DML) affectent les données de l'index. Un index filtré réduit les coûts de maintenance des index par rapport à un index non cluster de table entière car il est plus petit et sa maintenance n'a lieu que lorsque les données de l'index sont modifiées. Il est possible d'avoir un grand nombre d'index filtrés, notamment s'ils contiennent des données qui sont rarement modifiées. De la même façon, si un index filtré contient uniquement les données fréquemment modifiées, la taille réduite de l'index limite le coût de la mise à jour des statistiques.

  3. Coûts réduits de stockage des index.

    La création d’un index filtré peut réduire le stockage sur disque pour les index non cluster lorsqu’un index de table complète n’est pas nécessaire. Vous pouvez remplacer un index non cluster de table entière par plusieurs index filtrés sans augmenter considérablement le stockage nécessaire.

Remarques relatives à la conception

Lorsqu’une colonne comporte uniquement quelques valeurs pertinentes pour les requêtes, vous pouvez créer un index filtré sur le sous-ensemble de valeurs. L'index ainsi créé sera plus petit et coûtera moins cher en maintenance qu'un index non cluster de table entière défini sur les mêmes colonnes clés.

Par exemple, considérez un index filtré dans les scénarios de données suivants. Dans chaque cas, la WHERE clause de la requête doit être un sous-ensemble de la WHERE clause de l’index filtré, pour tirer parti de l’index filtré.

  • Lorsque les valeurs d’une colonne sont principalement NULL et que la requête effectue uniquement des sélections dans les valeurs non NULL. Vous pouvez créer un index filtré pour les lignes de données non NULL.
  • Lorsque les lignes d’une table sont marquées comme traitées par un workflow récurrent ou un processus de file d’attente. Au fil du temps, la plupart des lignes de la table sont marquées comme traitées. Un index filtré sur les lignes qui ne sont pas encore traitées profiterait à la requête périodique qui recherche les lignes qui ne sont pas encore traitées.
  • Lorsqu’une table a des lignes de données hétérogènes. Vous pouvez créer un index filtré pour une ou plusieurs catégories de données. Ceci peut améliorer les performances des requêtes sur ces lignes de données en limitant la portée d'une requête à une région spécifique de la table. En outre, l'index ainsi créé sera plus petit et coûtera moins cher en maintenance qu'un index non cluster de table entière.

Limitations et restrictions

  • Vous ne pouvez pas créer d’index filtré sur une vue. Toutefois, l'optimiseur de requête peut tirer parti d'un index filtré défini sur une table référencée dans une vue. L'optimiseur de requête prend en considération un index filtré pour une requête qui effectue des sélections dans une vue si les résultats de la requête sont corrects.

  • Vous ne pouvez pas créer d’index filtré sur une table lorsque la colonne accessible dans l’expression de filtre est d’un type de données CLR.

  • Les index filtrés présentent les avantages suivants par rapport aux vues indexées :

    • Coûts réduits de maintenance des index. Par exemple, le processeur de requêtes utilise moins de ressources processeur pour mettre à jour un index filtré qu'une vue indexée.

    • Qualité de plan améliorée. Par exemple, lors de la compilation de la requête, l'optimiseur de requête envisage beaucoup plus souvent d'utiliser un index filtré que la vue indexée équivalente.

    • Reconstructions d'index en ligne. Vous pouvez reconstruire des index filtrés alors qu'ils sont disponibles pour les requêtes. Les reconstructions d’index en ligne ne sont pas prises en charge pour les vues indexées. Pour plus d’informations, consultez l’option REBUILD ALTER INDEX (Transact-SQL).

    • Index non uniques. Les index filtrés peuvent être non uniques, alors que les vues indexées doivent être uniques.

  • Les index filtrés sont définis sur une seule table et ne prennent en charge que les opérateurs de comparaison simples. Si vous avez besoin d'une expression de filtre qui référence plusieurs tables ou présente une logique complexe, vous devez créer une vue. Les index filtrés ne prennent pas en charge LIKE les opérateurs.

  • Une colonne de l’expression d’index filtrée n’a pas besoin d’être une clé ou une colonne incluse dans la définition d’index filtrée si l’expression d’index filtrée équivaut au prédicat de requête et que la requête ne retourne pas la colonne dans l’expression d’index filtrée avec les résultats de la requête.

  • Une colonne dans l’expression d’index filtrée doit être une clé ou une colonne incluse dans la définition d’index filtrée si le prédicat de requête utilise la colonne dans une comparaison qui n’est pas équivalente à l’expression d’index filtrée.

  • Une colonne de l'expression d'index filtré doit être une colonne clé ou incluse dans la définition de l'index filtré si la colonne se trouve dans le jeu de résultats de la requête.

  • La clé d’index cluster de la table n’a pas besoin d’être une clé ou une colonne incluse dans la définition d’index filtrée. La clé de l'index cluster est automatiquement incluse dans tous les index non cluster, y compris les index filtrés. Pour en savoir plus, consultez le guide de conception et d’architecture des index.

  • Si l'opérateur de comparaison spécifié dans l'expression d'index filtré de l'index filtré provoque une conversion de données implicite ou explicite, une erreur se produit si cette conversion se produit du côté gauche d'un opérateur de comparaison. Une solution consiste à écrire l’expression d’index filtrée avec l’opérateur de conversion de données (CAST ou CONVERT) sur le côté droit de l’opérateur de comparaison.

  • Passez en revue les options requises SET pour la création d’index filtré dans la syntaxe CREATE INDEX (Transact-SQL)

  • Les filtres ne peuvent pas être appliqués à la clé primaire ou aux contraintes uniques, mais peuvent être appliqués aux index avec la UNIQUE propriété.

  • Vous ne pouvez pas créer d’index filtré sur une colonne calculée.

Autorisations

Nécessite une autorisation ALTER sur la table ou la vue. L’utilisateur doit être membre du rôle serveur fixe sysadmin ou des rôles de base de données fixes db_ddladmin et db_owner. Pour modifier l’expression d’index filtré, utilisez CREATE INDEX WITH DROP_EXISTING.

Créer un index filtré avec SSMS

  1. Dans l'Explorateur d'objets, sélectionnez le signe plus (+) pour développer la base de données qui contient la table sur laquelle vous souhaitez créer un index filtré.

  2. Sélectionnez le signe plus (+) pour développer le dossier Tables.

  3. Sélectionnez le signe plus (+) pour développer la table sur laquelle vous souhaitez créer un index filtré.

  4. Cliquez avec le bouton droit sur le dossier Index, pointez sur Nouvel index, puis sélectionnez Index non cluster....

  5. Dans la boîte de dialogue Nouvel index , sur la page Général , entrez le nom du nouvel index dans la zone Nom de l'index .

  6. Sous Colonnes de clés d’index, sélectionnez Ajouter....

  7. Dans la boîte de dialogue Sélectionnez les colonnes à partir denom_table , cochez la ou les cases correspondant à la ou aux colonnes de table à ajouter à l’index.

  8. Cliquez sur OK.

  9. Dans la page Filtre, sous Expression de filtre, entrez l’expression SQL que vous utiliserez pour créer l’index filtré.

  10. Cliquez sur OK.

Créer un index filtré avec Transact-SQL

Cet article requiert l'échantillon de base de données AdventureWorks, que vous pouvez télécharger à partir de la page d'accueil des AdventureWorks2022échantillons et Projects de communautés Microsoft SQL Server.

  1. Dans l' Explorateur d'objets, connectez-vous à une instance du Moteur de base de données.

  2. Dans la barre d’outils standard, sélectionnez Nouvelle requête.

  3. Copiez et collez l’exemple suivant dans la fenêtre de requête, puis sélectionnez Exécuter.

USE AdventureWorks2022;
GO

DROP INDEX IF EXISTS FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials
GO

CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials (ComponentID, StartDate)
    WHERE EndDate IS NOT NULL ;
GO

L'index filtré FIBillOfMaterialsWithEndDate est valide pour la requête suivante. Vous pouvez afficher le plan d’exécution de requête pour déterminer si l’optimiseur de requête a utilisé l’index filtré.

USE AdventureWorks2022;
GO

SELECT ProductAssemblyID, ComponentID, StartDate
FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL
    AND ComponentID = 5
    AND StartDate > '01/01/2008' ;
GO

Étapes suivantes

Pour en savoir plus sur la création d’index et les concepts connexes, consultez les articles suivants :