Créer des index filtrés

  • Article

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

Cet article décrit la création d’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 des index non-cluster lorsqu'un index de table entière 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.

Considérations sur la conception

Lorsqu'une colonne ne contient que quelques valeurs pertinentes pour les requêtes, vous pouvez créer un index filtré sur ce 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 clause WHERE de la requête doit être un sous-ensemble de la clause WHERE de l’index filtré, afin de profiter 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 seront marquées comme traitées. Un index filtré sur les lignes qui n’ont pas encore été traitées profiterait à la requête récurrente, car celle-ci rechercherait des lignes qui se présentent sous forme non 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 un index filtré sur une fenêtre. 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 un index filtré sur une table lorsque la colonne à laquelle vous accédez 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 régénérations d’index connectés pour les vues indexées ne sont pas prises en charge. Pour plus d’informations, consultez l’option REBUILD pour 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 les opérateurs LIKE.

  • Il n'est pas nécessaire qu'une colonne de l'expression d'index filtré soit une colonne clé ou incluse dans la définition de l'index filtré si l'expression d'index filtré est équivalente au prédicat de requête et si la requête ne retourne pas la colonne dans l'expression d'index filtré avec les résultats de la requête.

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

  • 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.

  • Il n'est pas nécessaire que la clé de l'index cluster de la table soit une colonne clé ou incluse dans la définition de l'index filtré. 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. L’une des solutions consiste à écrire l’expression de l’index filtré avec l’opérateur de conversion de données (CAST ou CONVERT) situé à droite de l’opérateur de comparaison.

  • Examinez les options SET requises pour la création d’un index filtré dans la syntaxe CRÉER UN INDEX (Transact-SQL)

  • Les filtres ne peuvent pas être appliqués aux contraintes de clé primaire ou d'unicité. En revanche, ils peuvent être appliqués aux index avec la propriété UNIQUE.

  • Vous ne pouvez pas créer un 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électionner des colonnes dans nom_table, cochez les cases correspondant 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

Les exemples de code Transact-SQL de cet article sont fondés sur l’échantillon de base de données AdventureWorks2022 ou AdventureWorksDW2022 fourni, que vous pouvez télécharger à partir de la page d’accueil Échantillons et projets communautaires 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 :