Partager via

  • Article

Index avec colonnes incluses

Vous pouvez étendre la fonctionnalité des index non cluster en ajoutant des colonnes non-clés au niveau du nœud terminal de l'index non cluster. 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 de clés d'index ou de la taille de la clé d'index.

Un index contenant des colonnes non-clés incluses 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]

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

Alors que les colonnes clés sont stockées à tous les niveaux de l'index, les colonnes non-clés sont stockées uniquement au niveau feuille. Pour plus d'informations sur les niveaux d'index, consultez Organisation des tables et des index.

Utilisation des colonnes incluses pour éviter les limites de taille

Vous pouvez inclure des colonnes non-clés dans un index non-cluster pour éviter de dépasser les limitations actuelles de taille d'index, établies à 16 colonnes clés au maximum et une taille de clé d'index de 900 octets au maximum. Le Moteur de base de données ne tient pas compte des colonnes non-clés lors du calcul du nombre de colonnes de clés d'index ou de la taille de la clé d'index.

Par exemple, supposons que vous voulez indexer les colonnes suivantes de la table Document de l'exemple de base de données AdventureWorks :

     Title nvarchar(50)

     Revision nchar(5)

     FileName nvarchar(400)

Comme les types de données nchar et nvarchar nécessitent deux octets par caractère, un index qui contient ces trois colonnes dépasse de 10 octets (455 * 2) la limitation de taille de 900 octets. En utilisant la clause INCLUDE de l'instruction CREATE INDEX, la clé d'index peut être définie en tant que (Title, Revision) et FileName en tant que colonne non-clé. De cette manière, la taille de la clé d'index vaut 110 octets (55 * 2) et l'index contient toujours toutes les colonnes requises. L'instruction ci-dessous crée cet index.

USE AdventureWorks;
GO
CREATE INDEX IX_Document_Title       
ON Production.Document (Title, Revision)       
INCLUDE (FileName);

Directives sur les index contenant des colonnes incluses

Lors de la conception d'index non-cluster contenant des colonnes incluses, tenez compte des directives suivantes :

  • Les colonnes non-clés sont définies dans la clause INCLUDE de l'instruction CREATE INDEX.

  • Les colonnes non-clés peuvent être définies uniquement sur les index non-cluster de tables ou de vues indexées.

  • Tous les types de données sont autorisés sauf text, ntext et image.

  • Les colonnes calculées qui sont déterministes et précises ou imprécises peuvent être des colonnes incluses. Pour plus d'informations, consultez Création d'index sur des colonnes calculées.

  • Comme pour les colonnes clés, les colonnes calculées dérivées des types de données image, ntext et text peuvent être des colonnes non-clés (incluses) tant que le type de données de la colonne calculée est autorisé en tant que colonne d'index non-clé.

  • Les noms des colonnes ne peuvent pas être spécifiés à la fois dans la liste INCLUDE et dans la liste des colonnes clés.

  • Les noms des colonnes ne peuvent pas être répétés dans la liste INCLUDE.

Directives sur la taille des colonnes

  • Vous devez spécifier au moins une colonne clé. Le nombre maximal de colonnes non-clés est de 1023. Il équivaut au nombre maximal de colonnes de table moins 1.

  • Les colonnes de clés d'index, colonnes non-clés exclues, doivent respecter les restrictions existantes de taille d'index, à savoir 16 colonnes clés au maximum et une taille totale de clé d'index de 900 octets.

  • La taille totale de toutes les colonnes non-clés est limitée uniquement par la taille des colonnes spécifiées dans la clause INCLUDE ; par exemple, les colonnes varchar(max) sont limitées à 2 Go.

Directives sur la modification des colonnes

Lors de la définition d'une colonne de table définie en tant que colonne incluse, les restrictions suivantes s'appliquent :

  • Les colonnes non-clés ne peuvent pas être supprimées de la table, sauf si l'index 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, nvarchar ou varbinary.

      [!REMARQUE]

      Ces restrictions sur la modification des colonnes s'appliquent également aux colonnes de clés d'index.

Recommandations relatives à la conception

La conception d'index non-cluster doit être réalisée avec une clé d'index de grande taille, de sorte que seules les colonnes utilisées pour la recherche sont les colonnes clés. Toutes les autres colonnes qui couvrent la requête doivent être des colonnes non-clés incluses. 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.

Par exemple, supposons que vous voulez concevoir un index qui couvre la requête ci-dessous.

USE AdventureWorks;
GO
SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address
WHERE PostalCode BETWEEN N'98000' and N'99999';

Pour couvrir la requête, chaque colonne doit être définie dans l'index. Même si vous pouviez définir toutes les colonnes en tant que colonnes clés, la taille de clé serait 334 octets. Comme la seule colonne vraiment utilisée comme critère de recherche est la colonne PostalCode, dont la longueur vaut 30 octets, une meilleure conception d'index définirait PostalCode comme colonne clé et inclurait toutes les autres colonnes comme colonnes non-clés.

L'instruction suivante crée un index contenant des colonnes incluses pour couvrir la requête.

USE AdventureWorks;
GO
CREATE INDEX IX_Address_PostalCode       
ON Person.Address (PostalCode)       
INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);

Considérations relatives aux performances

Évitez d'ajouter des colonnes superflues. L'ajout de trop nombreuses colonnes d'index, clés et non-clés, peut avoir les conséquences suivantes sur les performances :

  • Le nombre de lignes d'index contenues sur une page sera moindre. Ceci pourrait augmenter les E/S et réduire l'efficacité de la mémoire cache.

  • L'espace disque requis pour stocker l'index sera supérieur. En particulier, l'ajout des types de données varchar(max), nvarchar(max), varbinary(max) ou xml en tant que colonnes d'index non-clés peut accroître considérablement l'espace disque requis. En effet, les valeurs des colonnes sont copiées dans le niveau feuille de l'index. Par conséquent, elles résident à la fois dans l'index et dans la table de base.

  • La maintenance d'un index peut accroître la durée nécessaire pour effectuer des modifications, des insertions, des mises à jour ou des suppressions à la table sous-jacente ou à la vue indexée.

Vous devez déterminer si les gains de performances des requêtes compensent la dégradation des performances lors de la modification des données et la quantité d'espace disque supplémentaire nécessaire. Pour plus d'informations sur l'évaluation des performances des requêtes, consultez Analyse de requêtes.