ALTER TABLE (Transact-SQL)

  • Article

Mis à jour : 15 septembre 2007

Modifie la définition d'une table en changeant, en ajoutant ou en supprimant des colonnes et des contraintes, en réaffectant des partitions, en désactivant ou en activant des contraintes et des déclencheurs.

Icône Lien de rubriqueConventions de la syntaxe de Transact-SQL

Syntaxe

ALTER TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name 
{ 
    ALTER COLUMN column_name 
    { 
        [ type_schema_name. ] type_name [ ( { precision [ , scale ] 
            | max | xml_schema_collection } ) ] 
        [ COLLATE collation_name ] 
        [ NULL | NOT NULL ] 
    | {ADD | DROP } { ROWGUIDCOL | PERSISTED | NOT FOR REPLICATION}
    } 
    | [ WITH { CHECK | NOCHECK } ] ADD 
    { 
        <column_definition>
      | <computed_column_definition>
      | <table_constraint> 
    } [ ,...n ]
    | DROP 
    { 
        [ CONSTRAINT ] constraint_name 
        [ WITH ( <drop_clustered_constraint_option> [ ,...n ] ) ]
        | COLUMN column_name 
    } [ ,...n ] 
    | [ WITH { CHECK | NOCHECK } ] { CHECK | NOCHECK } CONSTRAINT 
        { ALL | constraint_name [ ,...n ] } 
    | { ENABLE | DISABLE } TRIGGER 
        { ALL | trigger_name [ ,...n ] }
    | SWITCH [ PARTITION source_partition_number_expression ]
        TO target_table 
        [ PARTITION target_partition_number_expression ]
}
[ ; ]

<drop_clustered_constraint_option> ::=  
    { 
        MAXDOP = max_degree_of_parallelism
      | ONLINE = {ON | OFF }
      | MOVE TO { partition_scheme_name ( column_name ) | filegroup
          | "default"}
    }

Arguments

  • database_name
    Nom de la base de données dans laquelle la table a été créée.
  • schema_name
    Nom du schéma auquel appartient la table.
  • table_name
    Nom de la table à modifier. Si la table ne se trouve pas dans la base de données active ou si elle n'est pas contenue dans le schéma appartenant à l'utilisateur actif, la base de données et le schéma doivent être explicitement spécifiés.

  • ALTER COLUMN
    Spécifie que la colonne nommée doit être modifiée. ALTER COLUMN n'est pas autorisé si le niveau de compatibilité est inférieur ou égal à 65. Pour plus d'informations, consultez sp_dbcmptlevel (Transact-SQL).

    La colonne modifiée ne peut pas être l'une des colonnes suivantes :

    • une colonne de type de données timestamp ;
    • la colonne ROWGUIDCOL de la table ;
    • une colonne calculée ou utilisée dans une colonne calculée ;
    • une colonne utilisée dans un index, sauf lorsque la colonne est du type varchar, nvarchar ou varbinary, que le type de données n'est pas modifié, que la nouvelle taille est supérieure ou égale à l'ancienne taille et que l'index n'est pas le résultat d'une contrainte PRIMARY KEY ;
    • une colonne utilisée dans des statistiques générées par l'instruction CREATE STATISTICS. Vous devez d'abord supprimer les statistiques à l'aide de l'instruction DROP STATISTICS. Les statistiques créées automatiquement par l'optimiseur de requête sont automatiquement supprimées par ALTER COLUMN.
    • une colonne utilisée dans une contrainte PRIMARY KEY ou [FOREIGN KEY] REFERENCES ;
    • une colonne utilisée dans une contrainte CHECK ou UNIQUE. Cependant, la modification de la longueur d'une colonne de longueur variable utilisée dans une contrainte CHECK ou UNIQUE est autorisée.
    • une colonne associée à une définition par défaut. Cependant, il est possible de modifier la longueur, la précision ou l'échelle d'une colonne si le type de données n'est pas modifié.
      Vous pouvez modifier le type de données de colonnes text, ntext et image seulement de l'une des manières suivantes :
      • text en varchar(max), nvarchar(max) ou xml
      • ntext en varchar(max), nvarchar(max) ou xml
      • image en varbinary(max)
        Certaines modifications de type de données peuvent entraîner une modification des données. Ainsi, la conversion d'une colonne de type nchar ou nvarchar en type char ou varchar peut entraîner la conversion de caractères étendus. Pour plus d'informations, consultez CAST et CONVERT (Transact-SQL). La réduction de la précision ou de l'échelle d'une colonne peut tronquer les données.
        Vous ne pouvez pas modifier le type de données d'une colonne d'une table partitionnée.
  • column_name
    Nom de la colonne à modifier, ajouter ou supprimer. column_name peut comporter au maximum 128 caractères. L'argument column_name peut être omis dans le cas de nouvelles colonnes créées avec le type de données timestamp. Le nom timestamp est utilisé si aucune valeur de column_name n'est spécifiée pour une colonne de type timestamp.

  • [ type_schema_name**.** ] type_name
    Nouveau type de données de la colonne modifiée ou type de données de la colonne ajoutée. Il n'est pas possible de spécifier l'argument type_name pour les colonnes existantes de tables partitionnées. type_name peut être un des types suivants :

    • type de données système SQL Server 2005 ;
    • type de données alias dérivé d'un type de données système SQL Server. Les types de données alias sont créés à l'aide de l'instruction CREATE TYPE avant de pouvoir être utilisés dans la définition d'une table.
    • type .NET Framework défini par l'utilisateur et schéma auquel il appartient. Les types .NET Framework définis par l'utilisateur sont créés à l'aide de l'instruction CREATE TYPE avant de pouvoir être utilisés dans la définition d'une table.

    Les critères suivants s'appliquent à l'argument type_name d'une colonne modifiée :

    • Le type de données précédent doit pouvoir être implicitement converti vers le nouveau type de données.
    • type_name ne peut pas être du type timestamp.
    • Les valeurs par défaut ANSI_NULL sont toujours activées pour ALTER COLUMN ; si l'option n'est pas spécifiée, la colonne accepte les valeurs NULL.
    • Le remplissage ANSI_PADDING est toujours activé (ON) pour ALTER COLUMN.
    • Si la colonne modifiée est une colonne d'identité, new_data_type doit être un type qui prend en charge la propriété d'identité.
    • La configuration actuelle de SET ARITHABORT est ignorée. ALTER TABLE fonctionne comme si l'option ARITHABORT était activée (ON).
    ms190273.note(fr-fr,SQL.90).gifRemarque :
    Si la clause COLLATE n'est pas spécifiée, la modification du type de données d'une colonne entraîne une modification du classement, qui est remplacé par le classement par défaut de la base de données.
  • max
    S'applique seulement aux types de données varchar, nvarchar et varbinary pour stocker 2^31-1 octets de données binaires, Unicode et de type caractère.
  • xml_schema_collection
    S'applique seulement au type de données xml, pour associer un schéma XML au type. Avant de définir une colonne de type xml dans un classement de schémas, celui-ci doit d'abord être créé au moyen de CREATE XML SCHEMA COLLECTION.

  • COLLATE < collation_name >
    Spécifie le nouveau classement pour la colonne modifiée. Si l'argument n'est pas spécifié, c'est le classement par défaut de la base de données qui est affecté à la colonne. Le nom du classement peut être un nom de classement Windows ou SQL. Pour une liste des classements et pour plus d'informations, consultez Nom de classement Windows (Transact-SQL) et Nom du classement SQL (Transact-SQL).

    La clause COLLATE peut être utilisée pour modifier seulement les classements des colonnes ayant le type de données char, varchar, nchar et nvarchar. Pour modifier le classement d'une colonne de type de données alias défini par l'utilisateur, vous devez exécuter des instructions ALTER TABLE distinctes pour modifier le type de données de la colonne en type de données système SQL Server et modifier son classement, puis pour modifier à nouveau le type de données de la colonne en type de données alias.

    ALTER COLUMN ne peut pas modifier un classement si une ou plusieurs des conditions suivantes sont remplies :

    • Une contrainte CHECK, une contrainte FOREIGN KEY ou des colonnes calculées font référence à la colonne modifiée.
    • Un index, des statistiques ou un index de texte intégral sont créés sur la colonne. Les statistiques créées automatiquement sur la colonne modifiée sont supprimées si le classement de la colonne est modifié.
    • Une vue ou une fonction liée à un schéma fait référence à la colonne.

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

  • NULL | NOT NULL
    Spécifie si la colonne accepte les valeurs NULL. Les colonnes qui n'acceptent pas les valeurs NULL peuvent être ajoutées à l'aide de l'instruction ALTER TABLE seulement si une valeur par défaut a été définie pour celles-ci ou si la table est vide. Il est possible de spécifier NOT NULL pour des colonnes calculées seulement si PERSISTED est également spécifié. Si la nouvelle colonne accepte les valeurs NULL et qu'aucune valeur par défaut n'est spécifiée, la colonne contient une valeur NULL pour chaque ligne de la table. Si la nouvelle colonne accepte les valeurs NULL et si une définition de valeur par défaut est ajoutée avec la nouvelle colonne, l'option WITH VALUES peut être utilisée pour stocker la valeur par défaut dans la nouvelle colonne pour chaque ligne existante de la table.

    Si la nouvelle colonne n'accepte pas les valeurs NULL et si la table n'est pas vide, une définition de valeur par défaut DEFAULT doit être ajoutée à la nouvelle colonne. La nouvelle colonne charge automatiquement la valeur par défaut dans les nouvelles colonnes de chaque ligne existante.

    L'option NULL peut être spécifiée dans l'instruction ALTER COLUMN pour forcer une colonne NOT NULL à accepter les valeurs NULL, excepté pour les colonnes soumises à des contraintes PRIMARY KEY. L'option NOT NULL peut être spécifiée dans ALTER COLUMN seulement si la colonne ne contient pas de valeurs NULL. Les valeurs NULL doivent être mises à jour avec une valeur quelconque avant que ALTER COLUMN NOT NULL soit autorisé. Par exemple :

    UPDATE MyTable SET NullCol = N'some_value' WHERE NullCol IS NULL
ALTER TABLE MyTable ALTER COLUMN NullCOl NVARCHAR(20) NOT NULL

    Lorsque vous créez ou modifiez une table à l'aide des instructions CREATE TABLE ou ALTER TABLE, les paramètres de la base de données et de la session influencent et éventuellement modifient la possibilité de valeurs NULL pour le type de données utilisé dans la définition d'une colonne. Il est recommandé de toujours définir explicitement une colonne comme NULL ou NOT NULL ou, si vous utilisez un type de données défini par l'utilisateur, d'autoriser la colonne à utiliser la possibilité de valeur NULL par défaut pour ce type de données. Pour plus d'informations, consultez CREATE TABLE (Transact-SQL).

    ms190273.note(fr-fr,SQL.90).gifRemarque :
    Si les options NULL ou NOT NULL sont spécifiées avec ALTER COLUMN, vous devez également spécifier l'option new_data_type [(precision [, scale ])]. Si vous ne modifiez pas le type de données, la précision ou l'échelle, spécifiez les valeurs actuelles de la colonne.

  • [ {ADD | DROP} ROWGUIDCOL ]
    Spécifie que la propriété ROWGUIDCOL est supprimée de la colonne spécifiée ou qu'elle lui est ajoutée. ROWGUIDCOL indique que la colonne est une colonne d'identificateur global unique (GUID). Une seule colonne uniqueidentifier par table peut être désignée comme colonne ROWGUIDCOL ; il est possible d'affecter la propriété ROWGUIDCOL seulement à une colonne uniqueidentifier. La propriété ROWGUIDCOL ne peut pas être affectée à une colonne dont le type de données est défini par l'utilisateur.

    ROWGUIDCOL n'impose pas l'unicité des valeurs stockées dans la colonne et ne génère pas automatiquement des valeurs pour les nouvelles lignes insérées dans la table. Pour générer des valeurs uniques pour chaque colonne, utilisez la fonction NEWID sur des instructions INSERT, ou définissez la fonction NEWID par défaut pour la colonne.

  • [ {ADD | DROP} PERSISTED ]
    Spécifie que la propriété PERSISTED est ajoutée à ou supprimée de la colonne spécifiée. La colonne doit être une colonne calculée définie avec une expression déterministe. Pour les colonnes spécifiées avec la propriété PERSISTED, le moteur de base de données SQL Server 2005 stocke physiquement les valeurs calculées dans la table et met à jour les valeurs lorsque d'autres colonnes dont dépend la colonne calculée sont mises à jour. Si vous marquez une colonne calculée comme PERSISTED, vous pouvez créer des index sur des colonnes calculées définies sur des expressions qui sont déterministes mais pas précises. Pour plus d'informations, consultez Création d'index sur des colonnes calculées.

    Toute colonne calculée utilisée comme colonne de partitionnement d'une table partitionnée doit être explicitement marquée comme PERSISTED.

  • WITH CHECK | WITH NOCHECK
    Spécifie si les données de la table doivent être validées par rapport à une contrainte FOREIGN KEY ou CHECK nouvellement ajoutée ou réactivée. Si l'option n'est pas spécifiée, l'option WITH CHECK est utilisée pour les nouvelles contraintes et l'option WITH NOCHECK pour les contraintes réactivées.

    Utilisez WITH NOCHECK si vous ne voulez pas vérifier les nouvelles contraintes CHECK ou FOREIGN KEY sur les données existantes. Ceci n'est pas recommandé, sauf dans quelques cas rares. La nouvelle contrainte sera évaluée dans toutes les mises à jour ultérieures. Toute violation de contrainte supprimée par l'option WITH NOCHECK lors de l'ajout de la contrainte peut faire échouer les mises à jour ultérieures si celles-ci mettent des lignes à jour à l'aide de données non conformes à la contrainte.

    L'optimiseur de requête ne prend pas en compte les contraintes définies avec WITH NOCHECK. Ces contraintes sont ignorées tant qu'elles n'ont pas été réactivées à l'aide de l'instruction ALTER TABLE table CHECK CONSTRAINT ALL.

  • ADD
    Spécifie qu'une ou plusieurs définitions de colonnes, définitions de colonnes calculées ou contraintes de tables sont ajoutées.

  • DROP { [ CONSTRAINT ] constraint_name | COLUMN column_name }
    Spécifie que constraint_name ou column_name est supprimé de la table. Plusieurs colonnes et contraintes peuvent être indiquées. L'option DROP COLUMN n'est pas autorisée si le niveau de compatibilité est 65 ou antérieur. Pour plus d'informations, consultez sp_dbcmptlevel (Transact-SQL).

    Il est possible de déterminer le nom de la contrainte défini par l'utilisateur ou fourni par le système en effectuant une requête dans les affichages catalogue sys.check_constraint, sys.default_constraints, sys.key_constraints et sys.foreign_keys.

    Il n'est pas possible de supprimer une contrainte PRIMARY KEY s'il existe un index XML sur la table.

    Une colonne ne peut pas être supprimée lorsqu'elle est :

    • utilisée dans un index ;
    • utilisée dans une contrainte CHECK, FOREIGN KEY, UNIQUE ou PRIMARY KEY ;
    • associée à une valeur par défaut définie à l'aide du mot clé DEFAULT ou liée à un objet par défaut ;
    • liée à une règle.
    ms190273.note(fr-fr,SQL.90).gifRemarque :
    La suppression d'une colonne ne permet pas de récupérer l'espace disque de la colonne. Vous pouvez être amené à récupérer l'espace disque d'une colonne supprimée lorsque la taille des lignes d'une table est proche de sa limite ou l'a dépassée. Récupérez de l'espace en créant un index cluster sur la table ou en reconstruisant un index cluster existant à l'aide de l'instruction ALTER INDEX.
  • WITH <drop_clustered_constraint_option>
    Spécifie qu'une ou plusieurs options de suppression de contrainte cluster sont définies.

  • MAXDOP = max_degree_of_parallelism
    Remplace l'option de configuration max degree of parallelism seulement pendant la durée de l'opération. Pour plus d'informations, consultez Option max degree of parallelism.

    Utilisez l'option MAXDOP pour limiter le nombre de processeurs utilisés dans un plan d'exécution parallèle. Le nombre maximal de processeurs est égal à 64.

    max_degree_of_parallelism peut avoir l'une des valeurs suivantes :

    • 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 (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.

    ms190273.note(fr-fr,SQL.90).gifRemarque :
    Le traitement en parallèle des index est possible uniquement dans SQL Server 2005 Enterprise Edition.

  • 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 le traitement des index. La valeur par défaut est OFF.

    • ON
      Les verrous de table à long terme ne sont pas maintenus pendant la durée de traitement des index. Pendant la phase principale de traitement des index, seul un verrou de partage intentionnel (Intent Share - IS) est maintenu sur la table source. Cela permet d'effectuer des requêtes ou des mises à jour dans la table sous-jacente et à l'opération sur les index de continuer. Au début de l'opération, un verrou partagé (Shared - S) est maintenu sur l'objet source pendant une période de temps très courte. À la fin de l'opération, pendant une courte période de temps, un verrou partagé (S - Shared) est acquis sur la source si un index non-cluster est en cours de création, ou un verrou SCH-M (Modification du schéma) est acquis lorsqu'un index non-cluster est créé ou supprimé en ligne et lorsqu'un index cluster ou non-cluster est en cours de reconstruction. Il n'est pas possible de définir ONLINE à ON lorsqu'un index est en cours de création sur une table temporaire locale.
    • OFF
      Les verrous de table sont appliqués pour la durée de traitement des index. Une opération d'index hors connexion qui crée, reconstruit ou supprime un index cluster, ou reconstruit ou supprime un index non-cluster, acquiert un verrou de modification de schéma (Sch-M) sur la table. Cela empêche tous les accès des utilisateurs à la table sous-jacente pendant la durée de l'opération. Une opération d'index hors connexion qui crée un index non-cluster acquiert un verrou partagé (Shared - S) 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.

    ms190273.note(fr-fr,SQL.90).gifRemarque :
    Les opérations d'index en ligne sont possibles seulement dans SQL Server 2005 Enterprise Edition.

  • MOVE TO ( partition_scheme_name ( column_name [ 1**,** ... n] ) | filegroup | "default"}
    Spécifie un emplacement où déplacer les lignes de données actuellement au niveau feuille de l'index cluster. La table est déplacée au nouvel emplacement.

    ms190273.note(fr-fr,SQL.90).gifRemarque :
    Dans ce contexte, « default » n'est pas un mot clé. Il s'agit d'un identificateur du groupe de fichiers par défaut, qui doit être délimité, par exemple dans MOVE TO "default" ou MOVE TO [default]. Si "default" est spécifié, l'option QUOTED_IDENTIFIER doit être activée (ON) pour la session active. Il s'agit du paramètre par défaut. Pour plus d'informations, consultez SET QUOTED_IDENTIFIER (Transact-SQL).
  • { CHECK | NOCHECK } CONSTRAINT
    Spécifie si constraint_name est activé ou désactivé. Cette option peut être utilisée seulement avec les contraintes FOREIGN KEY et CHECK. Lorsque NOCHECK est spécifié, la contrainte est désactivée ; les insertions et les mises à jour ultérieures ne sont pas validées par rapport aux conditions de la contrainte. Il n'est pas possible de désactiver les contraintes DEFAULT, PRIMARY KEY et UNIQUE.
  • ALL
    Spécifie que toutes les contraintes sont désactivées à l'aide de l'option NOCHECK, ou bien activées à l'aide de l'option CHECK.
  • { ENABLE | DISABLE } TRIGGER
    Spécifie si trigger_name est activé ou désactivé. Lorsqu'un déclencheur est désactivé, il est néanmoins défini pour la table. Toutefois, lorsque des instructions INSERT, UPDATE et DELETE sont exécutées sur la table, les actions du déclencheur ne sont pas effectuées tant que celui-ci n'a pas été réactivé.
  • ALL
    Spécifie que tous les déclencheurs de la table sont activés ou désactivés.
  • trigger_name
    Spécifie le nom du déclencheur à activer ou à désactiver.

  • SWITCH [ PARTITION source_partition_number_expression ] TO target_table [ PARTITION target_ partition_number_expression ]
    Bascule un bloc de données de l'une des manières suivantes :

    • Réaffecte toutes les données d'une table en tant que partition d'une table partitionnée déjà existante.
    • Bascule une partition d'une table partitionnée vers une autre.
    • Réaffecte toutes les données d'une partition d'une table partitionnée à une table non partitionnée existante.

    Si table est une table partitionnée, la valeur de source_partition_number_expression doit être spécifiée. Si l'élément target_table est partitionné, la valeur de target_partition_number_expression doit être spécifiée. En cas de réaffectation des données d'une table en tant que partition à une table partitionnée déjà existante ou de basculement d'une partition d'une table partitionnée vers une autre, la partition cible doit exister et être vide.

    En cas de réaffectation des données d'une partition pour constituer une seule table, la table cible doit être créée auparavant et être vide. La table ou la partition source ainsi que la table ou la partition cible doivent se trouver dans le même groupe de fichiers. Les index ou les partitions d'index correspondants doivent également se trouver dans le même groupe de fichiers. De nombreuses autres restrictions s'appliquent au basculement des partitions. Pour plus d'informations, consultez Transfert efficace de données à l'aide du commutateur de partitionnement. table et target_table ne peuvent pas être identiques. target_table peut être un identificateur multipartie.

    source_partition_number_expression et target_partition_number_expression sont des expressions constantes qui peuvent référencer des variables et des fonctions, y compris les variables et les fonctions définies par l'utilisateur. Ces arguments ne peuvent pas référencer des expressions Transact-SQL.

    ms190273.note(fr-fr,SQL.90).gifRemarque :
    Vous ne pouvez pas utiliser l'instruction SWITCH sur des tables répliquées.

Notes

Pour ajouter de nouvelles lignes de données, utilisez l'instruction INSERT. Pour supprimer des lignes de données, utilisez les instructions DELETE ou TRUNCATE TABLE. Pour modifier des valeurs dans les dignes existantes, utilisez l'instruction UPDATE.

Si le cache de la procédure contient des plans d'exécution qui référencent la table, ALTER TABLE les marque de façon à les recompiler lors de leur prochaine exécution.

Modification de la taille d'une colonne

Vous pouvez modifier la longueur, la précision ou l'échelle d'une colonne en spécifiant une nouvelle taille pour le type de données de colonne dans la clause ALTER COLUMN. Si la colonne contient des données, la nouvelle taille ne peut pas être inférieure à la longueur maximale des données. De plus, la colonne ne peut pas être définie dans un index, à moins qu'elle ne soit de type varchar, nvarchar ou varbinary et que l'index ne soit pas le résultat d'une contrainte PRIMARY KEY. Voir l'exemple P.

Verrous et ALTER TABLE

Les modifications spécifiées dans l'instruction ALTER TABLE sont implémentées immédiatement. Si elles nécessitent une modification des lignes de la table, ALTER TABLE met les lignes à jour. ALTER TABLE acquiert un verrou de modification du schéma sur la table pour garantir qu'aucune autre connexion ne référence même les métadonnées de la table pendant la modification, à l'exception des opérations d'index en ligne qui nécessitent un verrouillage de type SCH-M à la fin. Dans une opération ALTER TABLE…SWITCH, le verrou est acquis à la fois sur la table source et sur la table cible. Les modifications effectuées sur la table sont consignées dans un journal et peuvent être récupérées entièrement. Les modifications qui affectent toutes les lignes d'une table de dimension importante, telles que la suppression d'une colonne ou l'ajout d'une colonne NOT NULL avec une valeur par défaut, peuvent demander beaucoup de temps, tant pour s'exécuter que pour générer un grand nombre d'enregistrements dans le journal des transactions. Ces instructions ALTER TABLE doivent être exécutées avec le même soin que toute instruction INSERT, UPDATE ou DELETE qui affectent un grand nombre de lignes.

Exécution d'un plan en parallèle

Dans SQL Server 2005 Enterprise Edition, le nombre de processeurs utilisés pour exécuter une instruction ALTER TABLE ADD (basée sur un index) CONSTRAINT ou DROP (index cluster) CONSTRAINT est déterminé par l'option de configuration max degree of parallelism et par la charge de travail en cours. Si le moteur de base de données détecte que le système est occupé, le degré de parallélisme de l'opération est automatiquement diminué avant le démarrage de l'exécution de l'instruction. Vous pouvez configurer manuellement le nombre de processeurs utilisés pour exécuter l'instruction en spécifiant l'option MAXDOP.

Tables partitionnées

Outre les opérations SWITCH qui mettent en œuvre des tables partitionnées, ALTER TABLE peut être utilisée pour modifier l'état des colonnes, des contraintes et des déclencheurs d'une table partitionnée, de la même manière que pour les tables non partitionnées. Cependant, cette instruction n'est pas utilisable pour modifier la façon dont la table elle-même est partitionnée. Pour repartitionner une table partitionnée, utilisez les instructions ALTER PARTITION SCHEME et ALTER PARTITION FUNCTION. De plus, vous ne pouvez pas modifier le type de données d'une colonne d'une table partitionnée.

Restrictions sur les tables comportant des vues liées au schéma

Les restrictions applicables aux instructions ALTER TABLE dans les tables comportant des vues liées au schéma sont identiques à celles qui s'appliquent à la modification de tables comportant un index simple. L'ajout d'une colonne est autorisé. Cependant, la suppression ou la modification d'une colonne intervenant dans une vue associée à un schéma n'est pas autorisée. Si l'instruction ALTER TABLE requiert la modification d'une colonne utilisée dans une vue liée au schéma, ALTER TABLE échoue et le moteur de base de données génère un message d'erreur. Pour plus d'informations sur la liaison aux schémas et sur les vues indexées, consultez CREATE VIEW (Transact-SQL).

L'ajout ou la suppression de déclencheurs sur les tables de base n'est pas affectée par la création d'une vue liée au schéma comportant des références aux tables.

Index et ALTER TABLE

Tout index créé dans le cadre d'une contrainte est supprimé lorsque cette dernière est supprimée. Un index créé au moyen de l'instruction CREATE INDEX doit être supprimé à l'aide de l'instruction DROP INDEX. L'instruction ALTER INDEX peut être utilisée pour reconstruire un index faisant partie de la définition d'une contrainte ; il n'est pas nécessaire de supprimer la contrainte et de l'ajouter à nouveau à l'aide de l'instruction ALTER TABLE.

Tous les index et contraintes basés sur une colonne doivent être supprimés avant que la colonne puisse être supprimée.

Lorsqu'une contrainte qui a créé un index cluster est supprimée, les lignes de données stockées au niveau feuille de l'index cluster sont stockées dans une table non-cluster. Dans SQL Server 2005, vous pouvez supprimer l'index cluster et déplacer la table résultante dans un autre groupe de fichiers ou une autre partition en une seule transaction en spécifiant l'option MOVE TO. Cette option est soumise aux restrictions suivantes :

  • MOVE TO n'est pas valide pour les vues non indexées ou les index non-cluster.
  • Le schéma de partition ou le groupe de fichiers doit déjà exister.
  • Si MOVE TO n'est pas spécifié, la table est placée dans le même schéma de partition ou groupe de fichiers qui a été défini pour l'index cluster.

Lorsque vous supprimez un index cluster, vous pouvez spécifier l'option ONLINE = ON de façon que la transaction DROP INDEX ne bloque pas les requêtes et des modifications des données sous-jacentes et des index non-cluster associés.

L'option ONLINE = ON est soumise aux restrictions suivantes :

  • ONLINE = ON n'est pas valide pour les index cluster qui sont également désactivés. Les index désactivés doivent être supprimés au moyen de ONLINE = OFF.
  • Un seul index peut être supprimé à la fois.
  • ONLINE = ON n'est pas valide pour les vues indexées, les index non-cluster ou les index sur des tables temporaires locales.

Pour supprimer un index cluster, l'espace disque temporaire doit être égal à la taille de l'index cluster existant. Cet espace supplémentaire est libéré dès que l'opération est terminée.

ms190273.note(fr-fr,SQL.90).gifRemarque :
Les options répertoriées sous <drop_clustered_constraint_option> s'appliquent aux index cluster sur des tables ; ils ne s'appliquent pas aux index cluster sur des vues ou sur des index non cluster.

Réplication des modifications de schéma

Par défaut, lorsque vous exécutez l'instruction ALTER TABLE sur une table publiée d'un serveur de publication SQL Server, cette modification est propagée à tous les Abonnés SQL Server. Cette fonctionnalité comporte des restrictions et peut être désactivée. Pour plus d'informations, consultez Modification du schéma dans les bases de données de publication.

Autorisations

Nécessite l'autorisation ALTER sur la table.

Les autorisations ALTER TABLE s'appliquent aux tables mises en œuvre dans une instruction ALTER TABLE SWITCH. Toute donnée basculée hérite de la sécurité de la table cible.

Si des colonnes dans l'instruction ALTER TABLE sont définies avec un type CLR défini par l'utilisateur ou un type de données alias, l'autorisation REFERENCES sur le type est requise.

Exemples

A. Ajout d'une nouvelle colonne

L'exemple suivant ajoute une colonne qui accepte les valeurs NULL et pour laquelle aucune valeur n'est spécifiée via une définition DEFAULT. Dans la nouvelle colonne, chaque ligne aura la valeur NULL.

CREATE TABLE dbo.doc_exa (column_a INT) ;
GO
ALTER TABLE dbo.doc_exa ADD column_b VARCHAR(20) NULL ;
GO
EXEC sp_help doc_exa ;
GO
DROP TABLE dbo.doc_exa ;
GO

B. Suppression d'une colonne

L'exemple suivant supprime une colonne dans une table.

CREATE TABLE dbo.doc_exb (column_a INT, column_b VARCHAR(20) NULL) ;
GO
ALTER TABLE dbo.doc_exb DROP COLUMN column_b ;
GO
EXEC sp_help doc_exb ;
GO
DROP TABLE dbo.doc_exb ;
GO

C. Modification du type de données d'une colonne

L'exemple suivant modifie le type d'une colonne d'une table de INT en DECIMAL.

CREATE TABLE dbo.doc_exy (column_a INT ) ;
GO
INSERT INTO dbo.doc_exy (column_a) VALUES (10) ;
GO
ALTER TABLE dbo.doc_exy ALTER COLUMN column_a DECIMAL (5, 2) ;
GO
DROP TABLE dbo.doc_exy ;
GO

D. Ajout d'une colonne avec une contrainte

L'exemple suivant ajoute une nouvelle colonne avec une contrainte UNIQUE.

CREATE TABLE dbo.doc_exc (column_a INT) ;
GO
ALTER TABLE dbo.doc_exc ADD column_b VARCHAR(20) NULL 
    CONSTRAINT exb_unique UNIQUE ;
GO
EXEC sp_help doc_exc ;
GO
DROP TABLE dbo.doc_exc ;
GO

E. Ajout d'une contrainte CHECK non vérifiée à une colonne existante

L'exemple suivant ajoute une contrainte à une colonne existante de la table. La colonne comporte une valeur qui ne respecte pas la contrainte. Par conséquent, WITH NOCHECK empêche la validation de la contrainte sur les lignes existantes, et permet l'ajout de la contrainte.

CREATE TABLE dbo.doc_exd ( column_a INT) ;
GO
INSERT INTO dbo.doc_exd VALUES (-1) ;
GO
ALTER TABLE dbo.doc_exd WITH NOCHECK 
ADD CONSTRAINT exd_check CHECK (column_a > 1) ;
GO
EXEC sp_help doc_exd ;
GO
DROP TABLE dbo.doc_exd ;
GO

F. Ajout d'une contrainte DEFAULT à une colonne existante

L'exemple suivant crée une table de deux colonnes et insère une valeur dans la première ; l'autre colonne conserve la valeur NULL. Une contrainte DEFAULT est alors ajoutée à la deuxième colonne. Pour vérifier que la valeur par défaut est appliquée, une autre valeur est insérée dans la première colonne et la table fait l'objet d'une requête.

CREATE TABLE dbo.doc_exz ( column_a INT, column_b INT) ;
GO
INSERT INTO dbo.doc_exz (column_a)VALUES ( 7 ) ;
GO
ALTER TABLE dbo.doc_exz
ADD CONSTRAINT col_b_def
DEFAULT 50 FOR column_b ;
GO
INSERT INTO dbo.doc_exz (column_a) VALUES ( 10 ) ;
GO
SELECT * FROM dbo.doc_exz ;
GO
DROP TABLE dbo.doc_exz ;
GO

G. Ajout de plusieurs colonnes avec des contraintes

L'exemple suivant ajoute plusieurs colonnes avec des contraintes définies. La première colonne a la propriété IDENTITY. Chaque ligne de la table a de nouvelles valeurs incrémentielles dans la colonne d'identité.

CREATE TABLE dbo.doc_exe ( column_a INT CONSTRAINT column_a_un UNIQUE) ;
GO
ALTER TABLE dbo.doc_exe ADD 

-- Add a PRIMARY KEY identity column.
column_b INT IDENTITY
CONSTRAINT column_b_pk PRIMARY KEY, 

-- Add a column that references another column in the same table.
column_c INT NULL  
CONSTRAINT column_c_fk 
REFERENCES doc_exe(column_a),

-- Add a column with a constraint to enforce that 
-- nonnull data is in a valid telephone number format.
column_d VARCHAR(16) NULL 
CONSTRAINT column_d_chk
CHECK 
(column_d LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]' OR
column_d LIKE
'([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'),

-- Add a nonnull column with a default.
column_e DECIMAL(3,3)
CONSTRAINT column_e_default
DEFAULT .081 ;
GO
EXEC sp_help doc_exe ;
GO
DROP TABLE dbo.doc_exe ;
GO

H. Ajout d'une colonne acceptant les valeurs NULL, avec des valeurs par défaut

L'exemple suivant ajoute une colonne qui accepte les valeurs NULL, avec une définition DEFAULT. Il utilise l'option WITH VALUES pour spécifier des valeurs pour chaque ligne existante de la table. Si l'option WITH VALUES n'est pas utilisée, chaque ligne a la valeur NULL dans la nouvelle colonne.

USE AdventureWorks ; 
GO
CREATE TABLE dbo.doc_exf ( column_a INT) ;
GO
INSERT INTO dbo.doc_exf VALUES (1) ;
GO
ALTER TABLE dbo.doc_exf 
ADD AddDate smalldatetime NULL
CONSTRAINT AddDateDflt
DEFAULT GETDATE() WITH VALUES ;
GO
DROP TABLE dbo.doc_exf ;
GO

I. Désactivation et réactivation d'une contrainte

L'exemple suivant désactive une contrainte qui limite les salaires acceptés dans les données. NOCHECK CONSTRAINT est utilisé avec ALTER TABLE pour désactiver la contrainte et autoriser une insertion qui ne respecterait normalement pas la contrainte. CHECK CONSTRAINT réactive la contrainte.

CREATE TABLE dbo.cnst_example 
(id INT NOT NULL,
 name VARCHAR(10) NOT NULL,
 salary MONEY NOT NULL
    CONSTRAINT salary_cap CHECK (salary < 100000)
);

-- Valid inserts
INSERT INTO dbo.cnst_example VALUES (1,'Joe Brown',65000);
INSERT INTO dbo.cnst_example VALUES (2,'Mary Smith',75000);

-- This insert violates the constraint.
INSERT INTO dbo.cnst_example VALUES (3,'Pat Jones',105000);

-- Disable the constraint and try again.
ALTER TABLE dbo.cnst_example NOCHECK CONSTRAINT salary_cap;
INSERT INTO dbo.cnst_example VALUES (3,'Pat Jones',105000);

-- Re-enable the constraint and try another insert; this will fail.
ALTER TABLE dbo.cnst_example CHECK CONSTRAINT salary_cap;
INSERT INTO dbo.cnst_example VALUES (4,'Eric James',110000) ;

J. Suppression d'une contrainte

L'exemple suivant supprime une contrainte UNIQUE d'une table.

CREATE TABLE dbo.doc_exc ( column_a INT
CONSTRAINT my_constraint UNIQUE) ;
GO
ALTER TABLE dbo.doc_exc DROP CONSTRAINT my_constraint ;
GO
DROP TABLE dbo.doc_exc ;
GO

K. Basculement de partitions entre des tables

L'exemple suivant crée une table partitionnée, en partant du principe que le schéma de partition myRangePS1 est déjà créé dans la base de données. Ensuite, une table non partitionnée est créée avec la même structure que la table partitionnée et sur le même groupe de fichiers que PARTITION 2 de la table PartitionTable. Les données de PARTITION 2 de la table PartitionTable sont ensuite basculées dans la table NonPartitionTable.

CREATE TABLE PartitionTable (col1 int, col2 char(10))
ON myRangePS1 (col1) ;
GO
CREATE TABLE NonPartitionTable (col1 int, col2 char(10))
ON test2fg ;
GO
ALTER TABLE PartitionTable SWITCH PARTITION 2 TO NonPartitionTable ;
GO

L. Désactivation et réactivation d'un déclencheur

L'exemple suivant utilise l'option DISABLE TRIGGER de l'instruction ALTER TABLE pour désactiver le déclencheur et autoriser une insertion qui ne respecterait normalement pas le déclencheur. ENABLE TRIGGER est ensuite utilisée pour réactiver le déclencheur.

CREATE TABLE dbo.trig_example 
(id INT, 
name VARCHAR(12),
salary MONEY) ;
GO
-- Create the trigger.
CREATE TRIGGER dbo.trig1 ON dbo.trig_example FOR INSERT
AS
IF (SELECT COUNT(*) FROM INSERTED
WHERE salary > 100000) > 0
BEGIN
    print 'TRIG1 Error: you attempted to insert a salary > $100,000'
    ROLLBACK TRANSACTION
END ;
GO
-- Try an insert that violates the trigger.
INSERT INTO dbo.trig_example VALUES (1,'Pat Smith',100001) ;
GO
-- Disable the trigger.
ALTER TABLE dbo.trig_example DISABLE TRIGGER trig1 ;
GO
-- Try an insert that would typically violate the trigger.
INSERT INTO dbo.trig_example VALUES (2,'Chuck Jones',100001) ;
GO
-- Re-enable the trigger.
ALTER TABLE dbo.trig_example ENABLE TRIGGER trig1 ;
GO
-- Try an insert that violates the trigger.
INSERT INTO dbo.trig_example VALUES (3,'Mary Booth',100001) ;
GO

M. Création d'une contrainte PRIMARY KEY avec des options d'index

L'exemple suivant crée la contrainte PRIMARY KEY PK_TransactionHistoryArchive_TransactionID et définit les options FILLFACTOR, ONLINE et PAD_INDEX. L'index cluster généré portera le même nom que la contrainte.

USE AdventureWorks;
GO
ALTER TABLE Production.TransactionHistoryArchive WITH NOCHECK 
ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARY KEY CLUSTERED (TransactionID)
WITH (FILLFACTOR = 75, ONLINE = ON, PAD_INDEX = ON)
GO

N. Suppression d'une contrainte PRIMARY KEY en mode ONLINE

L'exemple suivant supprime une contrainte PRIMARY KEY avec l'option ONLINE définie à ON.

USE AdventureWorks;
GO
ALTER TABLE Production.TransactionHistoryArchive
DROP CONSTRAINT PK_TransactionHistoryArchive_TransactionID
WITH (ONLINE = ON);
GO

O. Ajout et suppression d'une contrainte FOREIGN KEY

L'exemple suivant crée la table ContactBackup, puis la modifie en ajoutant d'abord une contrainte FOREIGN KEY qui référence la table Contact, puis en supprimant la contrainte FOREIGN KEY.

USE AdventureWorks ;
GO
CREATE TABLE Person.ContactBackup
(ContactID int) ;
GO
ALTER TABLE Person.ContactBackup
ADD CONSTRAINT FK_ContactBacup_Contact FOREIGN KEY (ContactID)
    REFERENCES Person.Contact (ContactID) ;
ALTER TABLE Person.ContactBackup
DROP CONSTRAINT FK_ContactBacup_Contact ;
GO
DROP TABLE Person.ContactBackup ;

P. Modification de la taille d'une colonne

L'exemple suivant augmente la taille d'une colonne varchar et la précision et l'échelle d'une colonne decimal. Étant donné que les colonnes contiennent des données, il est impossible de réduire leur taille. Notez également que col_a est défini dans un index unique. La taille de col_a peut encore être augmentée car les données sont de type varchar et l'index n'est pas le résultat d'une contrainte PRIMARY KEY.

IF OBJECT_ID ( 'dbo.doc_exy', 'U' ) IS NOT NULL 
    DROP TABLE dbo.doc_exy;
GO
-- Create a two-column table with a unique index on the varchar column.
CREATE TABLE dbo.doc_exy ( col_a varchar(5) UNIQUE NOT NULL, col_b decimal (4,2));
GO
INSERT INTO dbo.doc_exy VALUES ('Test', 99.99);
GO
-- Verify the current column size.
SELECT name, TYPE_NAME(system_type_id), max_length, precision, scale
FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.doc_exy');
GO
-- Increase the size of the varchar column.
ALTER TABLE dbo.doc_exy ALTER COLUMN col_a varchar(25);
GO
-- Increase the scale and precision of the decimal column.
ALTER TABLE dbo.doc_exy ALTER COLUMN col_b decimal (10,4);
GO
-- Insert a new row.
INSERT INTO dbo.doc_exy VALUES ('MyNewColumnSize', 99999.9999) ;
GO
-- Verify the current column size.
SELECT name, TYPE_NAME(system_type_id), max_length, precision, scale
FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.doc_exy');

Voir aussi

Référence

sp_rename (Transact-SQL)
CREATE TABLE (Transact-SQL)
DROP TABLE (Transact-SQL)
sp_help (Transact-SQL)
ALTER PARTITION SCHEME (Transact-SQL)
ALTER PARTITION FUNCTION (Transact-SQL)
EVENTDATA (Transact-SQL)

Autres ressources

Création et modification de tables
Modification du schéma dans les bases de données de publication

Aide et Informations

Assistance sur SQL Server 2005

Historique des modifications

Version Historique

15 septembre 2007
Contenu modifié :
  • Ajout de la section « Modification de la taille d'une colonne » et de l'exemple P.

12 décembre 2006
Contenu modifié :
  • Clarification de la position et de la signification de la clause NOT FOR REPLICATION dans les sections sur la syntaxe et les arguments.
  • Précision indiquant que la table cible d'une clause SWITCH peut être exprimée sous la forme d'un identificateur multipartie.

14 avril 2006
Nouveau contenu :
  • Ajout du fait que vous ne pouvez pas utiliser l'instruction SWITCH sur des tables répliquées.

5 décembre 2005
Nouveau contenu :
  • La clause DROP NOT FOR REPLICATION a été ajoutée dans la liste des définitions d'arguments et dans le diagramme de la syntaxe.
Contenu modifié :
  • La clause COLLATE a été placée à l'emplacement approprié dans le diagramme de la syntaxe.
  • Les exemples M et N ont été corrigés.