Configuration des options d'index

Lorsque vous concevez, créez ou modifiez un index, plusieurs options sont à prendre en considération. Ces options peuvent être spécifiées au moment de la création d'un index ou lors de sa reconstruction. De plus, vous pouvez définir certaines options d'index à n'importe quel moment au moyen de la clause SET de l'instruction ALTER INDEX.

Option d'index

Description

Paramètre stocké dans les métadonnées

Rubrique connexe

PAD_INDEX

Définit le pourcentage d'espace libre dans les pages de niveau intermédiaire pendant la création de l'index.

Oui

Taux de remplissage (FILLFACTOR)

FILLFACTOR

Définit le pourcentage d'espace libre dans les pages de niveau feuille de chaque page d'index pendant la création de l'index.

Oui

Taux de remplissage (FILLFACTOR)

SORT_IN_TEMPDB

Détermine si les résultats de tri intermédiaires générés pendant la création de l'index doivent être stockés.

Lorsque l'option est activée (ON), les résultats du tri sont stockés dans la base de données tempdb. Lorsque l'option est désactivée (OFF), les résultats de tri sont stockés dans le même schéma de groupe de fichiers ou de partition que l'index crée.

RemarqueRemarque
Si l'opération de tri n'est pas nécessaire ou ne peut avoir lieu dans la mémoire, l'option SORT_IN_TEMPDB est ignorée.

Non

tempdb et création d'index

IGNORE_DUP_KEY

Spécifie la réponse d'erreur lorsqu'une opération d'insertion essaie d'insérer des valeurs de clé en double dans un index unique. L'option IGNORE_DUP_KEY s'applique uniquement aux opérations d'insertion après la création ou la régénération de l'index. La valeur par défaut est OFF.

Oui

CREATE INDEX

STATISTICS_NORECOMPUTE

Indique si les statistiques d'index périmées doivent être recalculées automatiquement ou pas.

Oui

Statistiques d'index

DROP_EXISTING

Indique que l'index existant doit être supprimé et recréé.

Non

Réorganisation et reconstruction d'index

ONLINE

Détermine si plusieurs utilisateurs peuvent accéder simultanément à la table sous-jacente ou aux données d'index cluster, ainsi qu'aux index non cluster associés pendant les opérations d'index.

RemarqueRemarque
Les opérations d'index en ligne ne sont disponibles que dans les éditions Enterprise, Developer et Evaluation de SQL Server.

Non

Exécution d'opérations d'index en ligne.

ALLOW_ROW_LOCKS

Détermine si les verrous de ligne sont utilisés lors de l'accès aux données de l'index.

Oui

CREATE INDEX

Personnalisation du verrouillage pour un index

ALLOW_PAGE_LOCKS

Détermine si les verrous de page sont utilisés lors de l'accès aux données de l'index.

Oui

CREATE INDEX

Personnalisation du verrouillage pour un index

MAXDOP

Définit le nombre maximal de processeurs que le processeur de requêtes peut utiliser pour exécuter une instruction d'index. Un nombre moins élevé de processeurs peut être utilisé en fonction de la charge de travail système actuelle.

RemarqueRemarque
Les opérations d'index parallèles ne sont disponibles que dans les éditions Enterprise, Developer et Evaluation de SQL Server.

Non

Modification des index

DATA_COMPRESSION

Spécifie l'option de compression de données pour la table, le numéro de partition ou la plage de partitions spécifiés. Les options sont NONE, ROW et PAGE.

Oui

Création de tables et d'index compressés

Pour définir des options pour un index

Définition d'options sans reconstruction

La clause SET de l'instruction ALTER INDEX vous permet de définir les options d'index suivantes sans reconstruire l'index :

  • ALLOW_PAGE_LOCKS

  • ALLOW_ROW_LOCKS

  • IGNORE_DUP_KEY

  • STATISTICS_NORECOMPUTE

Ces options sont immédiatement appliquées à l'index. D'autres options d'index, comme FILLFACTOR et ONLINE, doivent être spécifiées obligatoirement au moment où l'index est créé ou reconstruit.

Affichage des valeurs des options d'index

Toutes les valeurs des options d'index ne sont pas stockées dans les métadonnées. Les valeurs qui le sont peuvent être affichées au moyen des vues de catalogue correspondantes. Pour examiner les valeurs actuelles des options d'index pour les index actuels, utilisez la vue de catalogue sys.indexes. Pour examiner la valeur actuelle de STATISTICS_NORECOMPUTE, utilisez la vue de catalogue sys.stats. Pour plus d'informations, consultez Affichage des informations sur les index.

Exemple

L'exemple suivant définit les options ALLOW_ROW_LOCKS et IGNORE_DUP_KEY pour l'index AK_Product_ProductNumber de la table Production.Product.

USE AdventureWorks;
GO
--Verify the current values for these options.
SELECT allow_row_locks, ignore_dup_key
FROM sys.indexes 
WHERE name = N'AK_Product_ProductNumber';
GO
--Set the ALLOW_ROW_LOCKS option OFF and the IGNORE_DUP_KEY option ON.
ALTER INDEX AK_Product_ProductNumber       
ON Production.Product       
SET (ALLOW_ROW_LOCKS = OFF, IGNORE_DUP_KEY = ON);
GO
--Verify the new values for these options.
SELECT allow_row_locks, ignore_dup_key
FROM sys.indexes 
WHERE name = N'AK_Product_ProductNumber';
GO