Créer des index avec colonnes incluses

Cette rubrique explique comment ajouter des colonnes incluses (ou non-clés) pour étendre les fonctionnalités des index non cluster dans SQL Server 2012 à 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]

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

Dans cette rubrique

• Avant de commencer :

  Recommandations relatives à la conception

  Limitations et restrictions

  Sécurité

• Pour créer un index avec des colonnes non-clés, utilisez :

  SQL Server Management Studio

  Transact-SQL

Avant de commencer

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. 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 à 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 clés d'index ou de la taille de la clé d'index.

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 ntext et 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, ntext et 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, nvarchar ou 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.

[Top]

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 de clés d'index, cliquez sur Ajouter….

7. Dans la boîte de dialogue Sélectionnez les colonnes à partir de table_name, activez la ou les cases à cocher de la ou des 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 de table_name, activez la ou les cases à cocher 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.

[Top]

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 AdventureWorks2012;
   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
   

Pour plus d'informations, consultez CREATE INDEX (Transact-SQL).

[Top]