Créer des index filtrés

S’applique à : ouiSQL Server (toutes les versions prises en charge) OuiAzure SQL Database OuiAzure SQL Managed Instance ouiAzure Synapse Analytics OuiParallel Data Warehouse

Cet article explique comment créer un index filtré avec SQL Server Management Studio (SSMS) ou 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.

Remarques relatives à la conception

Lorsqu'une colonne contient seulement un petit nombre de 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 l’index filtré doit être un sous-ensemble de la clause WHERE d’une requête 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 majorité des lignes de la table sont marquées comme traitées. Un index filtré sur les lignes qui n’ont pas encore été traitées peut tirer parti de la requête récurrente qui recherche les lignes qui n’ont pas encore été 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 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 un index filtré sur une table quand la colonne à laquelle l’expression de filtre accède 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 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. Une solution consiste à écrire l'expression d'index filtré avec l'opérateur de conversion de données (CAST ou CONVERT) à droite de l'opérateur de comparaison.

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

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

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. Sélectionnez OK.

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

  10. Sélectionnez OK.

Créer un index filtré avec Transact-SQL

Cet exemple utilise la base de données AdventureWorks2019, disponible en téléchargement sur Exemples de bases de données AdventureWorks.

  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 AdventureWorks2019;  
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 AdventureWorks2019;  
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 :