Partage via

Créer des index avec colonnes incluses

  • Article

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

Cet article 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 la taille de la 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.

Remarque

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

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 les colonnes non-clés dans un index non cluster pour éviter de dépasser les limitations actuelles de taille d’index, établies à 32 colonnes clés au maximum et à une taille de clé d’index maximale 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 tient pas compte des colonnes non-clés lors du calcul du nombre de colonnes clés d'index ou de la taille de la 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 de très grande taille dans lesquels les colonnes incluses ne représentent pas un sous-ensemble suffisamment précis des colonnes de la table sous-jacente. 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 Index sur les colonnes calculées.

  • 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. Sélectionnez 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 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. Sous l’onglet Colonnes incluses, sélectionnez 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, 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
-- 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

Contenu connexe