ALTER INDEX (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 Types de colonnes d'objet volumineux : image, text, ntext, varchar(max), nvarchar(max), varbinary(max) et xml
  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

  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 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 ê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. 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 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.

• partition_number
  Numéro de partition d'un index partitionné à reconstruire ou à réorganiser. partition_number correspond à une expression constante pouvant faire référence à 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ésactivation d'index.

• REORGANIZE
  Indique que le niveau feuille de l'index va être réorganisé. Cette clause équivaut à DBCC INDEXDEFRAG. 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 incluses dans l'index. Pour plus d'informations, consultez Création d'index avec colonnes incluses.

    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 facteurs 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 destinés à la création de 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 tempdb et création d'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é 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. Cette option n'a aucun effet lors de l'exécution de CREATE INDEX, ALTER INDEX ou de UPDATE. 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é échouent.

  • 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 }
  Indique 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.

  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 disponibles que dans les éditions Enterprise, Developer et Evaluation de SQL Server.

  • 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 être défini sur 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. Ceci 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. Ceci 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. Pour plus d'informations sur les verrous, consultez Modes de verrouillage.

  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) ;

  • les index cluster si la table sous-jacente contient des données de type LOB ;

  • les index non cluster définis avec des colonnes dont les données sont de type LOB.

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

• ALLOW_ROW_LOCKS = { ON | OFF }
  Indique 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 }
  Indique 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 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 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 Option Degré maximum de parallélisme. Utilisez MAXDOP pour limiter le nombre de processeurs à utiliser dans l'exécution des plans parallèles. Il peut y en avoir jusqu'à 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.

  max_degree_of_parallelism peut être :

  • 1
    Supprime la génération de plans parallèles.

  • >1
    Restreint le nombre de processeurs maximal utilisés dans une opération d'indexation parallèle à un nombre spécifié.

  • 0 (valeur par défaut)
    Utilise le nombre de processeurs effectivement mis en œuvre ou un nombre inférieur d'après la charge de travail 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 les éditions Enterprise, Developer et Evaluation de SQL Server.

• 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 Création de tables et d'index compressés.

• 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 d'une 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.

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 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

Réorganisation d'index

Désactivation d'index

Définition des options

Opérations d'indexation en ligne

Restrictions des index spatiaux

Compression de données

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

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

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

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

D. Définition des options d'un index

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

USE AdventureWorks;
GO
ALTER INDEX IX_Employee_ManagerID ON HumanResources.Employee
DISABLE ;
GO

F. Désactivation des contraintes

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

G. Activation des contraintes

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

ALTER TABLE HumanResources.EmployeeDepartmentHistory
CHECK CONSTRAINT FK_EmployeeDepartmentHistory_Department_DepartmentID;
GO

H. Reconstruction d'un index partitionné

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

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

Historique des modifications

Voir aussi

Référence

Concepts