Partager via

  • Article

ALTER TABLE (Transact-SQL)

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 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 ] 
        [ SPARSE | NULL | NOT NULL ] 
    | {ADD | DROP } 
        { ROWGUIDCOL | PERSISTED | NOT FOR REPLICATION | SPARSE }
    } 
        | [ WITH { CHECK | NOCHECK } ]

    | ADD 
    { 
        <column_definition>
      | <computed_column_definition>
      | <table_constraint> 
      | <column_set_definition> 
    } [ ,...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 ] }

    | { ENABLE | DISABLE } CHANGE_TRACKING 
        [ WITH ( TRACK_COLUMNS_UPDATED = { ON | OFF } ) ]

    | SWITCH [ PARTITION source_partition_number_expression ]
        TO target_table 
        [ PARTITION target_partition_number_expression ]

    | SET ( FILESTREAM_ON = { partition_scheme_name | filegroup | 
                "default" | "NULL" } )

    | REBUILD 
      [ [PARTITION = ALL]
        [ WITH ( <rebuild_option> [ ,...n ] ) ] 
      | [ PARTITION = partition_number 
           [ WITH ( <single_partition_rebuild_option> [ ,...n ] ) ]
        ]
      ]

    | (<table_option>)
}
[ ; ]

<column_set_definition> ::= 
    column_set_name XML COLUMN_SET FOR ALL_SPARSE_COLUMNS

<drop_clustered_constraint_option> ::=  
    { 
        MAXDOP = max_degree_of_parallelism      | ONLINE = {ON | OFF }
      | MOVE TO { partition_scheme_name (column_name) | filegroup
          | "default" }
    }
<table_option> ::=
    {
        SET ( LOCK_ESCALATION = { AUTO | TABLE | DISABLE } )
    }

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

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 actuel, 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. 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 les statistiques générées par l'instruction CREATE STATISTICS sauf lorsque la colonne est de type varchar, nvarchar ou varbinary, que le type de données n'est pas modifié, que la nouvelle taille est égale ou supérieure à l'ancienne taille ou que la colonne est modifiée pour passer d'une valeur non Null à une valeur Null. Vous devez d'abord supprimer les statistiques à l'aide de l'instruction DROP STATISTICS. Les statistiques générées automatiquement par l'optimiseur de requête sont supprimées automatiquement 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 de timestamp est utilisé si aucun column_name n'est spécifié pour une colonne de type données 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 ;

    • type de données d'alias dérivé d'un type de données système SQL Server. Les types de données d'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).

    [!REMARQUE]

    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.

  • precision
    Précision du type de données spécifié. Pour plus d'informations sur les valeurs de précision valides, consultez Précision, échelle et longueur (Transact-SQL).

  • scale
    Échelle du type de données spécifié. Pour plus d'informations sur les valeurs d'échelle valides, consultez Précision, échelle et longueur (Transact-SQL).

  • 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 d'inclure une colonne xml dans une collection de schémas, vous devez d'abord créer cette dernière dans la base de données à l'aide 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 obtenir la liste des classements et pour plus d'informations, consultez Nom de classement Windows (Transact-SQL) et Nom du classement SQL Server (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 d'alias défini par l'utilisateur, vous devez exécuter des instructions ALTER TABLE distinctes afin de remplacer le type de données de la colonne par le type de données système SQL Server, modifier son classement, puis remplacer à nouveau le type de données de la colonne par le type de données d'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 recherche en 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).

  • SPARSE | NULL | NOT NULL
    Spécifie si la colonne est une colonne fragmentée ou si elle accepte les valeurs Null. Si la colonne à modifier est une colonne fragmentée, vous devez explicitement spécifier cette propriété ; sinon, la colonne redevient une colonne non fragmentée. Les colonnes fragmentées ne peuvent pas être désignées comme NOT NULL. Le fait de convertir une colonne fragmentée en colonne non fragmentée, ou inversement, a pour effet de verrouiller la table pendant la durée de l'exécution de la commande.

    Pour connaître les restrictions supplémentaires et obtenir plus d'informations sur les colonnes fragmentées ainsi que sur la possibilité de valeur Null, consultez Utilisation de colonnes fragmentées.

    Les colonnes qui n'acceptent pas les valeurs Null peuvent être ajoutées à l'aide de l'instruction ALTER TABLE seulement si vous spécifiez une valeur par défaut pour la colonne ou si l'ensemble de la table est vide. NOT NULL peut être spécifié pour les colonnes calculées uniquement 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 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 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. NOT NULL peut être spécifié 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 ne 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. Nous vous recommandons de toujours définir explicitement une colonne comme NULL ou NOT NULL lorsqu'il s'agit de colonnes non calculées.

    Si vous ajoutez une colonne avec un type de données défini par l'utilisateur, définissez la colonne avec la même possibilité de valeur Null que la propriété Null du type de données défini par l'utilisateur et spécifiez une valeur par défaut pour la colonne. Pour plus d'informations, consultez CREATE TABLE (Transact-SQL).

    [!REMARQUE]

    Si NULL ou NOT NULL est spécifié avec ALTER COLUMN, vous devez également spécifier 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 GUID de ligne. 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 supprimée de la colonne spécifiée ou qu'elle lui est ajouté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 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.

  • DROP NOT FOR REPLICATION
    Spécifie que les valeurs sont incrémentées dans les colonnes d'identité lorsque les agents de réplication effectuent des opérations d'insertion. Cette clause peut être spécifiée uniquement si column_name est une colonne d'identité. Pour plus d'informations, consultez Contrôle des contraintes, des identités et des déclencheurs avec l'option NOT FOR REPLICATION.

  • SPARSE
    Spécifie que la colonne à ajouter ou à supprimer est une colonne fragmentée. Le stockage des colonnes fragmentées est optimisé pour les valeurs Null. Les colonnes fragmentées ne peuvent pas être désignées comme NOT NULL. Le fait de convertir une colonne fragmentée en colonne non fragmentée, ou inversement, a pour effet de verrouiller la table pendant la durée de l'exécution de la commande.

    [!REMARQUE]

    Vous devez spécifier la propriété SPARSE chaque fois que vous modifiez la colonne ; sinon, la colonne redevient une colonne non fragmentée.

    Pour connaître les restrictions supplémentaires et obtenir plus d'informations sur les colonnes fragmentées, consultez Utilisation de colonnes fragmentées.

  • WITH CHECK | WITH NOCHECK
    Spécifie si les données de la table doivent être, ou non, 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, 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. Cela n'est pas recommandé, sauf dans quelques rares cas. 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.

    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.

    [!REMARQUE]

    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 Degré maximum de parallélisme.

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

    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 (valeur par défaut)
      Utilise le nombre réel de processeurs ou un nombre de processeurs inférieur en fonction de la charge de travail actuelle du système.

    Pour plus d'informations, consultez 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.

  • 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. REBUILD peut être effectué en tant qu'opération ONLINE.

    • 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 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é (S, Shared) 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. ONLINE ne peut pas avoir la valeur ON lors de la création d'un index sur une table temporaire locale. Seule l'opération de reconstruction HEAP monothread est autorisée.

    • OFF
      Des verrous de table sont appliqués pendant l'opération d'indexation. 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 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. Autorise les opérations de reconstruction HEAP multithread.

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

    [!REMARQUE]

    Les opérations d'index en ligne ne sont disponibles que dans les éditions Enterprise, Developer et Evaluation de SQL Server.

  • 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 vers le nouvel emplacement.

    [!REMARQUE]

    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 dans la colonne 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.

  • { ENABLE | DISABLE } CHANGE_TRACKING
    Spécifie si le suivi des modifications est activé ou désactivé pour la table. Par défaut, le suivi des modifications est désactivé.

    Cette option est disponible uniquement lorsque le suivi des modifications est activé pour la base de données. Pour plus d'informations, consultez Options SET de ALTER DATABASE (Transact-SQL).

    Pour activer le suivi des modifications, la table doit avoir une clé primaire.

  • WITH ( TRACK_COLUMNS_UPDATED = { ON | OFF } )
    Spécifie si le moteur de base de données stocke des informations sur les colonnes de suivi des modifications mises à jour. La valeur par défaut est OFF.

  • SWITCH [ PARTITION source_partition_number_expression ] TO [ schema_name**.** ] 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 à 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 préalablement créée 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 en plusieurs parties.

    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. Elles ne peuvent pas référencer des expressions Transact-SQL.  

    [!REMARQUE]

    Vous ne pouvez pas utiliser l'instruction SWITCH sur des tables répliquées.

  • SET ( FILESTREAM_ON = { partition_scheme_name | filestream_filegroup_name | "default" | "NULL" })
    Spécifie où les données FILESTREAM sont stockées.

    ALTER TABLE avec la clause SET FILESTREAM_ON réussit uniquement si la table n'a pas de colonnes FILESTREAM. Les colonnes FILESTREAM peuvent être ajoutées en utilisant une deuxième instruction ALTER TABLE.

    Si partition_scheme_name est spécifié, les règles pour CREATE TABLE s'appliquent. La table doit déjà être partitionnée pour les données de lignes, et son schéma de partition doit utiliser les mêmes fonction de partition et colonnes de partition que le schéma de partition de FILESTREAM.

    filestream_filegroup_name spécifie le nom d'un groupe de fichiers FILESTREAM. Le groupe de fichiers doit avoir un fichier qui est défini pour le groupe de fichiers à l'aide d'une instruction CREATE DATABASE ou ALTER DATABASE ; sinon, une erreur est générée.

    "default" spécifie le groupe de fichiers FILESTREAM avec le jeu de propriétés DEFAULT. S'il n'y a aucun groupe de fichiers FILESTREAM, une erreur est générée.

    "NULL" spécifie que toutes les références aux groupes de fichiers FILESTREAM pour la table seront supprimées. Toutes les colonnes FILESTREAM doivent être supprimées en premier. Vous devez utiliser SET FILESTREAM_ON**= "NULL"** pour supprimer toutes les données FILESTREAM associées à une table.

  • SET ( LOCK_ESCALATION = { AUTO | TABLE | DISABLE } )
    Spécifie les méthodes d'escalade de verrous autorisées pour une table.

    • AUTO
      Cette option permet au moteur de base de données SQL Server de sélectionner la granularité d'escalade de verrous appropriée pour le schéma de la table.

      • Si la table est partitionnée, l'escalade de verrous est effectuée jusqu'au niveau de la partition. Une fois que l'escalade de verrous a atteint le niveau de la partition, elle n'est pas étendue à la granularité TABLE.

      • Si la table n'est pas partitionnée, l'escalade de verrous continue jusqu'à la granularité TABLE.

    • TABLE
      L'escalade de verrous continue jusqu'à la granularité TABLE, que la table soit ou non partitionnée. Ce comportement est identique à celui de SQL Server 2005. TABLE est la valeur par défaut.

    • DISABLE
      Empêche l'escalade de verrous dans la plupart des cas. Les verrous de niveau table ne sont pas totalement interdits. Par exemple, lorsque vous analysez une table qui ne contient aucun index cluster sous le niveau d'isolation sérialisable, le moteur de base de données doit prendre un verrou de table pour protéger l'intégrité des données.

  • REBUILD
    Utilisez la syntaxe REBUILD WITH pour reconstruire une table entière qui inclut toutes les partitions d'une table partitionnée. Si la table a un index cluster, l'option REBUILD reconstruit l'index cluster. REBUILD peut être effectué en tant qu'opération ONLINE.

    Utilisez la syntaxe REBUILD PARTITION pour reconstruire une partition unique dans une table partitionnée.

  • PARTITION = ALL
    Reconstruit toutes les partitions lors de la modification des paramètres de compression de la partition.

  • REBUILD WITH ( <rebuild_option> )
    Toutes les options s'appliquent à une table pourvue d'un index cluster. Si la table n'a pas d'index cluster, la structure de segment n'est affectée que par certaines options.

    Pour une description complète des options de reconstruction, consultez index_option (Transact-SQL).

  • DATA_COMPRESSION
    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 :

    • NONE
      La table ou les partitions spécifiées ne sont pas compressées.

    • ROW
      La table ou les partitions spécifiées sont compressés au moyen de la compression de ligne.

    • PAGE
      La table ou les partitions spécifiées sont compressés au moyen de la compression de page.

    Pour reconstruire plusieurs partitions en même temps, consultez index_option (Transact-SQL). Si la table n'a pas d'index cluster, la modification de la compression de données reconstruit le segment de mémoire et les index non cluster. Pour plus d'informations sur la compression, consultez Création de tables et d'index compressés.

  • column_set_name XML COLUMN_SET FOR ALL_SPARSE_COLUMNS
    Représente le nom du jeu de colonnes. Un jeu de colonnes est une représentation XML non typée qui combine toutes les colonnes fragmentées d'une table dans une sortie structurée. Un jeu de colonnes ne peut pas être ajouté à une table qui contient des colonnes fragmentées. Pour plus d'informations sur les jeux de colonnes, consultez Utilisation de jeux de colonnes.

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 des lignes 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, l'échelle ou la précision d'une colonne en spécifiant une nouvelle taille pour le type de données de la colonne dans la clause ALTER COLUMN. Si des données existent dans la colonne, la nouvelle taille ne peut pas être inférieure à la taille maximale des données. En outre, la colonne ne peut pas être définie dans un index, sauf si le type de données de la colonne est varchar, nvarchar ou varbinary et si l'index n'est 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 de tables 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, aussi bien pour s'exécuter que pour générer un grand nombre d'enregistrements de journal. 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 2008 Enterprise, 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 de schéma 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é 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. Vous pouvez supprimer l'index cluster et déplacer la table résultante vers un autre groupe de fichiers ou schéma de partition dans une transaction unique 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 sorte que la transaction DROP INDEX ne bloque pas les requêtes ni les 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.

[!REMARQUE]

Les options <drop_clustered_constraint_option> s'appliquent aux index cluster sur des tables ; elles ne s'appliquent pas aux index cluster sur des vues ni aux 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.

Compression de données

Les tables système ne peuvent pas être activées pour la compression. Si la table est un segment de mémoire, l'opération de reconstruction pour le mode ONLINE sera monothread. Utilisez le mode OFFLINE pour une opération de reconstruction de segment de mémoire multithread. Pour plus d'informations sur la compression de données, consultez Création de tables et d'index compressés.

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

Les restrictions suivantes s'appliquent aux tables partitionnées :

  • Vous ne pouvez pas modifier le paramètre de compression d'une partition unique si la table possède des index non alignés.

  • La syntaxe ALTER TABLE <table> REBUILD PARTITION ... reconstruit la partition spécifiée.

  • La syntaxe ALTER TABLE <table> REBUILD WITH ... reconstruit toutes les partitions.

Autorisations

Requiert une autorisation ALTER sur la table.

Les autorisations ALTER TABLE s'appliquent aux tables impliquées 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 d'alias, l'autorisation REFERENCES est requise sur le type.

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 de 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 par rapport aux 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 si la valeur par défaut est appliquée, une autre valeur est insérée dans la première colonne, puis 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 nouvelle colonne a une 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 de 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 respecte 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. Une table non partitionnée est ensuite créée avec la même structure que la table partitionnée et dans 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 respecte normalement pas le déclencheur. ENABLE TRIGGER est ensuite utilisé 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 qui a la valeur 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 ainsi que la précision et l'échelle d'une colonne decimal. Dans la mesure où les colonnes contiennent des données, la taille de colonne peut uniquement être augmentée. Remarquez aussi que col_a est défini dans un index unique. La taille de col_a peut encore être augmentée, car le type de données est 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');

Q. Autorisation de l'escalade de verrous sur les tables partitionnées

L'exemple suivant autorise l'escalade de verrous au niveau de la partition sur une table partitionnée. Si la table n'est pas partitionnée, l'escalade de verrous continue jusqu'au niveau TABLE.

ALTER TABLE T1 SET (LOCK_ESCALATION = AUTO)
GO

R. Configuration du suivi des modifications sur une table

L'exemple ci-dessous active le suivi des modifications sur la table Person.Contact dans la base de données AdventureWorks.

USE AdventureWorks;
ALTER TABLE Person.Contact
ENABLE CHANGE_TRACKING;

L'exemple ci-dessous active le suivi des modifications ainsi que le suivi des colonnes qui sont mises à jour lors d'une modification.

USE AdventureWorks;
ALTER TABLE Person.Contact
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON);

L'exemple ci-dessous désactive le suivi des modifications sur la table Person.Contact dans la base de données AdventureWorks :

USE AdventureWorks;
ALTER TABLE Person.Contact
DISABLE CHANGE_TRACKING;

S. Modification d'une table pour modifier la compression

L'exemple suivant modifie la compression d'une table non partitionnée. Le segment de mémoire ou l'index cluster sera reconstruit. Si la table est un segment, tous les index non cluster associés à la table sont reconstruits.

ALTER TABLE T1 
REBUILD WITH (DATA_COMPRESSION = PAGE);

L'exemple suivant modifie la compression d'une table partitionnée. La syntaxe REBUILD PARTITION = 1 entraîne uniquement la reconstruction de la partition numéro 1.

ALTER TABLE PartitionTable1 
REBUILD PARTITION = 1 WITH (DATA_COMPRESSION =  NONE) ;
GO

La même opération utilisant la syntaxe de remplacement suivante provoque la reconstruction de toutes les partitions dans la table.

ALTER TABLE PartitionTable1 
REBUILD PARTITION ALL 
WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(1) ) ;

Pour obtenir d'autres exemples de compression de données, consultez Création de tables et d'index compressés.

T. Ajout d'une colonne fragmentée

Les exemples suivants illustrent l'ajout et la modification de colonnes fragmentées dans la table T1. Le code pour créer la table T1 se présente comme suit.

CREATE TABLE T1
(C1 int PRIMARY KEY,
C2 varchar(50) SPARSE NULL,
C3 int SPARSE NULL,
C4 int ) ;
GO

Pour ajouter une colonne fragmentée supplémentaire C5, exécutez l'instruction suivante.

ALTER TABLE T1
ADD C5 char(100) SPARSE NULL ;
GO

Pour convertir la colonne non fragmentée C4 en colonne fragmentée, exécutez l'instruction suivante.

ALTER TABLE T1
ALTER COLUMN C4 ADD SPARSE ;
GO

Pour convertir la colonne fragmentée C4 en colonne non fragmentée, exécutez l'instruction suivante.

ALTER TABLE T1
ALTER COLUMN C4 DROP SPARSE;
GO

U. Ajout d'un jeu de colonnes

Les exemples suivants montrent comment ajouter une colonne à la table T2. Un jeu de colonnes ne peut pas être ajouté à une table qui contient déjà des colonnes fragmentées. Le code permettant de créer la table T2 est comme suit.

CREATE TABLE T2
(C1 int PRIMARY KEY,
C2 varchar(50) NULL,
C3 int NULL,
C4 int ) ;
GO

Les trois instructions suivantes ajoutent un jeu de colonnes nommé CS, puis changent les colonnes C2 et C3 en SPARSE.

ALTER TABLE T2
ADD CS XML COLUMN_SET FOR ALL_SPARSE_COLUMNS ;
GO

ALTER TABLE T2
ALTER COLUMN C2 ADD SPARSE ; 
GO

ALTER TABLE T2
ALTER COLUMN C3 ADD SPARSE ;
GO

V. Modification du classement des colonnes

L'exemple suivant indique comment modifier le classement d'une colonne. En premier lieu, nous créons la table T3 avec les classements d'utilisateur par défaut :

CREATE TABLE T3
(C1 int PRIMARY KEY,
C2 varchar(50) NULL,
C3 int NULL,
C4 int ) ;
GO

Ensuite, le classement de la colonne C2 est modifié en Latin1_General_BIN. Notez que le type de données est obligatoire, bien qu'il ne soit pas modifié.

ALTER TABLE T3
ALTER COLUMN C2 varchar(50) COLLATE Latin1_General_BIN 
GO

Historique des modifications du document

Mise à jour du contenu

Correction de l'option AUTO de l'escalade de verrous.

Ajout de [ SPARSE ] aux propriétés de syntaxe (avant [ NULL | NOT NULL ].

Ajout d'informations sur les types UDT, les valeurs par défaut, ainsi que sur la possibilité de valeur Null dans la section Arguments.

Ajout d'informations supplémentaires sur la définition de la syntaxe SPARSE dans la section Arguments.

Ajout d'un exemple montrant comment modifier le classement de colonne et la nécessité de respécifier le type de données.