Article
03/31/2012

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

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 :

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.
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 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. Pour créer un index filtré, utilisez la clause WHERE facultative. Pour plus d'informations, consultez Règles de conception d'index filtrés.
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, spatiaux, filtrés 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	999	Inclut les index non-cluster créés par des contraintes PRIMARY KEY ou UNIQUE et les index filtrés, mais pas les index XML.
Index XML par table	249	Inclut les index XML primaires et secondaires sur les colonnes ayant un type de colonnes xml. Index sur les colonnes de type de données XML
Index spatiaux par table	249	Utilisation d'index spatiaux (moteur de base de données)
Nombre de colonnes clés par index	16*	L'index cluster est limité à 15 colonnes si la table contient également un index XML primaire ou un index spatial. Taille maximale des clés d'index.
Taille des enregistrements de clés d'index	900 octets*	Non applicable aux index XML ou aux index spatiaux. Pour qu'une table prenne en charge les index spatiaux, la taille d'enregistrement de clé d'index maximale est de 895 octets. 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 d'objet volumineux (LOB) : 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 sur les colonnes de type de données 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
geometry	Peut être indexé avec plusieurs index spatiaux.	Types de données spatiales

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 nécessaire

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

La taille de chaque ligne de données dans la table et le nombre de lignes par page. Ce dernier point détermine le nombre de pages de données devant être lues sur le disque pour la création de l'index.
Les colonnes de l'index et les types de données utilisés. Ce point détermine le nombre de pages d'index devant être écrites sur le disque. Pour plus d'informations, consultez Estimation de la taille d'un index cluster et Estimation de la taille d'un index non-cluster.
L'espace disque temporaire requis durant le processus de création d'index. Pour plus d'informations, consultez Détermination de l'espace disque requis par les index.

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 jusqu'à une date et heure. 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éé.

Hormis pour les index XML et spatiaux, vous pouvez spécifier que l'index soit 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.