Création d'index (Moteur de base de données)

  • Article

Cette rubrique décrit les principales tâches de création d'index et propose des instructions relatives à la mise en œuvre et aux performances à prendre en compte avant de créer un index.

Tâches de création d'index

Les tâches ci-dessous constituent la stratégie recommandée pour la création d'index :

  1. Conception de l'index
    La conception d'un index est une tâche critique. Elle nécessite l'identification des colonnes à utiliser, la sélection du type d'index (par exemple, cluster ou non-cluster) et des options d'index appropriées, ainsi que la détermination du placement du groupe de fichiers et du schéma de partition. Pour plus d'informations, consultez Conception d'index.
  2. Identification de la meilleure méthode de création. La création d'un index s'effectue de plusieurs façons :
    • En définissant une contrainte PRIMARY ou UNIQUE KEY sur une colonne avec CREATE TABLE ou ALTER TABLE.
      Le Moteur de base de données SQL Server 2005 crée automatiquement un index unique pour appliquer l'impératif d'unicité de la contrainte PRIMARY KEY ou UNIQUE. Par défaut, un index cluster unique est créé pour appliquer la contrainte PRIMARY KEY, à moins qu'il existe déjà un index cluster sur la table ou si vous spécifiez un index non-cluster unique. Par défaut, un index non-cluster unique est créé pour appliquer la contrainte UNIQUE, à moins qu'un index cluster unique soit spécifié explicitement et qu'il n'existe pas d'index cluster sur la table.
      Des options d'index et l'emplacement de l'index, le groupe de fichiers ou le schéma de partition, peuvent également être spécifiés.
      Un index créé dans le cadre d'une contrainte PRIMARY KEY ou UNIQUE se voit automatiquement attribuer le même nom que la contrainte. Pour plus d'informations, consultez Contraintes PRIMARY KEY et Contraintes UNIQUE.
    • En créant un index indépendant d'une contrainte à l'aide de l'instruction CREATE INDEX ou de la boîte de dialogue Nouvel index dans l'Explorateur d'objets de SQL Server Management Studio.
      Vous devez spécifier le nom de l'index, ainsi que celui de la table et des colonnes auxquelles il s'applique. Des options d'index et l'emplacement de l'index, le groupe de fichiers ou le schéma de partition, peuvent également être spécifiés. Par défaut, un index non-cluster non unique est créé si les options cluster ou unique ne sont pas précisées.
  3. Création de l'index
    Le fait que la table sur laquelle sera créé l'index est vide ou contient des données est un facteur qu'il est important de prendre en compte. La création d'un index sur une table vide n'a pas de répercussions en termes de performances au moment de la création. Toutefois, les performances seront affectées par la suite lorsque des données seront ajoutées à cette table.
    La création d'index sur des tables volumineuses doit faire l'objet d'une planification rigoureuse afin de ne pas nuire aux performances de la base de données. Dans ce cas, il est préférable de créer d'abord l'index cluster, puis les index non-cluster. Envisagez de paramétrer l'option ONLINE à ON (activé) lors de la création d'index sur les tables existantes. De cette façon, les verrous de table à long terme ne sont pas maintenus, et les requêtes ou les mises à jour portant sur la table sous-jacente continuent d'être exécutées. Pour plus d'informations, consultez Exécution d'opérations en ligne sur les index.

Considérations relatives à la mise en œuvre

Le tableau suivant répertorie les valeurs maximales s'appliquant aux index cluster, non-cluster et XML. Sauf indication contraire, ces limitations s'appliquent à tous les types d'index.

Limites maximales pour l'index Valeur Autres informations

Index cluster par table

1

 

Index non-cluster par table

249

Y compris les index non-cluster créés par des contraintes PRIMARY KEY ou UNIQUE, mais pas les index XML.

Index XML par table

249

Y compris les index XML primaires et secondaires sur les colonnes ayant un type de colonnes XML.

Index portant sur des colonnes de type xml

Nombre de colonnes clés par index

16*

Taille maximale des clés d'index.

L'index cluster est limité à 15 colonnes si la table contient également un index XML primaire.

Taille maximale des enregistrements de clés d'index

900 octets*

Ne s'applique pas aux index XML.

Taille maximale des clés d'index.

*Pour éviter les limitations relatives à la taille des enregistrements et au nombre de colonnes clés pour les index non-cluster, incluez des colonnes non-clé dans l'index. Pour plus d'informations, consultez Index avec colonnes incluses.

Types de données

En règle générale, n'importe quelle colonne d'une table ou d'une vue peut être indexée. Le tableau ci-dessous répertorie les types de données dont la présence dans un index fait l'objet de restrictions.

Type de données Présence dans un index Autres informations

Type CLR défini par l'utilisateur

Peut être indexé si le type prend en charge le tri binaire.

Utilisation de types CLR définis par l'utilisateur

Types de données LOB (Large Object) : image, ntext, text, varchar(max), nvarchar(max), varbinary(max) et xml

Interdits dans les colonnes clés d'index. Toutefois, une colonne xml peut être une colonne clé dans un index XML primaire ou secondaire défini sur une table.

Autorisés pour les colonnes non-clés (incluses) d'un index non-cluster, excepté pour image, ntext et text.

Autorisés s'ils sont inclus dans une expression de colonne calculée.

Index avec colonnes incluses

Index portant sur des colonnes de type xml

Colonnes calculées

Peuvent être indexées. Il peut s'agir de colonnes calculées définies en tant qu'appels de méthode d'une colonne d'un type CLR défini par l'utilisateur, à condition que les méthodes sont marquées comme déterministes.

Les colonnes calculées dérivées de types de données LOB peuvent être indexées comme des colonnes clés ou non-clés, à condition que leur type de données soit autorisé pour les colonnes clés ou non-clés d'index.

Création d'index sur des colonnes calculées

Colonnes Varchar envoyées hors ligne

La clé d'un index cluster ne peut pas contenir de colonnes varchar ayant des données dans l'unité d'allocation ROW_OVERFLOW_DATA. Si un index cluster est créé sur une colonne varchar et que les données existantes se trouvent dans l'unité d'allocation IN_ROW_DATA, les opérations d'insertion ou de mise à jour suivantes sur cette colonne qui enverraient des données hors ligne échoueront.

Organisation des tables et des index

Données de dépassement de ligne de plus de 8 Ko

Considérations supplémentaires

Voici quelques règles supplémentaires à suivre pour créer un index :

  • Vous pouvez créer un index si vous disposez de l'autorisation CONTROL ou ALTER sur la table.
  • Lorsqu'il est créé, l'index est automatiquement activé et disponible. Vous pouvez supprimer l'accès à un index en le désactivant. Pour plus d'informations, consultez Désactivation d'index.

Espace disque requis

La quantité d'espace disque nécessaire pour stocker l'index dépend des facteurs suivants :

Considérations relatives aux performances

La durée nécessaire pour créer physiquement un index dépend pour beaucoup du sous-système de disques. Il est important de tenir compte des facteurs suivants :

  • Le mode de récupération de la base de données. Comparativement au mode de restauration complète, le mode de récupération utilisant les journaux de transaction offre de meilleures performances et réduit la consommation de l'espace de journalisation durant la création d'index. Cependant, il offre moins de souplesse pour la récupération limitée dans le temps. Pour plus d'informations, consultez Choix d'un mode de récupération pour des opérations d'index.
  • Le niveau RAID (Redundant Array of Independent Disks) utilisé pour stocker la base de données et les fichiers journaux des transactions. En général, les niveaux RAID utilisant l'agrégation par bandes offrent une bande passante accrue au niveau des E/S.
  • Le nombre de disques de la batterie de disques (en cas d'utilisation d'un système RAID). Plus le nombre de disques de la batterie est élevé, plus les taux de transfert des données augmentent (proportionnellement).
  • L'emplacement de stockage des tris intermédiaires des données. L'emploi de l'option SORT_IN_TEMPDB peut réduire le temps nécessaire pour créer un index lorsque tempdb ne se trouve pas sur le même ensemble de disques que la base de données utilisateur. Pour plus d'informations, consultez tempdb et création d'index.
  • Création de l'index en ligne ou hors connexion
    Si un index est créé hors connexion (comportement par défaut), des verrous exclusifs sont pris sur la table sous-jacente jusqu'à ce que la transaction qui crée l'index soit terminée. La table est inaccessible aux utilisateurs pendant que l'index est créé.
    Dans SQL Server 2005, vous pouvez spécifier que l'index doit être créé en ligne. Lorsque l'option en ligne est activée (ON), les verrous de table à long terme ne sont pas maintenus, et les requêtes ou les mises à jour portant sur la table sous-jacente continuent d'être exécutées pendant la création de l'index. Bien que nous recommandions les opérations sur les index en ligne, il convient d'évaluer votre environnement et vos besoins spécifiques. Il est parfois préférable d'exécuter les opérations sur les index hors connexion. De cette façon, les utilisateurs disposent d'un accès restreint aux données durant l'opération, mais cette dernière est accomplie plus rapidement et se révèle moins gourmande en ressources. Pour plus d'informations, consultez Exécution d'opérations en ligne sur les index.
Pour créer une contrainte PRIMARY KEY ou UNIQUE lorsque vous créez une table

CREATE TABLE

Pour créer une contrainte PRIMARY KEY ou UNIQUE sur une table existante

ALTER TABLE

Pour créer un index

CREATE INDEX

Voir aussi

Concepts

Création d'index cluster
Création d'index avec colonnes incluses
Création d'index non-cluster
Création d'index uniques
Implémentation de tables et d'index partitionnés
Contraintes PRIMARY KEY
Contraintes UNIQUE
Index portant sur des colonnes de type xml

Autres ressources

ALTER TABLE (Transact-SQL)
CREATE TABLE (Transact-SQL)
Mise en route avec la recherche de texte intégral

Aide et Informations

Assistance sur SQL Server 2005