Partage via

ALTER TABLE index_option (Transact-SQL)

  • Article

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Spécifie un ensemble d’options applicables à un index qui fait partie d’une définition de contrainte créée à l’aide d’ALTER TABLE.

Pour obtenir une description complète des options d’index, consultez CREATE INDEX.

Conventions de la syntaxe Transact-SQL

Syntaxe

{
    PAD_INDEX = { ON | OFF }
  | FILLFACTOR = fillfactor
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }
  | SORT_IN_TEMPDB = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE }
      [ ON PARTITIONS ( { <partition_number_expression> | <range> }
      [ , ...n ] ) ]
  | XML_COMPRESSION = { ON | OFF }
      [ ON PARTITIONS ( { <partition_number_expression> | <range> }
      [ , ...n ] ) ]
  | ONLINE = { ON | OFF }
  | RESUMABLE = { ON | OFF }
  | MAX_DURATION = <time> [ MINUTES ]
}

<range> ::=
<partition_number_expression> TO <partition_number_expression>

<single_partition_rebuild__option> ::=
{
    SORT_IN_TEMPDB = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE } }
  | ONLINE = { ON [ ( <low_priority_lock_wait> ) ] | OFF }
}

<low_priority_lock_wait>::=
{
    WAIT_AT_LOW_PRIORITY ( MAX_DURATION = <time> [ MINUTES ] ,
                           ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS } )
}

Remarque

Pour afficher la syntaxe Transact-SQL pour SQL Server 2014 (12.x) et versions antérieures, consultez la Documentation sur les versions antérieures.

Arguments

PAD_INDEX = { ON | OFF }

S’applique à : SQL Server 2008 (10.0.x) et versions ultérieures

Spécifie le remplissage de l'index. Par défaut, il s’agit de OFF.

  • ACTIVÉ

    Le pourcentage d’espace libre indiqué par FILLFACTOR est appliqué aux pages du niveau intermédiaire de l’index.

  • OFF ou fillfactor n’est pas spécifié

    Les pages de niveau intermédiaire sont remplies jusqu'à la presque totalité de la capacité, laissant suffisamment d'espace pour au moins une ligne de la taille maximale possible de l'index, compte tenu du jeu de clés des pages intermédiaires.

FILLFACTOR = fillfactor

S’applique à : SQL Server 2008 (10.0.x) et versions ultérieures

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. La valeur spécifiée doit être un entier compris entre 1 et 100. La valeur par défaut est 0.

Notes

Les facteurs de remplissage de valeur 0 et 100 sont en tout point identiques.

IGNORE_DUP_KEY = { ON | OFF }

Spécifie le type de réponse quand 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. Cette option n’a aucun effet lors de l’exécution de CREATE INDEX, d’ALTER INDEX ou d’UPDATE. Par défaut, il s’agit de OFF.

  • ACTIVÉ

    Un message d’avertissement s’affiche quand 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

    Une erreur s’affiche quand des valeurs de clé en double sont insérées dans un index unique. INSERT Toute l’opération est restaurée.

IGNORE_DUP_KEY ne peut pas être défini ON pour les index créés sur une vue, des index non uniques, des index XML, des index spatiaux et des index filtrés.

Pour voir IGNORE_DUP_KEY, utilisez sys.indexes.

Dans la syntaxe à compatibilité descendante, WITH IGNORE_DUP_KEY équivaut à WITH IGNORE_DUP_KEY = ON.

STATISTICS_NORECOMPUTE = { ON | OFF }

Désactivez ou activez l’option de mise à jour automatique des statistiques, AUTO_STATISTICS_UPDATEpour les statistiques associées aux index spécifiés. Par défaut, il s’agit de OFF.

  • ACTIVÉ

    Les mises à jour automatiques des statistiques sont désactivées après la reconstruction de l’index.

  • OFF

    Les mises à jour automatiques des statistiques sont activées après la reconstruction de l’index.

Pour restaurer la mise à jour automatique des statistiques, définissez la STATISTICS_NORECOMPUTEOFFvaleur ou exécutez UPDATE STATISTICS sans la NORECOMPUTE clause.

Avertissement

Si vous désactivez la mise à jour automatique des statistiques, cela peut empêcher l’optimiseur de requête de choisir des plans d’exécution optimaux pour les requêtes qui impliquent la table. Vous devez utiliser cette option avec parcimonie et uniquement par un administrateur de base de données qualifié.

Ce paramètre n’empêche pas une mise à jour automatique avec l’analyse complète des statistiques liées à l’index, pendant l’opération de reconstruction.

ALLOW_ROW_LOCKS = { ON | OFF }

S’applique à : SQL Server 2008 (10.0.x) et versions ultérieures

Indique si les verrous de ligne sont autorisés ou non. La valeur par défaut est ON.

  • ACTIVÉ

    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 }

S’applique à : SQL Server 2008 (10.0.x) et versions ultérieures

Indique si les verrous de page sont autorisés. La valeur par défaut est ON.

  • ACTIVÉ

    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.

OPTIMIZE_FOR_SEQUENTIAL_KEY = {ON | OFF }

S’applique à : SQL Server 2019 (15.x) et versions ultérieures

Spécifie s’il faut optimiser ou pas la contention d’insertion de la dernière page. Par défaut, il s’agit de OFF. Pour plus d’informations, consultez la section Clés séquentielles de l’article CREATE INDEX .

SORT_IN_TEMPDB = { ON | OFF }

S’applique à : SQL Server 2008 (10.0.x) et versions ultérieures

Spécifie s’il faut stocker les résultats de tri dans tempdb. Par défaut, il s’agit de OFF.

  • ACTIVÉ

    Les résultats de tri intermédiaires utilisés pour générer l’index sont stockés dans tempdb. Cela peut réduire le temps nécessaire pour créer un index s’il tempdb se trouve sur un ensemble de disques différent de la base de données utilisateur. 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.

ONLINE = { ON | OFF }

S’applique à : SQL Server 2008 (10.0.x) et versions ultérieures

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. Par défaut, il s’agit de OFF. REBUILD peut être effectué en tant qu’opération ONLINE .

Remarque

Les index non cluster uniques ne peuvent pas être créés en ligne. Cela inclut les index créés en raison d’une contrainte ou PRIMARY KEY d’une UNIQUE contrainte.

  • ACTIVÉ

    Les verrous de table à long terme ne sont pas maintenus pendant 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. Ceci permet d'exécuter les requêtes ou les mises à jour dans la table sous-jacente et ses index. Au début de l’opération, un verrou partagé (S) est conservé sur l’objet source pendant une courte période. À la fin de l’opération, pendant une courte période, un verrou S (partagé) est acquis sur la source si un index non cluster est créé ; ou un verrou Sch-M (modification de schéma) est acquis lorsqu’un index cluster est créé ou supprimé en ligne et lorsqu’un index cluster ou non cluster est reconstruit. Bien que les verrous d'index en ligne soient des verrous de métadonnées courtes, le verrou Sch-M doit notamment attendre que toutes les transactions bloquantes soient terminées sur cette table. Pendant le temps d’attente, le verrou Sch-M bloque toutes les autres transactions en attente derrière ce verrou en cas d’accès à la même table. ONLINE ne peut pas être défini ON sur le moment où un index est créé sur une table temporaire locale.

    Remarque

    La reconstruction d’index en ligne peut définir les options low_priority_lock_wait décrites plus loin dans cette section. low_priority_lock_wait gère la priorité du verrou S ou Sch-M pendant la reconstruction d’index en ligne.

  • OFF

    Des verrous de table sont appliqués pendant l’opération d’indexation. Cela empêche tous les utilisateurs d’accéder à la table sous-jacente pendant l’opération. Une opération d'indexation hors ligne qui crée, régénère ou supprime un index cluster, ou régénère ou supprime un index non cluster, acquiert un verrou de modification de schéma (Sch-M) sur la table. Cela empêche tous les utilisateurs d’accéder à la table sous-jacente pendant 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 les mises à jour de la table sous-jacente, mais autorise les opérations de lecture, telles que SELECT les instructions.

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

Remarque

Les opérations d’index en ligne ne sont pas disponibles dans chaque édition de Microsoft SQL Server. Pour obtenir la liste des fonctionnalités prises en charge par les éditions de SQL Server, consultez Éditions et fonctionnalités prises en charge de SQL Server 2022.

RESUMABLE = { ON | OFF}

S’applique à : SQL Server 2022 (16.x) et versions ultérieures

Spécifie si une opération ALTER TABLE ADD CONSTRAINT peut être reprise. Une opération d’ajout de contrainte de table peut être reprise quand ON. Une opération d’ajout de contrainte de table ne peut pas être reprise quand OFF. La valeur par défaut est OFF. Quand l’option RESUMABLE est définie sur ON, l’option ONLINE = ON est obligatoire.

MAX_DURATION lorsqu’elle est utilisée avec RESUMABLE = ON (nécessite ONLINE = ON) indique le temps (valeur entière spécifiée en minutes) qu’une opération de contrainte d’ajout en ligne pouvant être reprise est exécutée avant d’être suspendue. Si elle n’est pas spécifiée, l’opération continue jusqu’à ce qu’elle soit terminée. MAXDOP est également pris en charge RESUMABLE = ON .

Pour plus d’informations sur l’activation et l’utilisation d’opérations ALTER TABLE ADD CONSTRAINT pouvant être reprises, consultez Ajout de contraintes de table avec reprise.

MAXDOP = max_degree_of_parallelism

S’applique à : SQL Server 2008 (10.0.x) et versions ultérieures

Remplace l’option de configuration max degree of parallelism pendant l’opération d’index. Pour plus d’informations, consultez Configurer le degré maximal de parallélisme (option de configuration de serveur). Permet MAXDOP de limiter le nombre de processeurs utilisés dans une exécution de plan parallèle. Le nombre maximal de processeurs est égal à 64.

max_degree_of_parallelism peut avoir la valeur :

  • 1: supprime la génération de plan parallèle.
  • >1: limite le nombre maximal de processeurs utilisés dans une opération d’index parallèle au nombre spécifié.
  • 0 (par défaut) : utilise le nombre réel de processeurs ou moins en fonction de la charge de travail système actuelle.

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

Notes

Les opérations d’index parallèles ne sont pas disponibles dans toutes les éditions de Microsoft SQL Server. Pour obtenir la liste des fonctionnalités prises en charge par les éditions de SQL Server, consultez Éditions et fonctionnalités prises en charge de SQL Server 2022.

DATA_COMPRESSION

S’applique à : SQL Server 2008 (10.0.x) et versions ultérieures

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 disponibles sont les suivantes :

  • Aucune

    La table ou les partitions spécifiées ne sont pas compressées. S'applique uniquement aux tables rowstore ; ne s'applique pas aux tables columnstore.

  • ROW

    La table ou les partitions spécifiées sont compressées au moyen de la compression de ligne. S'applique uniquement aux tables rowstore ; ne s'applique pas aux tables columnstore.

  • PAGE

    La table ou les partitions spécifiées sont compressées au moyen de la compression de page. S'applique uniquement aux tables rowstore ; ne s'applique pas aux tables columnstore.

  • COLUMNSTORE

    S’applique à : SQL Server 2014 (12.x) et ultérieur

    S'applique uniquement aux tables columnstore. COLUMNSTORE spécifie de décompresser une partition qui a été compressée avec l’option COLUMNSTORE_ARCHIVE . Lorsque les données sont restaurées, l’index COLUMNSTORE continue d’être compressé avec la compression columnstore utilisée pour toutes les tables columnstore.

  • COLUMNSTORE_ARCHIVE

    S’applique à : SQL Server 2014 (12.x) et ultérieur

    S'applique uniquement aux tables columnstore, qui sont des tables stockées avec un index cluster columnstore. COLUMNSTORE_ARCHIVE compresse davantage la partition spécifiée en une taille plus petite. Peut être utilisé pour l'archivage, ou d'autres situations qui nécessitent moins de stockage et supportent plus de temps pour le stockage et la récupération.

Pour plus d’informations sur la compression, consultez Compression des données.

XML_COMPRESSION

S'applique à : SQL Server 2022 (16.x) et versions ultérieures, base de données Azure SQL, Azure SQL Managed Instance

Spécifie l’option de compression XML pour toute les colonnes de type de données XML de la table. Les options disponibles sont les suivantes :

  • ACTIVÉ

    Les colonnes utilisant le type de données xml sont compressées.

  • OFF

    Les colonnes utilisant le type de données xml ne sont pas compressées.

ON PARTITIONS ( { <partition_number_expression> | <range> } [ ,...n ] )

S’applique à : SQL Server 2008 (10.0.x) et versions ultérieures

Spécifie les partitions auxquelles les paramètres DATA_COMPRESSION ou XML_COMPRESSION s’appliquent. Si la table n’est pas partitionnée, l’argument ON PARTITIONS génère une erreur. Si la ON PARTITIONS clause n’est pas fournie, l’option ou XML_COMPRESSION l’option DATA_COMPRESSION s’applique à toutes les partitions d’une table partitionnée.

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

  • Indiquez le nombre d’une partition, par exemple : ON PARTITIONS (2).
  • Spécifie 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, par exemple : 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 :

--For rowstore tables
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)
)

--For columnstore tables
REBUILD WITH
(
  DATA_COMPRESSION = COLUMNSTORE ON PARTITIONS (1, 3, 5),
  DATA_COMPRESSION = COLUMNSTORE_ARCHIVE ON PARTITIONS (2, 4, 6 TO 8)
)

<single_partition_rebuild__option>

Dans la plupart des cas, la régénération d'un index reconstruit toutes les partitions d'un index partitionné. Les options suivantes, lorsqu'elles sont appliquées à une partition unique, ne régénèrent pas toutes les partitions.

  • SORT_IN_TEMPDB
  • MAXDOP
  • DATA_COMPRESSION
  • XML_COMPRESSION

low_priority_lock_wait

S’applique à : SQL Server 2014 (12.x) et ultérieur

Une SWITCH reconstruction d’index en ligne ou en ligne se termine dès qu’il n’existe aucune opération bloquante pour cette table. WAIT_AT_LOW_PRIORITY indique que si l’opération SWITCH de reconstruction d’index en ligne ou en ligne ne peut pas être terminée immédiatement, elle attend. L’opération contient des verrous de faible priorité, ce qui permet à d’autres opérations qui contiennent des verrous en conflit avec les instructions DDL de continuer. Omettre l’option WAIT AT LOW PRIORITY équivaut à WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE).

MAX_DURATION = heure [ MINUTES ]

Temps d’attente (valeur entière spécifiée en minutes) que le SWITCH verrou de reconstruction d’index en ligne qui doit être acquis, attend lors de l’exécution de la commande DDL. L’opération SWITCH de reconstruction d’index en ligne tente de se terminer immédiatement. Si l’opération est bloquée pour le MAX_DURATION moment, l’une des ABORT_AFTER_WAIT actions est exécutée. MAX_DURATION l’heure est toujours en minutes, et le mot MINUTES peut être omis.

ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS }

  • NONE

    Poursuit l’opération SWITCH de reconstruction d’index en ligne ou sans modifier la priorité de verrou (à l’aide de la priorité régulière).

  • SELF

    Quitte l’opération SWITCH DDL de reconstruction d’index en ligne ou en cours d’exécution sans effectuer d’action.

  • BLOCKERS

    Tue toutes les transactions utilisateur qui bloquent actuellement l’opération SWITCH DDL de reconstruction d’index en ligne ou qui bloquent l’opération afin que l’opération puisse continuer.

    BLOCKERS nécessite l’autorisation ALTER ANY CONNECTION .

Contenu connexe