Créer des index avec colonnes incluses

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

Cette rubrique explique comment ajouter des colonnes incluses (ou non-clés) pour étendre les fonctionnalités des index non cluster dans SQL Server à l’aide de SQL Server Management Studio ou de Transact-SQL. L'inclusion de colonnes non-clés permet de créer des index non-cluster qui couvrent davantage de requêtes. En effet, les colonnes non-clés présentent les avantages suivants :

  • Elles peuvent contenir des types de données qui ne sont pas autorisés dans les colonnes de clés d'index.
  • Elles ne sont pas prises en compte par le moteur de base de données lors du calcul du nombre de colonnes clés d’index ou de taille de clé d’index.

Un index contenant des colonnes non-clés peut améliorer considérablement les performances des requêtes lorsque toutes les colonnes de la requête sont incluses dans l'index en tant que colonnes clés ou non-clés. Les gains de performances sont dus au fait que l'optimiseur de requête peut localiser toutes les valeurs des colonnes dans l'index ; l'accès aux données de table et d'index n'a pas lieu, produisant ainsi un nombre moindre d'opérations d'E/S sur le disque.

Note

Quand un index contient toutes les colonnes auxquelles une requête fait référence, on dit qu’il couvre la requête.

Avant de commencer

Recommandations relatives à la conception

  • Refaites la conception des index non-cluster qui ont une clé d’index de grande taille, de sorte que seules les colonnes utilisées pour la recherche soient des colonnes clés. Toutes les autres colonnes qui couvrent la requête doivent être des colonnes non-clés. De cette manière, vous disposez de toutes les colonnes nécessaires pour couvrir la requête, mais la clé d'index elle-même est petite et efficace.

  • Incluez des colonnes non clés dans un index non cluster pour éviter de dépasser les limites de taille d’index actuelles d’un maximum de 32 colonnes clés et une taille maximale de clé d’index de 1 700 octets (16 colonnes clés et 900 octets avant SQL Server 2016 (13.x)). Le moteur de base de données ne prend pas en compte les colonnes non clés lors du calcul du nombre de colonnes clés d’index ou de taille de clé d’index.

  • L’ordre des colonnes non clés dans la définition de l’index n’a pas d’impact sur les performances des requêtes qui utilisent cet index.

  • Évitez les index non cluster très larges où les colonnes incluses ne représentent pas un sous-ensemble suffisamment étroit des colonnes de table sous-jacentes. Si vous ajoutez des index de grande taille, vérifiez toujours si le coût de la mise à jour d’un grand index supplémentaire compense le coût de la lecture directe de la table.

Limitations et restrictions

  • Les colonnes non-clés peuvent uniquement être définies sur des index non cluster.

  • Tous les types de données, à l'exception de text, de ntextet de image , peuvent être utilisés en tant que colonnes non-clés.

  • Les colonnes calculées déterministes et précises ou imprécises peuvent être des colonnes non-clés. Pour plus d'informations, consultez Indexes on Computed Columns.

  • Les colonnes calculées dérivées des types de données image, ntextet text peuvent être des colonnes non-clés tant que le type de données de la colonne calculée est autorisé en tant que colonne d'index non-clé.

  • Les colonnes non-clés ne peuvent pas être supprimées d’une table, sauf si l’index de cette table est d’abord supprimé.

  • Les colonnes non-clés ne peuvent pas être modifiées, sauf pour effectuer les opérations suivantes :

    • modifier la possibilité de valeur NULL de la colonne de NOT NULL à NULL ;

    • augmenter la longueur des colonnes varchar, nvarcharou varbinary .

Sécurité

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 .

Utilisation de SQL Server Management Studio

Pour créer un index avec des colonnes non-clés

  1. Dans l'Explorateur d'objets, cliquez sur le signe plus (+) pour développer la base de données qui contient la table sur laquelle vous souhaitez créer un index avec des colonnes non-clés.

  2. Cliquez sur le signe plus (+) pour développer le dossier Tables .

  3. Cliquez sur le signe plus (+) pour développer la table sur laquelle vous souhaitez créer un index avec des colonnes non-clés.

  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 l’onglet Colonnes clés d’index, cliquez sur 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. Sous l’onglet Colonnes incluses, cliquez sur Ajouter....

  10. Dans la boîte de dialogue Sélectionnez les colonnes à partir denom_table , cochez la ou les cases de la ou des colonnes de table à ajouter à l’index en tant que colonnes non-clés.

  11. Cliquez sur OK.

  12. Dans la boîte de dialogue Nouvel index , cliquez sur OK.

Utilisation de Transact-SQL

Pour créer un index avec des colonnes non-clés

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

  2. Dans la barre d'outils standard, cliquez sur Nouvelle requête.

  3. Copiez et collez l'exemple suivant dans la fenêtre de requête, puis cliquez sur Exécuter.

    USE AdventureWorks2022;  
    GO  
    -- Creates a nonclustered index on the Person.Address table with four included (nonkey) columns.   
    -- index key column is PostalCode and the nonkey columns are  
    -- AddressLine1, AddressLine2, City, and StateProvinceID.  
    CREATE NONCLUSTERED INDEX IX_Address_PostalCode  
    ON Person.Address (PostalCode)  
    INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);  
    GO  
    

CREATE INDEX (Transact-SQL)
Guide de conception d’index SQL Server