ALTER INDEX (Transact-SQL)

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

Conventions de syntaxe Transact-SQL

Syntaxe

ALTER INDEX { index_name | ALL }
    ON <object>
    { REBUILD 
        [ [ 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
}

<single_partition_rebuild_index_option> ::=
{
    SORT_IN_TEMPDB = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
}

<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
  Spécifie tous les index associés à une table ou à une vue indépendamment du type d'index. La spécification de l'argument ALL entraîne l'échec de l'instruction lorsque des index se trouvent dans un groupe de fichiers hors connexion ou en lecture seule ou lorsque l'opération spécifiée n'est pas autorisée sur certains types d'index. Le tableau suivant liste les opérations d'index et les types d'index non autorisés.

  Spécification de l'argument ALL avec cette opération	Entraîne un échec si la table possède des
  REBUILD WITH ONLINE = ON	Index XML Les colonnes de types de données LOB sont les suivantes : image, text, ntext, varchar(max), nvarchar(max), varbinary(max) et xml.
  REBUILD PARTITION = partition_number	Index non partitionné, index XML ou index désactivé
  REORGANIZE	Index dont l'option ALLOW_PAGE_LOCKS a la valeur OFF.
  REORGANIZE PARTITION = partition_number	Index non partitionné, index XML ou index désactivé
  SET	Index désactivé

  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 de ALL avec PARTITION entraîne la reconstruction ou la réorganisation de toutes les partitions d'index ayant la même valeur 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]) ]
  Spécifie l'index à reconstruire avec les mêmes colonnes, le même type d'index, le même attribut d'unicité et le même ordre de tri. Cette clause équivaut à DBCC DBREINDEX. REBUILD réactive un index désactivé. La reconstruction d'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 d'index ne sont pas spécifiées, les valeurs existantes des options d'index stockées dans sys.indexes sont appliquées. Si une valeur venait à manquer dans sys.indexes, celle indiquée par défaut dans la définition de l'argument de l'option est appliquée.

  Les options ONLINE et IGNORE_DUP_KEY ne sont pas valides lorsque vous reconstruisez un index XML.

  Si l'option ALL est indiquée et que la table sous-jacente correspond à un segment de mémoire, 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 faire l'objet d'une journalisation minimale si le mode de récupération de base de données est défini sur Journalisé en bloc ou sur Simple. Pour plus d'informations, consultez Choix d'un mode de récupération pour des opérations d'index.

  Remarque :
  Si vous reconstruisez un index XML primaire, la table utilisateur sous-jacente n'est pas disponible pendant l'opération d'index.

• PARTITION
  Spécifie 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_number
  Numéro de partition d'un index partitionné à reconstruire ou à réorganiser. partition_number est une expression constante qui peut faire référence à des variables. Cela inclut les fonctions et les variables de type défini par l'utilisateur, mais exclut les instructions Transact-SQL. partition_number doit correspondre à une valeur existante, sinon l'instruction échoue.

• WITH (<single_partition_rebuild_index_option>)
  SORT_IN_TEMPDB et MAXDOP sont les options que vous pouvez spécifier lors de la reconstruction d'une seule partition (PARTITION = n). Les index XML ne peuvent pas être spécifié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 table est entièrement verrouillée pendant cette opération.

• DISABLE
  Marque l'index comme étant désactivé ou non disponible en vue d'une utilisation par le moteur de base de données SQL Server 2005. Tout index peut être désactivé. La définition d'un index désactivé est conservée dans le catalogue système sans données d'index sous-jacentes. La désactivation d'un index cluster empêche les utilisateurs d'accéder aux données de la table sous-jacente. Pour activer un index, utilisez ALTER INDEX REBUILD ou CREATE INDEX WITH DROP_EXISTING. Pour plus d'informations, consultez Désactivation d'index.

• REORGANIZE
  Spécifie le niveau feuille de l'index à réorganiser. Cette clause équivaut à DBCC INDEXDEFRAG. L'instruction ALTER INDEX REORGANIZE s'effectue toujours en ligne. En d'autres termes, les verrous de table à long terme ne sont pas conservés, ce qui permet aux requêtes et aux mises à jour de la table sous-jacente de suivre leur cours pendant la transaction ALTER INDEX REORGANIZE. REORGANIZE ne peut pas être spécifié pour un index désactivé ou un index dont ALLOW_PAGE_LOCKS a la valeur OFF.

• WITH ( LOB_COMPACTION = { ON | OFF } )
  Spécifie 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. Le compactage de ces données peut optimiser l'utilisation de l'espace disque. La valeur par défaut est ON.

  • ON
    Toutes les pages qui contiennent des données LOB sont compactées.

    La réorganisation d'un index cluster spécifié compacte toutes les colonnes LOB qui figurent dans l'index cluster. La réorganisation d'un index non-cluster compacte toutes les colonnes LOB qui sont des colonnes non-clés dans l'index. Pour plus d'informations, consultez Création d'index avec colonnes incluses.

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

  • OFF
    Les pages qui contiennent des données LOB 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] )
  Spécifie les 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 qui est spécifié par FILLFACTOR est appliqué aux pages de niveau intermédiaire de l'index. Si FILLFACTOR n'est pas spécifié alors que PAD_INDEX a la valeur ON, la valeur du facteur de remplissage stockée dans sys.indexes est utilisée.
  • OFF ou fillfactor n'est pas spécifié
    Les pages de niveau intermédiaire sont remplies quasiment jusqu'à pleine capacité. Cela laisse suffisamment d'espace libre pour au moins une ligne de taille maximale pouvant être occupée par l'index, déterminée 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 qui indique l'espace occupé par le niveau feuille de chaque page d'index calculé par le moteur de base de données 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 valeurs de facteur de remplissage correspondant à 0 et à 100 sont identiques à tous les niveaux.

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

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

  Important :
  La création ou la modification d'un index cluster avec une valeur FILLFACTOR influe sur la quantité d'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 }
  Spécifie si les résultats du tri doivent être stockés dans tempdb. La valeur par défaut est OFF.

  • ON
    Les résultats intermédiaires du tri qui permettent de créer l'index sont stockés dans tempdb. Si tempdb se trouve sur un ensemble de disques différents de celui où se trouve la base de données utilisateur, le temps nécessaire à la création d'un index peut s'en trouver réduit. Ceci dit, l'espace disque occupé augmente également lors de la construction de l'index.
  • OFF
    Les résultats intermédiaires du tri sont stockés dans la même base de données que l'index.

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

  Pour plus d'informations, consultez tempdb et création d'index.

• IGNORE_DUP_KEY = { ON | OFF }
  Spécifie la réponse d'erreur dans le cas de valeurs de clé en double dans une transaction d'insertion de lignes multiples sur un index cluster ou non-cluster unique. La valeur par défaut est OFF.

  • ON
    Un message d'avertissement est généré et seules les lignes qui ne respectent pas l'index UNIQUE échouent.
  • OFF
    Un message d'erreur est généré et la transaction est entièrement restaurée.

  Le paramètre IGNORE_DUP_KEY s'applique uniquement aux opérations d'insertion qui se produisent après la création ou la reconstruction de l'index. Il n'a aucun effet lors de l'opération d'index. IGNORE_DUP_KEY n'a également aucun effet dans une instruction UPDATE.

  IGNORE_DUP_KEY ne peut pas avoir la valeur ON dans le cas d'index XML et d'index créés sur une vue. Pour plus d'informations, consultez CREATE INDEX (Transact-SQL).

• STATISTICS_NORECOMPUTE = { ON | OFF }
  Spécifie si les statistiques propres à la distribution doivent être 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, cela risque d'empêcher l'optimiseur de requêtes de choisir les meilleurs plans d'exécution 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 les requêtes et la modification de données pendant l'opération d'index. La valeur par défaut est OFF.

  Remarque :
  Les opérations d'index en ligne sont uniquement disponibles dans SQL Server 2005 Enterprise Edition.

  • ON
    Les verrous de table à long terme ne sont pas conservés pendant l'opération d'index. Pendant la principale phase de cette opération, seul un verrou de partage intentionnel est conservé 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é est très brièvement conservé sur l'objet source. À la fin de l'opération, un verrou partagé est très brièvement conservé sur la source si un index non-cluster est en cours de création ou bien un verrou de 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 avoir la valeur ON lorsqu'un index est en cours de création sur une table locale temporaire.
  • OFF
    Les verrous de table sont appliqués pendant l'opération d'index. Une opération d'index hors connexion, qui crée, reconstruit ou supprime un index cluster ou qui reconstruit ou supprime un index non-cluster, acquiert un verrou de modification du schéma sur la table. Cela empêche tous les utilisateurs d'accéder à la table sous-jacente pendant l'opération. Une opération d'index hors connexion qui crée un index non-cluster acquiert un verrou partagé sur la table. Cela empêche les mises à jour de la table sous-jacente, mais permet les opérations de lecture, telles que des instructions SELECT.

  Pour plus d'informations, consultez Fonctionnement des opérations d'index en ligne. Pour plus d'informations sur les verrous, consultez Modes de verrouillage.

  Les index, notamment les index sur des tables temporaires globales, ne peuvent pas être reconstruits en ligne, sauf les index suivants :

  • les index désactivés ;
  • les index XML ;
  • les index sur des tables temporaires locales ;
  • les index partitionnés ;
  • les index cluster si la table sous-jacente contient des types de données LOB ;
  • les index non-cluster qui sont définis avec des colonnes de types de données LOB.

  Les index non-cluster peuvent être reconstruits si la table contient des types de données LOB mais qu'aucune de ces colonnes n'est utilisée dans la définition de l'index sous forme de colonnes clés ou non-clés.

• ALLOW_ROW_LOCKS = { ON | OFF }
  Spécifie si les verrous de ligne sont autorisés ou non. 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 le moment où 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 ou non. 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 le moment où 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 a la valeur OFF.

• MAXDOP = max_degree_of_parallelism
  Remplace l'option de configuration Degré maximal de parallélisme pendant l'opération d'index. Pour plus d'informations, consultez Option max degree of parallelism. Ajoutez MAXDOP pour limiter le nombre de processeurs utilisés lors de l'exécution des plans parallèles. Le nombre maximal de processeurs est 64.

  Valeurs possibles de max_degree_of_parallelism :

  • 1
    Supprime la génération des plans parallèles.
  • >1
    Restreint le nombre maximal de processeurs utilisés dans une opération d'index parallèle à un nombre spécifié.
  • 0 (valeur par défaut)
    Utilise le nombre réel de processeurs ou moins, en fonction de la charge actuelle du système.

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

  Remarque :
  Les opérations d'index parallèles ne sont disponibles que dans SQL Server 2005 Enterprise Edition.

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. Utilisez CREATE INDEX avec la clause DROP_EXISTING pour effectuer les 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 spécifié dans la clause REBUILD, la valeur du facteur de remplissage stockée dans le catalogue système est utilisée lors du processus de reconstruction. Pour afficher les paramètres d'options d'index actuels, utilisez sys.indexes.

Remarque :
Les valeurs de ONLINE, MAXDOP et SORT_IN_TEMPDB ne sont pas stockées dans le catalogue système. Sauf indication contraire dans l'instruction d'index, la valeur par défaut de l'option est 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 LOB_COMPACTION, la valeur de Degré maximal de parallélisme est une opération mono-thread. Pour plus d'informations, consultez Configuration d'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 recréation. 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 existant et de retrier les lignes de l'index en pages contiguës. Quand ALL est spécifié, tous les index sur la table sont supprimés puis reconstruits en une seule opération. Les contraintes FOREIGN KEY n'ont pas à être supprimées au préalable. Lorsque des index contenant 128 étendues ou plus sont reconstruits, le moteur de base de données diffère les désallocations de pages et les verrous qui y sont associés jusqu'à ce que la transaction soit validée. Pour plus d'informations, consultez Suppression et reconstruction d'objets volumineux.

Remarque :

En général, la reconstruction ou la réorganisation des 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 au plus ; par conséquent, la fragmentation dans un petit index peut ne pas être réduite après la réorganisation ou la reconstruction de l'index. Pour plus d'informations sur les extensions mixtes, consultez Pages et extensions.

Dans les précédentes versions de SQL Server, vous aviez parfois la possibilité de reconstruire un index non-cluster pour corriger les incohérences dues à des défaillances matérielles. Dans SQL Server 2005, vous pouvez toujours réparer de telles incohérences entre l'index et l'index cluster en reconstruisant un index non-cluster hors connexion. Vous ne pouvez toutefois 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. En revanche, 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. Il est toutefois 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 suivre 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 spécifié, les index relationnels, aussi bien cluster que non-cluster, et les index XML sur la table sont réorganisés. Certaines restrictions sont applicables lorsque vous spécifiez ALL ; consultez sa définition dans la section Arguments.

Pour plus d'informations, consultez Réorganisation et reconstruction d'index.

Désactivation d'index

La désactivation d'un index permet d'empêcher les utilisateurs d'accéder à l'index, et dans le cas d'index cluster, aux données de la table sous-jacente. La définition de l'index est conservée dans le catalogue système. La désactivation d'un index, qu'il soit non-cluster ou cluster, sur une vue supprime physiquement les données de l'index. La désactivation d'un index cluster permet d'empêcher 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 de clé primaire. Ces index sont nécessaires à la réplication. Pour désactiver un index, vous devez d'abord supprimer la table de la publication. Pour plus d'informations, consultez Publication de données et d'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 a la valeur ON. Pour plus d'informations, consultez Désactivation d'index.

Définition 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 ni à 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 Configuration des options d'index.

Options des 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 convertir 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é lorsque vous accédez à l'index. Pour plus d'informations sur la configuration de la granularité de verrouillage d'un index, consultez Personnalisation du verrouillage pour un index.

Si ALL est spécifié lors de la définition des options de verrous de ligne ou de page, les paramètres s'appliquent à tous les index. Si la table sous-jacente est un segment de mémoire, les paramètres s'appliquent aux éléments suivants :

ALLOW_ROW_LOCKS = ON ou OFF	Au segment de mémoire et à tout index non-cluster qui y est associé.
ALLOW_PAGE_LOCKS = ON	Au segment de mémoire et à tout index non-cluster qui y est associé.
ALLOW_PAGE_LOCKS = OFF	Entièrement aux 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 de page partagés, de mise à jour et exclusifs sont interdits. Le moteur de base de données peut toujours acquérir un verrou de page intentionnel (partagé, de mise à jour ou exclusif) pour sa gestion interne.

Pour plus d'informations, consultez Promotion de verrous (moteur de base de données).

Opérations d'index en ligne

Si vous reconstruisez un index et que l'option ONLINE a la valeur ON, les objets sous-jacents, les tables et les index associés sont disponibles pour les requêtes et la modification de données. Les verrous de table exclusifs ne sont conservés que pendant une courte durée 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'index en ligne simultanément sur une même table, mais uniquement dans les cas suivants :

• création de plusieurs index non-cluster ;
• 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'index 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 simultanément plusieurs index sur une même table ni créer d'index lors de la reconstruction d'un index existant sur la même table.

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

Autorisations

Pour exécuter ALTER INDEX, vous devez au minimum posséder les 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 index unique sur la table Employee.

USE AdventureWorks;
GO
ALTER INDEX PK_Employee_EmployeeID ON HumanResources.Employee
REBUILD;
GO

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

L'exemple suivant spécifie le mot clé ALL. Tous les index associés à la table sont ainsi reconstruits. Trois options sont spécifiées.

USE AdventureWorks;
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. Comme l'index contient un type de données LOB au niveau feuille, l'instruction compacte par la même occasion toutes les pages qui contiennent des données LOB. Notez que la spécification de l'option WITH (LOB_COMPACTION) n'est pas nécessaire, car la valeur par défaut est ON.

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

D. Paramétrage des options d'un index

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

USE AdventureWorks;
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 AdventureWorks;
GO
ALTER INDEX IX_Employee_ManagerID 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 sur la table sous-jacente est automatiquement désactivée et un message d'avertissement s'affiche.

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

L'ensemble de résultats retourne le présent avertissement.

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 ré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 AdventureWorks;
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. Cet exemple part du principe que l'échantillon de l'index partitionné a été installé. Pour plus d'informations sur son installation, consultez Readme_PartitioningScript.

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

Voir aussi

Référence

CREATE INDEX (Transact-SQL)
sys.dm_db_index_physical_stats
EVENTDATA (Transact-SQL)

Autres ressources

Désactivation d'index
Index portant sur des colonnes de type xml
Exécution d'opérations en ligne sur les index
Réorganisation et reconstruction d'index

Aide et Informations

Assistance sur SQL Server 2005