ALTER INDEX (Transact-SQL)

  • Article

Modifie une table ou un index de vue (relationnel ou XML) en désactivant, en reconstruisant ou en réorganisant l'index d'une part, ou en définissant les options portant sur l'index d'autre part.

Icône Lien de rubrique Conventions de la syntaxe Transact-SQL

Syntaxe

ALTER INDEX { index_name | ALL }
    ON <object>
    { REBUILD 
        [ [PARTITION = ALL]
          [ WITH ( <rebuild_index_option> [ ,...n ] ) ] 
          | [ PARTITION = partition_number 
                [ WITH ( <single_partition_rebuild_index_option>
                        [ ,...n ] )
                ] 
            ]
        ]
    | DISABLE
    | REORGANIZE 
        [ PARTITION = partition_number ]
        [ WITH ( LOB_COMPACTION = { ON | OFF } ) ]
  | SET ( <set_index_option> [ ,...n ] ) 
    }
[ ; ]

<object> ::=
{
    [ database_name. [ schema_name ] . | schema_name. ]
    table_or_view_name
}

<rebuild_index_option > ::=
{
    PAD_INDEX = { ON | OFF }
  | FILLFACTOR = fillfactor 
  | SORT_IN_TEMPDB = { ON | OFF }
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | ONLINE = { ON | OFF } 
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE | ROW | PAGE } 
     [ ON PARTITIONS ( { <partition_number_expression> | <range> } 
     [ , ...n ] ) ]
}
<range> ::= 
<partition_number_expression> TO <partition_number_expression>
}

<single_partition_rebuild_index_option> ::=
{
    SORT_IN_TEMPDB = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE | ROW | PAGE } }
}

<set_index_option>::=
{
    ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
}

Arguments

  • index_name
    Nom de l'index. Les noms d'index doivent être uniques dans une table ou une vue, mais ne doivent pas être nécessairement uniques dans une base de données. Les noms d'index doivent se conformer aux règles régissant les identificateurs.

  • ALL
    Indique tous les index associés à une table ou à une vue indépendamment du type d'index. L'ajout de l'option ALL provoque un échec de l'instruction si des index se trouvent dans un groupe de fichiers hors connexion ou en lecture seule ou si l'opération spécifiée n'est pas autorisée sur certains types d'index. Le tableau suivant répertorie les types d'opérations ainsi que les types d'index non autorisés.

    Spécification de l'option ALL avec cette opération

    Entraîne un échec si la table possède des

    REBUILD WITH ONLINE = ON

    Index XML

    Index spatial

    REBUILD PARTITION = partition_number

    Index non partitionné, index XML, index spatial ou index désactivé

    REORGANIZE

    Index pour lesquels ALLOW_PAGE_LOCKS est défini sur OFF

    REORGANIZE PARTITION = partition_number

    Index non partitionné, index XML, index spatial ou index désactivé

    IGNORE_DUP_KEY = ON

    Index spatial

    Index XML

    ONLINE = ON

    Index spatial

    Index XML
    AttentionAttention

    Pour plus d'informations sur les opérations d'index pouvant être effectuées en ligne, consultez Instructions pour les opérations d'index en ligne.

    Si ALL est spécifié avec PARTITION = partition_number, tous les index doivent être alignés. Ceci revient à les partitionner d'après des fonctions de partitionnement équivalentes. L'utilisation conjointe de ALL avec PARTITION entraîne la reconstruction ou la réorganisation de toutes les partitions d'index portant la même valeur de partition_number. Pour plus d'informations sur les index partitionnés, consultez Tables et index partitionnés.

  • database_name
    Nom de la base de données.

  • schema_name
    Nom du schéma auquel appartient la vue ou la table.

  • table_or_view_name
    Nom de la table ou de la vue associée à l'index. Pour afficher un rapport des index relatifs à un objet, utilisez l'affichage catalogue sys.indexes.

  • REBUILD [ WITH (<rebuild_index_option> [ ,... n]) ]
    Index à reconstruire d'après les mêmes colonnes, le même type d'index, le même attribut assurant son unicité ainsi que le même ordre de tri. Cette clause équivaut à DBCC DBREINDEX. REBUILD permet de réactiver un index désactivé. La reconstruction portant sur un index cluster n'entraîne pas celle des index non cluster associés, à moins que le mot clé ALL ne soit spécifié. Si les options propres aux index ne sont pas indiquées, leurs valeurs existantes stockées dans sys.indexes sont celles qui prévalent dans ce cas. Si une valeur venait à ne pas se trouver dans sys.indexes, celle indiquée par défaut dans la définition de l'argument de l'option s'applique alors.

    Lorsque vous reconstruisez un index XML ou un index spatial, les options ONLINE = ON et IGNORE_DUP_KEY = ON ne sont pas valides.

    Si l'option ALL est indiquée et que la table sous-jacente correspond à un segment, l'opération de reconstruction n'a alors aucun effet sur la table. Tous les index non cluster associés à la table sont donc reconstruits.

    L'opération de reconstruction peut être consignée dans un journal au minimum si le mode de récupération de base de données est défini sur Utilisant les journaux de transactions ou sur Simple.

    [!REMARQUE]

    Si vous reconstruisez un index XML primaire, la table utilisateur sous-jacente devient indisponible pour toute la durée de l'opération d'indexation.

  • PARTITION
    Indique que seule une partition d'un index doit être reconstruite ou réorganisée. PARTITION ne peut pas être spécifié si index_name n'est pas un index partitionné.

    PARTITION = ALL reconstruit toutes les partitions.

    AttentionAttention

    La création et la reconstruction des index non alignés sur une table contenant plus de 1 000 partitions sont possibles, mais ne sont pas prises en charge. Ces opérations peuvent entraîner une dégradation des performances ou une consommation de mémoire excessive. Nous vous recommandons d'utiliser uniquement des index alignés lorsque le nombre de partitions est supérieur à 1000.

  • partition_number
    Numéro de partition d'un index partitionné à reconstruire ou à réorganiser. partition_number est une expression de constante qui peut référencer des variables. Cela inclut les variables définies par l'utilisateur ou les fonctions et les fonctions définies par l'utilisateur, mais exclut l'instruction Transact-SQL. partition_number doit exister, sinon l'instruction échoue.

  • WITH (<single_partition_rebuild_index_option>)
    SORT_IN_TEMPDB, MAXDOP et DATA_COMPRESSION sont les options qui peuvent être spécifiées lors de la reconstruction d'une partition unique (PARTITION = n). Les index XML ne peuvent pas être indiqués dans une opération de reconstruction d'une seule partition.

    La reconstruction d'un index partitionné ne peut pas être effectuée en ligne. La totalité de la table est verrouillée pendant cette opération.

  • DISABLE
    Marque l'index comme désactivé et non disponible pour être utilisé par le Moteur de base de données. Tout index peut être désactivé. La définition d'un index désactivé est conservé dans le catalogue système sans y inclure de données sous-jacentes. Désactiver un index cluster permet d'éviter l'accès aux données de la table sous-jacente par les utilisateurs. Pour activer un index, utilisez ALTER INDEX REBUILD ou CREATE INDEX WITH DROP_EXISTING. Pour plus d'informations, consultez Désactiver les index et contraintes et Activer les index et contraintes.

  • REORGANIZE
    Indique que le niveau de feuille de l'index va être réorganisé. L'instruction ALTER INDEX REORGANIZE est toujours exécutée en ligne. En d'autres termes, les verrous de tables bloquants à long terme ne sont pas conservés, ce qui permet aux requêtes et aux mises à jour de la table sous-jacente de se poursuivre pendant la transaction ALTER INDEX REORGANIZE. REORGANIZE ne peut pas être indiqué pour un index désactivé ou un index dont ALLOW_PAGE_LOCKS est désactivé (OFF).

  • WITH ( LOB_COMPACTION = { ON | OFF } )
    Indique que toutes les pages qui contiennent des données LOB sont compactées. Les types de données LOB sont les suivants : image, text, ntext, varchar(max), nvarchar(max), varbinary(max) et xml. Compacter ces données contribue à améliorer l'utilisation de l'espace disque. La valeur par défaut est ON.

    • ON
      Toutes les pages qui contiennent des données d'objet volumineux sont compactées.

      La réorganisation d'un index cluster spécifié compacte toutes les colonnes LOB contenues dans l'index cluster. La réorganisation d'un index non cluster compacte toutes les colonnes LOB qui sont des colonnes non-clés (incluses) dans l'index.

      Si ALL est indiqué, tous les index associés à la table ou à la vue spécifiée sont réorganisés ; toutes les colonnes LOB associés à l'index cluster, à la table sous-jacente ou à l'index non cluster possédant des colonnes incluses sont compactées.

    • OFF
      Les pages contenant des données d'objet volumineux ne sont pas compactées.

      La valeur OFF n'a aucun effet sur un segment de mémoire.

    La clause LOB_COMPACTION est ignorée si les colonnes LOB sont manquantes.

  • SET ( <set_index option> [ ,... n] )
    Indique des options d'index sans pour autant reconstruire ou réorganiser l'index. SET ne peut pas être spécifié pour un index désactivé.

  • PAD_INDEX = { ON | OFF }
    Indique le remplissage de l'index. La valeur par défaut est OFF.

    • ON
      Le pourcentage d'espace libre indiqué par FILLFACTOR est appliqué aux pages de niveau intermédiaire de l'index. Si FILLFACTOR n'est pas précisé alors que PAD_INDEX est défini sur ON, la valeur du facteur de remplissage stocké dans sys.indexes est utilisée.

    • OFF ou fillfactor n'est pas spécifié
      Les pages de niveau intermédiaire sont remplies jusqu'à la presque totalité de la capacité. Cela laisse suffisamment d'espace libre pour au moins une ligne de la taille maximale que l'index peut occuper, d'après un ensemble de clés sur les pages intermédiaires.

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

  • FILLFACTOR = fillfactor
    Spécifie un pourcentage indiquant le taux de remplissage appliqué par le moteur de base de données au niveau feuille de chaque page d'index lors de la création ou de la modification de l'index. fillfactor doit être une valeur entière comprise entre 1 et 100. La valeur par défaut est 0.

    [!REMARQUE]

    Les taux de remplissage 0 et 100 sont identiques en tous points.

    Un paramètre FILLFACTOR explicite ne s'applique que lors de la première création ou reconstruction de l'index. Le Moteur de base de données ne conserve pas dynamiquement dans les pages le pourcentage d'espace libre défini. Pour plus d'informations, consultez CREATE INDEX (Transact-SQL).

    Pour afficher le paramètre du facteur de remplissage, utilisez sys.indexes.

    Important

    La création ou la modification d'un index cluster avec une valeur de FILLFACTOR affecte la quantité de l'espace de stockage occupé par les données, car le Moteur de base de données redistribue les données lorsqu'il crée l'index en question.

  • SORT_IN_TEMPDB = { ON | OFF }
    Indique si les résultats du tri doivent être stockés dans tempdb. La valeur par défaut est OFF.

    • ON
      Les résultats de tri intermédiaires utilisés pour créer l'index sont stockés dans tempdb. Si tempdb ne se trouve pas sur le même groupe de disques que la base de données utilisateur, le temps nécessaire à la création de l'index peut être réduit. Toutefois, une plus grande quantité d'espace disque est alors utilisée lors de la création de l'index.

    • OFF
      Les résultats de tri intermédiaires sont stockés dans la même base de données que l'index.

    Si une opération de tri n'est pas requise ou si le tri peut s'effectuer en mémoire, l'option SORT_IN_TEMPDB est ignorée.

    Pour plus d'informations, consultez Option SORT_IN_TEMPDB pour les index.

  • IGNORE_DUP_KEY = { ON | OFF }
    Spécifie la réponse d'erreur lorsqu'une opération d'insertion essaie d'insérer des valeurs de clés 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.

    • ON
      Un message d'avertissement s'affichera lorsque des valeurs de clé en double sont insérées dans un index unique. Seules les lignes qui violent la contrainte d'unicité échoueront.

    • OFF
      Un message d'erreur s'affichera lorsque des valeurs de clé en double sont insérées dans un index unique. L'intégralité de l'opération INSERT sera restaurée.

    IGNORE_DUP_KEY ne peut pas être activé (ON) dans le cas d'index créés sur une vue, d'index non uniques, d'index XML, d'index spatiaux et d'index filtrés.

    Pour afficher IGNORE_DUP_KEY, utilisez sys.indexes.

    Dans la syntaxe de compatibilité descendante, WITH IGNORE_DUP_KEY est équivalent à WITH IGNORE_DUP_KEY = ON.

  • STATISTICS_NORECOMPUTE = { ON | OFF }
    Spécifie si les statistiques de distribution sont recalculées. La valeur par défaut est OFF.

    • ON
      Les statistiques obsolètes ne sont pas recalculées automatiquement.

    • OFF
      La mise à jour automatique des statistiques est activée.

    Pour restaurer la mise à jour automatique des statistiques, affectez la valeur OFF à STATISTICS_NORECOMPUTE, ou exécutez UPDATE STATISTICS sans la clause NORECOMPUTE.

    Important

    Si vous désactivez le recalcul automatique des statistiques de distribution, il se peut que cela empêche l'optimiseur de requêtes de choisir les plans d'exécution optimaux pour les requêtes impliquant la table.

  • ONLINE = { ON | OFF }
    Spécifie si les tables sous-jacentes et les index associés sont disponibles pour modifier des requêtes et des données pendant l'opération d'index. La valeur par défaut est OFF.

    Pour un index XML ou un index spatial, seul ONLINE = OFF est pris en charge et si ONLINE a la valeur ON, une erreur est générée.

    [!REMARQUE]

    Les opérations d'index en ligne ne sont pas disponibles dans toutes les éditions de Microsoft SQL Server. Pour obtenir une liste des fonctionnalités prises en charge par les éditions de SQL Server, consultez Fonctionnalités prises en charge par les éditions de SQL Server 2012.

    • ON
      Les verrous de table à long terme ne sont pas maintenus pendant la durée de l'opération d'index. Lors de la principale phase de l'indexation, seul le verrou de partage intentionnel (IS, Intent Share) est maintenu sur la table source. Cela permet aux requêtes ou aux mises à jour effectuées dans la table et les index sous-jacents de continuer. Au début de l'opération, un verrou partagé (S, Shared) est très brièvement maintenu sur l'objet source. À la fin de l'opération, un verrou partagé (S) est très brièvement maintenu sur la source si un index non cluster est en cours de création ou bien un verrou SCH-M (Modification du schéma) est acquis lorsqu'un index cluster est créé ou supprimé en ligne ou lorsqu'un index cluster ou non cluster est en cours de reconstruction. ONLINE ne peut pas prendre la valeur ON si un index est en cours de création sur une table locale temporaire.

    • OFF
      Des verrous de table sont appliqués pendant l'opération d'indexation. Une opération d'index hors connexion qui crée, reconstruit ou supprime un index cluster, spatial ou XML, ou qui reconstruit ou supprime un index non cluster, acquiert un verrou Sch-M (Modification du schéma) sur la table. Cela empêche tous les utilisateurs d'accéder à la table sous-jacente pendant la durée de l'opération. Une opération d'indexation hors ligne qui crée un index non-cluster acquiert un verrou partagé (S, Shared) sur la table. Cela empêche la mise à jour de la table sous-jacente, mais autorise les opérations de lecture, telles que des instructions SELECT.

    Pour plus d'informations, consultez Fonctionnement des opérations d'index en ligne.

    Les index, notamment les index portant sur des tables temporaires globales, ne peuvent pas être reconstruits en ligne, à l'exception des index suivants :

    • Index XML

    • les index portant sur des tables temporaires locales ;

    • sous-ensemble d'un index partitionné (un index partitionné entier peut être reconstruit en ligne) ;

  • ALLOW_ROW_LOCKS = { ON | OFF }
    Indique si les verrous de ligne sont autorisés. La valeur par défaut est ON.

    • ON
      Les verrous de ligne sont autorisés lors de l'accès à l'index. Le Moteur de base de données détermine à quel moment les verrous de ligne sont utilisés.

    • OFF
      Les verrous de ligne ne sont pas utilisés.

  • ALLOW_PAGE_LOCKS = { ON | OFF }
    Spécifie si les verrous de page sont autorisés. La valeur par défaut est ON.

    • ON
      Les verrous de page sont autorisés lors de l'accès à l'index. Le Moteur de base de données détermine à quel moment les verrous de page sont utilisés.

    • OFF
      Les verrous de page ne sont pas utilisés.

    [!REMARQUE]

    Un index ne peut pas être réorganisé lorsque ALLOW_PAGE_LOCKS est désactivé (OFF).

  • MAXDOP **=**max_degree_of_parallelism
    Prévaut sur l'option de configuration Max Degree of Parallelism pendant la durée de l'opération d'indexation. Pour plus d'informations, consultez Configurer l'option de configuration du serveur Degré maximal de parallélisme. Utilisez MAXDOP pour limiter le nombre de processeurs utilisés dans une exécution de plans parallèles. Le nombre maximal de processeurs est égal à 64.

    Important

    Bien que l'option MAXDOP soit prise en charge syntaxiquement pour tous les index XML, pour un index spatial ou un XML primaire, ALTER INDEX utilise actuellement seulement un processeur unique.

    Valeurs possibles de max_degree_of_parallelism :

    • 1
      Supprime la création du plan d'exécution parallèle.

    • >1
      Limite au nombre spécifié le nombre maximal de processeurs utilisés dans le traitement en parallèle des index.

    • 0 (valeur par défaut)
      Utilise le nombre réel de processeurs ou un nombre de processeurs inférieur en fonction de la charge de travail actuelle du système.

    Pour plus d'informations, consultez Configurer des opérations d'index parallèles.

    [!REMARQUE]

    Les opérations d'index parallèles ne sont pas disponibles dans toutes les éditions de Microsoft SQL Server. Pour obtenir une liste des fonctionnalités prises en charge par les éditions de SQL Server, consultez Fonctionnalités prises en charge par les éditions de SQL Server 2012.

  • DATA_COMPRESSION
    Spécifie l'option de compression de données pour l'index, le numéro de partition ou la plage de partitions spécifiés. Les options disponibles sont les suivantes :

    • NONE
      L'index ou les partitions spécifiées ne sont pas compressés.

    • ROW
      L'index ou les partitions spécifiées sont compressés au moyen de la compression de ligne.

    • PAGE
      L'index ou les partitions spécifiées sont compressés au moyen de la compression de page.

    Pour plus d'informations sur la compression, consultez Compression de données.

  • ON PARTITIONS ( { <partition_number_expression> | <plage> } [,...n] )
    Spécifie les partitions auxquelles le paramètre DATA_COMPRESSION s'applique. Si l'index n'est pas partitionné, l'argument ON PARTITIONS générera une erreur. Si la clause ON PARTITIONS n'est pas fournie, l'option DATA_COMPRESSION s'applique à toutes les partitions d'un index partitionné.

    <partition_number_expression> peut être spécifié des manières suivantes :

    • Spécifiez le numéro de partition, par exemple : ON PARTITIONS (2).

    • Spécifiez des numéros de partition pour plusieurs partitions individuelles séparées par des virgules, par exemple : ON PARTITIONS (1, 5).

    • Spécifiez à la fois des plages et des partitions individuelles : ON PARTITIONS (2, 4, 6 TO 8).

    <range> peut être spécifié sous la forme de numéros de partitions séparés par le mot TO, par exemple : ON PARTITIONS (6 TO 8).

    Pour définir des types différents de compression de données pour des partitions différentes, spécifiez plusieurs fois l'option DATA_COMPRESSION, par exemple :

    REBUILD WITH 
(
DATA_COMPRESSION = NONE ON PARTITIONS (1), 
DATA_COMPRESSION = ROW ON PARTITIONS (2, 4, 6 TO 8), 
DATA_COMPRESSION = PAGE ON PARTITIONS (3, 5)
)

Notes

ALTER INDEX ne peut pas être utilisé pour recréer la partition d'un index ou le déplacer vers un autre groupe de fichiers. Cette instruction ne peut pas être utilisée pour modifier la définition de l'index, comme l'ajout ou la suppression de colonnes ou la modification de l'ordre des colonnes. Exécutez CREATE INDEX avec la clause DROP_EXISTING afin de procéder aux opérations suivantes.

Si une option n'est pas spécifiée de façon explicite, le paramètre actuel s'applique. Par exemple, si un paramètre FILLFACTOR n'est pas indiqué dans la clause REBUILD, la valeur de facteur de remplissage stockée dans le catalogue système est utilisée lors du processus de reconstruction. Pour consulter les paramètres d'options d'indexation en cours, utilisez sys.indexes.

[!REMARQUE]

Les valeurs pour les options ONLINE, MAXDOP et SORT_IN_TEMPDB ne sont pas stockées dans le catalogue système. À moins qu'elle ne soit précisée dans l'instruction d'indexation, la valeur par défaut de l'option est alors utilisée.

Sur des ordinateurs multiprocesseurs, comme c'est aussi le cas pour d'autres requêtes, ALTER INDEX REBUILD utilise automatiquement plus de processeurs pour pouvoir procéder aux opérations d'analyse et de tri associées à la modification de l'index. Lors de l'exécution de ALTER INDEX REORGANIZE, que ce soit avec ou sans l'option LOB_COMPACTION, la valeur de Max Degree of Parallelism correspond à une opération mono-thread. Pour plus d'informations, consultez Configurer des opérations d'index parallèles.

Un index ne peut pas être réorganisé ou reconstruit si le groupe de fichiers dans lequel il se trouve est hors connexion ou en lecture seule. Si le mot clé ALL est spécifié et que des index se trouvent dans un groupe de fichiers hors connexion ou en lecture seule, l'instruction échoue.

Reconstruction des index

La reconstruction d'un index entraîne sa suppression puis sa reconstruction. Ceci permet d'éviter toute fragmentation, de libérer de l'espace disque en compactant les pages d'après le paramètre du facteur de remplissage spécifié ou déjà existant et en retriant les lignes de l'index en pages contiguës. Quand ALL est précisé, tous les index sur la table sont supprimés puis reconstruits en une seule transaction. Il n'est pas nécessaire de supprimer les contraintes FOREIGN KEY au préalable. Lorsque de la reconstruction d'index contenant au moins 128 étendues, le Moteur de base de données diffère les désallocations de pages ainsi que les verrous qui y sont associés jusqu'à ce que la transaction soit validée.

Bien souvent, la reconstruction ou la réorganisation de petits index ne réduit pas la fragmentation. Les pages des petits index sont stockées sur des extensions mixtes. Les extensions mixtes sont partagées par huit objets maximum ; par conséquent, la fragmentation dans un petit index peut ne pas être réduite après sa réorganisation ou sa reconstruction.

Dans SQL Server 2012, les statistiques ne sont pas créées en analysant toutes les lignes de la table lorsqu'un index partitionné est créé ou reconstruit. Au lieu de cela, l'optimiseur de requête utilise l'algorithme d'échantillonnage par défaut pour générer des statistiques. Pour obtenir des statistiques sur les index partitionnés en analysant toutes les lignes de la table, utilisez CREATE STATISTICS ou UPDATE STATISTICS avec la clause FULLSCAN.

Dans les versions précédentes de SQL Server, vous aviez parfois la possibilité de reconstruire un index non cluster afin de corriger les incohérences dues à des défaillances matérielles. Dans SQL Server 2008 et les versions ultérieures, vous pouvez toujours réparer de telles incohérences entre l'index et l'index cluster en reconstruisant un index non cluster hors connexion. Toutefois, vous ne pouvez pas réparer les incohérences d'un index non cluster en reconstruisant l'index en ligne. En effet, le mécanisme de reconstruction en ligne utilise l'index non cluster existant comme base pour la reconstruction et propage de ce fait l'incohérence. Par contre, la reconstruction de l'index hors connexion impose une analyse de l'index cluster (ou segments de mémoire) et élimine donc l'incohérence. Comme pour les versions précédentes, nous vous recommandons d'éliminer les incohérences en restaurant les données concernées à partir d'une sauvegarde. Toutefois, il est possible que vous puissiez réparer les incohérences d'un index en reconstruisant l'index non cluster hors connexion. Pour plus d'informations, consultez DBCC CHECKDB (Transact-SQL).

Réorganisation d'index

La réorganisation d'un index utilise des ressources système minimes. En effet, elle défragmente le niveau feuille des index cluster et non cluster sur les tables et les vues en retriant les pages de niveau feuille de façon physique afin de resuivre l'ordre logique, c'est-à-dire de gauche à droite, des nœuds. Cette opération compacte également les pages d'index. Le compactage s'appuie sur la valeur du facteur de remplissage existante. Pour afficher le paramètre du facteur de remplissage, utilisez sys.indexes.

Si ALL est précisé, les index relationnels, aussi bien cluster que non cluster, et les index XML sur la table sont réorganisés. Des restrictions s'appliquent néanmoins si vous utilisez l'option ALL ; consultez sa définition dans la section Arguments.

Pour plus d'informations, consultez Réorganiser et reconstruire des index.

Désactivation d'index

Désactiver un index permet d'éviter l'accès à l'index, et dans le cas d'index cluster, aux données de la table sous-jacente par les utilisateurs. La définition de l'index est conservée dans le catalogue système. Désactiver un index, qu'il soit non cluster ou cluster, sur une vue supprime physiquement les données de l'index. Désactiver un index cluster permet d'éviter l'accès aux données mais celles-ci ne sont plus mises à jour dans l'arborescence binaire (appelé également arbre B) jusqu'à ce que l'index soit supprimé ou reconstruit. Pour afficher l'état d'un index, qu'il soit activé ou désactivé, lancez une requête sur la colonne is_disabled dans l'affichage catalogue sys.indexes.

Si une table se trouve dans une publication de réplication transactionnelle, vous ne pouvez pas désactiver d'index associés à des colonnes clés primaires. Ces index sont requis par la réplication. Pour désactiver un index, vous devez d'abord supprimer la table de la publication. Pour plus d'informations, consultez Publier des données et des objets de base de données.

Pour activer l'index, utilisez l'instruction ALTER INDEX REBUILD ou CREATE INDEX WITH DROP_EXISTING. La reconstruction d'un index cluster désactivé ne peut être effectuée si l'option ONLINE est activée (ON). Pour plus d'informations, consultez Désactiver les index et contraintes.

Configuration des options

Vous pouvez définir les options ALLOW_ROW_LOCKS, ALLOW_PAGE_LOCKS, IGNORE_DUP_KEY et STATISTICS_NORECOMPUTE pour un index précis sans pour autant avoir à le reconstruire ou le réorganiser. Les valeurs modifiées sont immédiatement appliquées à l'index. Pour afficher ces paramètres, utilisez sys.indexes. Pour plus d'informations, consultez Définir les options d'index.

Options de verrous de ligne et de page

Si ALLOW_ROW_LOCKS = ON et ALLOW_PAGE_LOCK = ON, les verrous de ligne, de page et de table sont autorisés lorsque vous accédez à l'index. Le Moteur de base de données choisit le verrou approprié et peut promouvoir un verrou de ligne ou de page en verrou de table.

Si ALLOW_ROW_LOCKS = OFF et ALLOW_PAGE_LOCK = OFF, seul un verrou au niveau des tables est autorisé si vous accédez à l'index.

Si ALL est indiqué lors de la définition des options de verrouillage de ligne ou de page, les paramètres s'appliquent à tous les index. Si la table sous-jacente correspond à un segment de mémoire, les paramètres s'appliquent des façons suivantes :

ALLOW_ROW_LOCKS = ON ou OFF

S'applique au segment de mémoire et à tout index non cluster qui lui est associé.

ALLOW_PAGE_LOCKS = ON

S'applique au segment de mémoire et à tout index non cluster qui lui est associé.

ALLOW_PAGE_LOCKS = OFF

Verrou entier pour les index non cluster. En d'autres termes, tous les verrous de page ne sont pas autorisés sur les index non cluster. En ce qui concerne le segment de mémoire, seul les verrous partagé (P), de mise à jour (M) et exclusifs (E) ne sont pas autorisés. Le Moteur de base de données peut toujours acquérir un verrou de page intentionnel (IS, IU ou IX) à des fins internes.

Opérations d'index en ligne

Si vous reconstruisez un index et que l'option ONLINE est activée (ON), les objets, les tables et les index associés sous-jacents sont disponibles pour permettre les requêtes et la modification de données. Les verrous de tables exclusifs ne sont maintenus que pour une durée courte lors du processus de modification.

La réorganisation d'un index s'effectue toujours en ligne. Elle ne conserve pas les verrous à long terme et ne bloque pas ainsi les requêtes ou les mises à jour en cours d'exécution.

Vous pouvez lancer des opérations d'indexation en ligne simultanées sur une même table mais uniquement dans les cas suivants :

  • création d'index non cluster multiples ;

  • réorganisation de différents index sur une même table ;

  • réorganisation de différents index lors de la reconstruction d'index ne se chevauchant pas et portant sur une même table.

Toute autre opération d'indexation en ligne effectuée en même temps qu'une autre entraîne un échec de l'opération. Par exemple, vous ne pouvez pas reconstruire de façon concurrente plusieurs index portant sur une même table ou créer d'index lors de la reconstruction d'un index existant portant sur la même table.

Pour plus d'informations, consultez Exécuter des opérations en ligne sur les index.

Restrictions des index spatiaux

Lorsque vous reconstruisez un index spatial, la table utilisateur sous-jacente est indisponible pour toute la durée de l'opération d'index car l'index spatial détient un verrou de schéma.

La contrainte PRIMARY KEY dans la table utilisateur ne peut pas être modifiée alors qu'un index spatial est défini sur une colonne de cette table. Pour modifier la contrainte PRIMARY KEY, commencez par supprimer chaque index spatial de la table. Après avoir modifié la contrainte PRIMARY KEY, vous pouvez recréer chaque index spatial.

Dans une opération individuelle de reconstruction de partition, vous ne pouvez pas spécifier d'index spatial. Toutefois, vous pouvez spécifier des index spatiaux dans une reconstruction de partition complète.

Pour modifier des options spécifiques à un index spatial, telles que BOUNDING_BOX ou GRID, vous pouvez utiliser une instruction CREATE SPATIAL INDEX qui spécifie DROP_EXISTING = ON ou supprimer l'index spatial et en créer un nouveau. Pour un exemple, consultez CREATE SPATIAL INDEX (Transact-SQL).

Restrictions concernant les index columnstore

Excepté pour l'option REBUILD, un index columnstore optimisé en mémoire xVelocity ne peut pas être modifié. Supprimez et recréez plutôt l'index columnstore.

Compression de données

Pour plus d'informations sur la compression de données, consultez Compression de données.

Pour évaluer la façon dont la modification de l'état de compression affecte une table, un index ou une partition, utilisez la procédure stockée sp_estimate_data_compression_savings.

Les restrictions suivantes s'appliquent aux index partitionnés :

  • Lorsque vous utilisez ALTER INDEX ALL ...,, vous ne pouvez pas modifier le paramètre de compression d'une partition unique si la table a des index non alignés.

  • La syntaxe ALTER INDEX <index> ... REBUILD PARTITION ... reconstruit la partition spécifiée de l'index.

  • La syntaxe ALTER INDEX <index> ... REBUILD WITH ... reconstruit toutes les partitions de l'index.

Statistiques

Lorsque vous exécutez ALTER INDEX ALL … sur une table, seules les statistiques associées aux index sont mises à jour. Les statistiques automatiques ou manuelles créées sur la table (au lieu d'un index) ne sont pas mises à jour.

Autorisations

Pour pouvoir exécuter l'instruction ALTER INDEX, vous devez obligatoirement bénéficier au minimum d'autorisations nécessaires pour exécuter les instructions ALTER sur la table ou la vue.

Exemples

A. Reconstruction d'un index

L'exemple suivant reconstruit un seul index portant sur la table Employee.

USE AdventureWorks2012;
GO
ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD;
GO

B. Reconstruction de tous les index d'une table et indication des options

L'exemple suivant indique le mot clé ALL. Ceci permet de reconstruire tous les index associés à la table. Trois options sont spécifiées.

USE AdventureWorks2012;
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON);
GO

C. Réorganisation d'un index avec compactage LOB

L'exemple suivant réorganise un index cluster unique. L'index contenant un type de données LOB au niveau de la feuille, l'instruction compacte par la même occasion toutes les pages contenant les données LOB. Notez que l'indication de l'option WITH (LOB_COMPACTION) n'est pas nécessaire car la valeur par défaut est ON.

USE AdventureWorks2012;
GO
ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto
REORGANIZE ;
GO

D. Définition des options d'un index

L'exemple suivant définit plusieurs options sur l'index AK_SalesOrderHeader_SalesOrderNumber.

USE AdventureWorks2012;
GO
ALTER INDEX AK_SalesOrderHeader_SalesOrderNumber ON
    Sales.SalesOrderHeader
SET (
    STATISTICS_NORECOMPUTE = ON,
    IGNORE_DUP_KEY = ON,
    ALLOW_PAGE_LOCKS = ON
    ) ;
GO

E. Désactivation d'un index

L'exemple suivant désactive un index non cluster sur la table Employee.

USE AdventureWorks2012;
GO
ALTER INDEX IX_Employee_OrganizationNode ON HumanResources.Employee
DISABLE ;
GO

F. Désactivation des contraintes

L'exemple suivant désactive une contrainte PRIMARY KEY en désactivant l'index PRIMARY KEY. La contrainte FOREIGN KEY portant sur la table sous-jacente est automatiquement désactivée et un avertissement s'affiche.

USE AdventureWorks2012;
GO
ALTER INDEX PK_Department_DepartmentID ON HumanResources.Department
DISABLE ;
GO

Le jeu de résultats retourne l'avertissement suivant.

Warning: Foreign key 'FK_EmployeeDepartmentHistory_Department_DepartmentID'

on table 'EmployeeDepartmentHistory' referencing table 'Department'

was disabled as a result of disabling the index 'PK_Department_DepartmentID'.

G. Activation des contraintes

L'exemple suivant active les contraintes PRIMARY KEY et FOREIGN KEY désactivées dans l'exemple F.

La contrainte PRIMARY KEY est activée lors de la reconstruction de l'index PRIMARY KEY.

USE AdventureWorks2012;
GO
ALTER INDEX PK_Department_DepartmentID ON HumanResources.Department
REBUILD ;
GO

La contrainte FOREIGN KEY est ensuite activée.

ALTER TABLE HumanResources.EmployeeDepartmentHistory
CHECK CONSTRAINT FK_EmployeeDepartmentHistory_Department_DepartmentID;
GO

H. Reconstruction d'un index partitionné

L'exemple suivant reconstruit une seule partition, celle portant le numéro de partition 5, de l'index partitionné IX_TransactionHistory_TransactionDate.

USE AdventureWorks;
GO
-- Verify the partitioned indexes.
SELECT *
FROM sys.dm_db_index_physical_stats (DB_ID(),OBJECT_ID(N'Production.TransactionHistory'), NULL , NULL, NULL);
GO
--Rebuild only partition 5.
ALTER INDEX IX_TransactionHistory_TransactionDate
ON Production.TransactionHistory
REBUILD Partition = 5;
GO

I. Modification du paramètre de compression d'un index

L'exemple suivant reconstruit un index sur une table non partitionnée.

ALTER INDEX IX_INDEX1 
ON T1
REBUILD 
WITH ( DATA_COMPRESSION = PAGE )
GO

Pour obtenir d'autres exemples de compression de données, consultez Compression de données.

Voir aussi

Référence

CREATE INDEX (Transact-SQL)

CREATE SPATIAL INDEX (Transact-SQL)

CREATE XML INDEX (Transact-SQL)

DROP INDEX (Transact-SQL)

sys.dm_db_index_physical_stats (Transact-SQL)

EVENTDATA (Transact-SQL)

Concepts

Désactiver les index et contraintes

Index XML (SQL Server)

Exécuter des opérations en ligne sur les index

Réorganiser et reconstruire des index