TABLE D’AUTEL (Transact-SQL)

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)Entrepôt dans Microsoft FabricBase de données SQL dans Microsoft Fabric

Modifie une définition de table en modifiant, ajoutant ou déposant des colonnes et des contraintes. ALTER TABLE réaffecte et reconstruit également des partitions, ou désactive et active les contraintes et les déclencheurs.

Note

Actuellement, dans Fabric Warehouse, ALTER TABLE seuls les contraintes sont prises en charge et l’ajout de colonnes nullables. Consultez syntaxe de l’entrepôt dans Microsoft Fabric. Actuellement, les tables optimisées en mémoire ne sont pas disponibles dans la base de données SQL de Microsoft Fabric.

La syntaxe est ALTER TABLE différente pour les tables sur disque et les tables optimisées en mémoire. Utilisez les liens suivants pour accéder directement au bloc de syntaxe approprié pour vos types de tables et aux exemples de syntaxe appropriés :

Tables sur disque :

• Syntax
• Examples

Tables optimisées en mémoire :

• Syntax
• Modification des tables Memory-Optimized

Pour plus d’informations sur les conventions de la syntaxe, consultez Conventions de la syntaxe Transact-SQL.

Syntaxe des tables basées sur disque

ALTER TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
{
    ALTER COLUMN column_name
    {
        [ type_schema_name. ] type_name
            [ (
                {
                   precision [ , scale ]
                 | max
                 | xml_schema_collection
                }
            ) ]
        [ COLLATE collation_name ]
        [ NULL | NOT NULL ] [ SPARSE ]
      | { ADD | DROP }
          { ROWGUIDCOL | PERSISTED | NOT FOR REPLICATION | SPARSE | HIDDEN }
      | { ADD | DROP } MASKED [ WITH ( FUNCTION = ' mask_function ') ]
    }
    [ WITH ( ONLINE = ON | OFF ) ]
    | [ WITH { CHECK | NOCHECK } ]

    | ADD
    {
        <column_definition>
      | <computed_column_definition>
      | <table_constraint>
      | <column_set_definition>
    } [ ,...n ]
      | [ system_start_time_column_name datetime2 GENERATED ALWAYS AS ROW START
                [ HIDDEN ] [ NOT NULL ] [ CONSTRAINT constraint_name ]
            DEFAULT constant_expression [WITH VALUES] ,
                system_end_time_column_name datetime2 GENERATED ALWAYS AS ROW END
                   [ HIDDEN ] [ NOT NULL ][ CONSTRAINT constraint_name ]
            DEFAULT constant_expression [WITH VALUES] ,
                start_transaction_id_column_name bigint GENERATED ALWAYS AS TRANSACTION_ID START
                   [ HIDDEN ] NOT NULL [ CONSTRAINT constraint_name ]
            DEFAULT constant_expression [WITH VALUES],
                  end_transaction_id_column_name bigint GENERATED ALWAYS AS TRANSACTION_ID END
                   [ HIDDEN ] NULL [ CONSTRAINT constraint_name ]
            DEFAULT constant_expression [WITH VALUES],
                  start_sequence_number_column_name bigint GENERATED ALWAYS AS SEQUENCE_NUMBER START
                   [ HIDDEN ] NOT NULL [ CONSTRAINT constraint_name ]
            DEFAULT constant_expression [WITH VALUES],
                  end_sequence_number_column_name bigint GENERATED ALWAYS AS SEQUENCE_NUMBER END
                   [ HIDDEN ] NULL [ CONSTRAINT constraint_name ]
            DEFAULT constant_expression [WITH VALUES]
        ]
       PERIOD FOR SYSTEM_TIME ( system_start_time_column_name, system_end_time_column_name )
    | DROP
     [ {
         [ CONSTRAINT ][ IF EXISTS ]
         {
              constraint_name
              [ WITH
               ( <drop_clustered_constraint_option> [ ,...n ] )
              ]
          } [ ,...n ]
          | COLUMN [ IF EXISTS ]
          {
              column_name
          } [ ,...n ]
          | PERIOD FOR SYSTEM_TIME
     } [ ,...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 ]
        [ WITH ( <low_priority_lock_wait> ) ]

    | SET
        (
            [ FILESTREAM_ON =
                { partition_scheme_name | filegroup | "default" | "NULL" } ]
            | SYSTEM_VERSIONING =
                  {
                    OFF
                  | ON
                      [ ( HISTORY_TABLE = schema_name . history_table_name
                          [, DATA_CONSISTENCY_CHECK = { ON | OFF } ]
                          [, HISTORY_RETENTION_PERIOD =
                          {
                              INFINITE | number {DAY | DAYS | WEEK | WEEKS
                  | MONTH | MONTHS | YEAR | YEARS }
                          }
                          ]
                        )
                      ]
                  }
            | DATA_DELETION =
                {
                      OFF
                    | ON
                        [(  [ FILTER_COLUMN = column_name ]
                            [, RETENTION_PERIOD = { INFINITE | number { DAY | DAYS | WEEK | WEEKS
                                    | MONTH | MONTHS | YEAR | YEARS } } ]
                        )]
                    } )
    | REBUILD
      [ [PARTITION = ALL]
        [ WITH ( <rebuild_option> [ ,...n ] ) ]
      | [ PARTITION = partition_number
           [ WITH ( <single_partition_rebuild_option> [ ,...n ] ) ]
        ]
      ]

    | <table_option>
    | <filetable_option>
    | <stretch_configuration>
}
[ ; ]

-- ALTER TABLE options

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

<filetable_option> ::=
    {
       [ { ENABLE | DISABLE } FILETABLE_NAMESPACE ]
       [ SET ( FILETABLE_DIRECTORY = directory_name ) ]
    }

<stretch_configuration> ::=
    {
      SET (
        REMOTE_DATA_ARCHIVE
        {
            = ON (<table_stretch_options>)
          | = OFF_WITHOUT_DATA_RECOVERY ( MIGRATION_STATE = PAUSED )
          | ( <table_stretch_options> [, ...n] )
        }
            )
    }

<table_stretch_options> ::=
    {
     [ FILTER_PREDICATE = { null | table_predicate_function } , ]
       MIGRATION_STATE = { OUTBOUND | INBOUND | PAUSED }
    }

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

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

Pour plus d'informations, consultez les pages suivantes :

• TABLE ALTER column_constraint
• TABLE ALTER column_definition
• MODIFIER LA TABLE computed_column_definition
• ALTER TABLE index_option
• ALTER TABLE table_constraint

Syntaxe des tables à mémoire optimisée

ALTER TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
{
    ALTER COLUMN column_name
    {
        [ type_schema_name. ] type_name
            [ (
                {
                   precision [ , scale ]
                }
            ) ]
        [ COLLATE collation_name ]
        [ NULL | NOT NULL ]
    }

    | ALTER INDEX index_name
    {
        [ type_schema_name. ] type_name
        REBUILD
        [ [ NONCLUSTERED ] WITH ( BUCKET_COUNT = bucket_count )
        ]
    }

    | ADD
    {
        <column_definition>
      | <computed_column_definition>
      | <table_constraint>
      | <table_index>
      | <column_index>
    } [ ,...n ]

    | DROP
     [ {
         CONSTRAINT [ IF EXISTS ]
         {
              constraint_name
          } [ ,...n ]
        | INDEX [ IF EXISTS ]
      {
         index_name
       } [ ,...n ]
          | COLUMN [ IF EXISTS ]
          {
              column_name
          } [ ,...n ]
          | PERIOD FOR SYSTEM_TIME
     } [ ,...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 ]
        [ WITH ( <low_priority_lock_wait> ) ]

}
[ ; ]

-- ALTER TABLE options

< table_constraint > ::=
 [ CONSTRAINT constraint_name ]
{
   {PRIMARY KEY | UNIQUE }
     {
       NONCLUSTERED (column [ ASC | DESC ] [ ,... n ])
       | NONCLUSTERED HASH (column [ ,... n ] ) WITH ( BUCKET_COUNT = bucket_count )
     }
    | FOREIGN KEY
        ( column [ ,...n ] )
        REFERENCES referenced_table_name [ ( ref_column [ ,...n ] ) ]
    | CHECK ( logical_expression )
}

<column_index> ::=
  INDEX index_name
{ [ NONCLUSTERED ] | [ NONCLUSTERED ] HASH WITH (BUCKET_COUNT = bucket_count) }

<table_index> ::=
  INDEX index_name
{ [ NONCLUSTERED ] HASH (column [ ,... n ] ) WITH (BUCKET_COUNT = bucket_count)
  | [ NONCLUSTERED ] (column [ ASC | DESC ] [ ,... n ] )
      [ ON filegroup_name | default ]
  | CLUSTERED COLUMNSTORE [ WITH ( COMPRESSION_DELAY = { 0 | delay [MINUTES] } ) ]
      [ ON filegroup_name | default ]
}

Syntaxe pour Azure Synapse Analytics et Parallel Data Warehouse

ALTER TABLE { database_name.schema_name.source_table_name | schema_name.source_table_name | source_table_name }
{
    ALTER COLUMN column_name
        {
            type_name [ ( precision [ , scale ] ) ]
            [ COLLATE Windows_collation_name ]
            [ NULL | NOT NULL ]
        }
    | ADD { <column_definition> | <column_constraint> FOR column_name} [ ,...n ]
    | DROP { COLUMN column_name | [CONSTRAINT] constraint_name } [ ,...n ]
    | REBUILD {
            [ PARTITION = ALL [ WITH ( <rebuild_option> ) ] ]
          | [ PARTITION = partition_number [ WITH ( <single_partition_rebuild_option> ] ]
      }
    | { SPLIT | MERGE } RANGE (boundary_value)
    | SWITCH [ PARTITION source_partition_number
        TO target_table_name [ PARTITION target_partition_number ] [ WITH ( TRUNCATE_TARGET = ON | OFF ) ] ]
}
[ ; ]

<column_definition>::=
{
    column_name
    type_name [ ( precision [ , scale ] ) ]
    [ <column_constraint> ]
    [ COLLATE Windows_collation_name ]
    [ NULL | NOT NULL ]
}

<column_constraint>::=
    [ CONSTRAINT constraint_name ]
    {
        DEFAULT constant_expression
        | PRIMARY KEY NONCLUSTERED (column_name [ ,... n ]) NOT ENFORCED -- Applies to Azure Synapse Analytics only
        | UNIQUE (column_name [ ,... n ]) NOT ENFORCED -- Applies to Azure Synapse Analytics only
    }
<rebuild_option > ::=
{
    DATA_COMPRESSION = { COLUMNSTORE | COLUMNSTORE_ARCHIVE }
        [ ON PARTITIONS ( {<partition_number> [ TO <partition_number>] } [ , ...n ] ) ]
    | XML_COMPRESSION = { ON | OFF }
        [ ON PARTITIONS ( {<partition_number> [ TO <partition_number>] } [ , ...n ] ) ]
}

<single_partition_rebuild_option > ::=
{
    DATA_COMPRESSION = { COLUMNSTORE | COLUMNSTORE_ARCHIVE }
}

Note

Le pool SQL serverless dans Azure Synapse Analytics prend en charge seulement les tables externes et temporaires.

Syntaxe de l’entrepôt dans Fabric

ALTER TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
{
  ADD  { column_name <data_type> [COLLATE collation_name] [ <column_options> ] } [ ,...n ]
| ADD { <column_constraint> FOR column_name} [ ,...n ]
| DROP { COLUMN column_name | [CONSTRAINT] constraint_name } [ ,...n ]
}
[ ; ]

<column_options> ::=
[ NULL ] -- default is NULL

<data type> ::=
datetime2 ( n )
| date
| time ( n )
| float [ ( n ) ]
| real [ ( n ) ]
| decimal [ ( precision [ , scale ] ) ]
| numeric [ ( precision [ , scale ] ) ]
| bigint
| int
| smallint
| bit
| varchar [ ( n ) ]
| char [ ( n ) ]
| varbinary [ ( n ) ]
| uniqueidentifier

<column_constraint>::=
    [ CONSTRAINT constraint_name ]
    {
       PRIMARY KEY NONCLUSTERED (column_name [ ,... n ]) NOT ENFORCED
        | UNIQUE NONCLUSTERED (column_name [ ,... n ]) NOT ENFORCED
    | FOREIGN KEY
        ( column [ ,...n ] )
        REFERENCES referenced_table_name [ ( ref_column [ ,...n ] ) ] NOT ENFORCED
    }

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, vous devez spécifier explicitement la base de données et le schéma.

ALTER COLONNE

Spécifie que la colonne nommée doit être modifiée.

La colonne modifiée ne peut pas être :

• Une colonne avec un type de données timestamp

• Pour ROWGUIDCOL la table.

• une colonne calculée ou utilisée dans une colonne calculée ;

• Utilisé dans les statistiques générées par l’instruction CREATE STATISTICS . Les utilisateurs doivent s’exécuter DROP STATISTICS pour supprimer les statistiques avant ALTER COLUMN de pouvoir réussir. Exécutez cette requête pour obtenir toutes les statistiques et les colonnes de statistiques créées par l’utilisateur pour une table.

  SELECT s.name AS statistics_name,
         c.name AS column_name,
         sc.stats_column_id
  FROM sys.stats AS s
       INNER JOIN sys.stats_columns AS sc
           ON s.object_id = sc.object_id
          AND s.stats_id = sc.stats_id
       INNER JOIN sys.columns AS c
           ON sc.object_id = c.object_id
          AND c.column_id = sc.column_id
  WHERE s.object_id = OBJECT_ID('<table_name>');
  

  Note

  Les statistiques générées automatiquement par l’optimiseur de requête sont automatiquement supprimées par ALTER COLUMN.

• Utilisé dans une contrainte ou PRIMARY KEY une [FOREIGN KEY] REFERENCES contrainte.

• Utilisé dans une contrainte ou CHECK une UNIQUE contrainte. Toutefois, la modification de la longueur d’une colonne de longueur variable utilisée dans une contrainte ou CHECK une UNIQUE contrainte 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 ne pouvez modifier le type de données de colonnes text, ntext et image que 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. Par exemple, 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 l’article CAST et CONVERT. La réduction de la précision ou de l'échelle d'une colonne peut tronquer les données.

Note

Vous ne pouvez pas modifier le type de données d'une colonne d'une table partitionnée.

Le type de données des colonnes inclus dans un index ne peut pas être modifié, sauf quand la colonne est du type varchar, nvarchar ou varbinary et que la nouvelle taille est supérieure ou égale à l’ancienne taille.

Une colonne incluse dans une contrainte de clé primaire ne peut pas être modifiée à NOT NULLpartir de NULL .

Lorsque vous utilisez Always Encrypted (sans enclaves sécurisées), si la colonne en cours de modification est chiffrée ENCRYPTED WITH, vous pouvez modifier le type de données en type de données compatible (par exemple INTBIGINT), mais vous ne pouvez pas modifier les paramètres de chiffrement.

Lorsque vous utilisez Always Encrypted avec des enclaves sécurisées, vous pouvez modifier les paramètres de chiffrement si la clé de chiffrement de colonne qui protège la colonne (et la nouvelle clé de chiffrement de colonne si vous modifiez la clé) prennent en charge les calculs d’enclave (chiffrés avec des clés principales de la colonne prenant en charge l’enclave). Pour plus d’informations, consultez Always Encrypted avec enclaves sécurisées.

Lorsque vous modifiez une colonne, le moteur de base de données effectue le suivi de chaque modification en ajoutant une ligne dans une table système et en marquant la modification de colonne précédente en tant que colonne supprimée. Dans le cas rare où vous modifiez une colonne trop souvent, le moteur de base de données peut atteindre la limite de taille d'enregistrement. Si cela se produit, vous obtenez l’erreur 511 ou 1708. Pour éviter ces erreurs, régénérez régulièrement l'index cluster sur la table ou réduisez le nombre de modifications de colonne.

column_name

Nom de la colonne à ajouter, modifier ou supprimer. Le maximum pour column_name est de 128 caractères. Pour les nouvelles colonnes, vous pouvez omettre column_name pour les colonnes créées avec un type de données timestamp. Le nom timestamp est utilisé si vous ne spécifiez pas de column_name pour une colonne du type de données timestamp.

Note

Les nouvelles colonnes sont ajoutées après toutes les colonnes existantes de la table à modifier.

[ 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. Vous ne pouvez pas spécifier type_name pour les colonnes existantes de tables partitionnées. type_name peut être l’un des types suivants :

• type de données système SQL Server ;
• type de données alias dérivé d'un type de données système SQL Server. Vous créez des types de données alias avec l’instruction CREATE TYPE avant de pouvoir être utilisés dans une définition de table.
• type .NET Framework défini par l'utilisateur et schéma auquel il appartient. Vous créez des types définis par l’utilisateur avec l’instruction CREATE TYPE avant de pouvoir être utilisés dans une définition de 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 timestamp.
• ANSI_NULL valeurs par défaut sont toujours activées pour ALTER COLUMN; si elle n’est pas spécifiée, la colonne est nullable.
• ANSI_PADDING le remplissage est toujours ON pour ALTER COLUMN.
• Si la colonne modifiée est une colonne d’identité, new_data_type doit être un type de données qui prend en charge la propriété d’identité.
• Le paramètre actuel pour SET ARITHABORT lequel il est ignoré. ALTER TABLE fonctionne comme s’il ARITHABORT est défini sur ON.

Note

Si la COLLATE clause n’est pas spécifiée, la modification du type de données d’une colonne entraîne une modification du 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.

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.

max

S’applique uniquement aux types de données varchar, nvarchar et varbinary pour le stockage de 2^31-1 octets de données caractères, binaires et Unicode.

xml_schema_collection

S’applique à : SQL Server et Azure SQL Database.

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 une collection de schémas, vous commencez par créer la collection de schémas avec 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 une liste et plus d’informations, consultez le nom du classement Windows et le nom du classement SQL Server.

La COLLATE clause modifie les classements uniquement des colonnes des types 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, utilisez des instructions distinctes ALTER TABLE pour remplacer la colonne par un type de données système SQL Server. Ensuite, modifiez son classement et transformez à nouveau la colonne en type de données alias.

ALTER COLUMN ne peut pas avoir de modification de classement si une ou plusieurs des conditions suivantes existent :

• Une CHECK contrainte, FOREIGN KEY une contrainte ou des colonnes calculées font référence à la colonne modifiée.
• Tous les index, statistiques ou 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 au schéma fait référence à la colonne.

Pour plus d’informations sur les classements pris en charge, consultez COLLATE.

NULL | NON NULL

Spécifie si la colonne accepte les valeurs NULL. Les colonnes qui n’autorisent pas les valeurs Null sont ajoutées ALTER TABLE uniquement si elles ont une valeur par défaut spécifiée ou si la table est vide. Vous pouvez spécifier NOT NULL uniquement pour les colonnes calculées si vous avez également spécifié PERSISTED. Si la nouvelle colonne accepte les valeurs NULL et que vous ne spécifiez aucune valeur par défaut, la nouvelle colonne contient une valeur NULL pour chaque ligne de la table. Si la nouvelle colonne autorise les valeurs Null et que vous ajoutez une définition par défaut avec la nouvelle colonne, vous pouvez utiliser WITH VALUES pour stocker la valeur par défaut dans la nouvelle colonne pour chaque ligne existante de la table.

Si la nouvelle colonne n’autorise pas les valeurs Null et que la table n’est pas vide, vous devez ajouter une DEFAULT définition avec la nouvelle colonne. La nouvelle colonne est alors automatiquement chargée avec la valeur par défaut dans les nouvelles colonnes de chaque ligne existante.

Vous pouvez spécifier NULL dans ALTER COLUMN ce cas pour forcer une NOT NULL colonne à autoriser les valeurs Null, à l’exception des colonnes dans les PRIMARY KEY contraintes. Vous pouvez spécifier NOT NULL uniquement ALTER COLUMN si la colonne ne contient aucune valeur Null. Les valeurs Null doivent être mises à jour vers une valeur avant l’autorisation ALTER COLUMNNOT NULL , 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 avec les instructions ou CREATE TABLE les ALTER TABLE instructions, les paramètres de base de données et de session influencent et remplacent éventuellement la nullabilité du type de données utilisé dans une définition de colonne. Assurez-vous que vous définissez toujours explicitement une colonne comme NULL ou NOT NULL pour des colonnes non conformes.

Si vous ajoutez une colonne avec un type de données défini par l'utilisateur, veillez à définir la colonne avec la même possibilité de valeur Null que le type de données défini par l'utilisateur. Spécifiez également une valeur par défaut pour la colonne. Pour plus d’informations, consultez CREATE TABLE.

Note

Si NULL ou NOT NULL est spécifié avec ALTER COLUMN, new_data_type [(précision [, échelle ])] doit également être spécifié. Si le type de données, la précision et l’échelle ne sont pas modifiés, spécifiez les valeurs de colonne actuelles.

[ {ADD | LARCHE} ROWGUIDCOL ]

S’applique à : SQL Server et Azure SQL Database.

Spécifie que la ROWGUIDCOL propriété est ajoutée ou supprimée de la colonne spécifiée. ROWGUIDCOL indique que la colonne est une colonne GUID de ligne. Vous ne pouvez définir qu’une seule colonne uniqueidentifier par table comme ROWGUIDCOL colonne. Et vous ne pouvez affecter la ROWGUIDCOL propriété qu’à une colonne uniqueidentifier . Vous ne pouvez pas affecter ROWGUIDCOL à une colonne d’un type de données défini par l’utilisateur.

ROWGUIDCOL n’applique pas l’unicité des valeurs stockées dans la colonne et ne génère pas automatiquement de 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() ou NEWSEQUENTIALID() dans les instructions INSERT. Vous pouvez également spécifier la fonction NEWID() ou NEWSEQUENTIALID() comme valeur par défaut pour la colonne.

[ {ADD | CHUTE} PERSISTÉ ]

Spécifie que la PERSISTED propriété 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 en tant que PERSISTED, le moteur de base de données stocke physiquement les valeurs calculées dans la table et met à jour les valeurs quand toutes les autres colonnes sur lesquelles dépend la colonne calculée sont mises à jour. En marquant une colonne calculée comme PERSISTED, vous pouvez créer des index sur des colonnes calculées définies sur des expressions déterministes, mais pas précises. Pour plus d’informations, consultez Index sur les colonnes calculées.

SET QUOTED_IDENTIFIER doit être ON lorsque vous créez ou modifiez des index sur des colonnes calculées ou des vues indexées. Pour plus d’informations, voir SET QUOTED_IDENTIFIER.

Toute colonne calculée utilisée comme colonne de partitionnement d’une table partitionnée doit être marquée PERSISTEDexplicitement.

Note

Dans la base de données Fabric SQL, les colonnes calculées sont autorisées, mais elles ne sont actuellement pas mises en miroir vers Fabric OneLake.

NE PAS ÊTRE LARGUÉ POUR LA RÉPLICATION

S’applique à : SQL Server et Azure SQL Database.

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. Vous ne pouvez spécifier cette clause que si column_name est une colonne d’identité.

SPARSE

Indique que la nouvelle colonne est une colonne éparse. Le stockage des colonnes éparses est optimisé pour les valeurs Null. Vous ne pouvez pas définir des colonnes NOT NULLéparses comme . Lorsque vous convertissez une colonne éparse en colonne non éparse ou inversement, cette option verrouille la table pendant la durée de l'exécution de la commande. Vous devrez peut-être utiliser la REBUILD clause pour récupérer les économies d’espace. Pour obtenir des restrictions supplémentaires et plus d’informations sur les colonnes éparses, consultez Utiliser des colonnes éparses.

AJOUTER MASQUÉ AVEC ( FUNCTION = 'mask_function')

S’applique à : SQL Server 2016 (13.x) et versions ultérieures, et Azure SQL Database.

Spécifie un masque dynamique des données. mask_function est le nom de la fonction de masquage avec les paramètres appropriés. Quatre fonctions sont disponibles :

• default()
• email()
• partial()
• random()

Nécessite ALTER ANY MASK une autorisation.

Pour supprimer un masque, utilisez DROP MASKED. Pour connaître les paramètres de fonction, consultez Masquage des données dynamiques.

Ajouter et supprimer un masque nécessite ALTER ANY MASK une autorisation.

WITH ( ONLINE = ON | OFF) <s’applique à la modification d’une colonne>

S’applique à : SQL Server 2016 (13.x) et versions ultérieures, et Azure SQL Database.

Permet d'effectuer de nombreuses actions de modification de colonne pendant que la table reste disponible. La valeur par défaut est OFF. Vous pouvez exécuter la modification de colonne en ligne pour les modifications de colonne liées à un type de données, à la longueur de colonne ou à la précision, à la possibilité de valeur null, au caractère épars et au classement.

La colonne alter en ligne permet aux utilisateurs créés et autostatistiques de référencer la colonne modifiée pendant la durée de l’opération ALTER COLUMN , ce qui permet aux requêtes de s’exécuter comme d’habitude. À la fin de l'opération, les statistiques automatiques qui font référence à la colonne sont supprimées et les statistiques créées par l'utilisateur sont invalidées. L'utilisateur doit mettre à jour manuellement les statistiques générées par l'utilisateur une fois l'opération terminée. Si la colonne fait partie d’une expression de filtre pour des index ou les statistiques, vous ne pouvez pas effectuer une opération ALTER COLUMN.

• Pendant l’exécution de l’opération alter column en ligne, toute opération DDL qui peut dépendre de cette colonne (par exemple, la création ou la modification d’index, de vues, etc.) est bloquée ou échoue avec une erreur appropriée. Ce comportement garantit que l'opération de modification de colonne en ligne n'échouera pas en raison des dépendances introduites pendant son exécution.

• La modification d’une colonne à NOT NULL n’est NULL pas prise en charge en tant qu’opération en ligne lorsque la colonne modifiée est référencée par des index non cluster.

• Online ALTER n’est pas pris en charge lorsque la colonne est référencée par une contrainte de vérification et que l’opération ALTER limite la précision de la colonne (numérique ou datetime).

• L'option WAIT_AT_LOW_PRIORITY ne peut pas être utilisée avec la modification de colonne en ligne.

• ALTER COLUMN ... ADD/DROP PERSISTED n’est pas pris en charge pour modifier la colonne en ligne.

• ALTER COLUMN ... ADD/DROP ROWGUIDCOL/NOT FOR REPLICATION n’est pas affecté par la modification de colonne en ligne.

• La modification de colonne en ligne ne prend pas en charge la modification d'une table où le suivi des modifications est activé ou qui est un serveur de publication de la réplication de fusion.

• La modification de colonne en ligne ne prend pas en charge la modification depuis ou vers des types de données CLR.

• La modification de colonne en ligne ne prend pas en charge la modification d'un type de données XML qui possède une collection de schémas différente de la collection de schémas active.

• La modification de colonne en ligne ne réduit pas les restrictions relatives aux périodes de modification possibles d'une colonne. Les références par index/statistiques, etc. peuvent entraîner l’échec de la modification.

• La modification de colonne en ligne ne prend pas en charge la modification simultanée de plusieurs colonnes.

• La modification de colonne en ligne n’a aucun effet dans une table temporelle dont la version est contrôlée par le système. ALTER la colonne n’est pas exécutée en ligne, quelle que soit la valeur spécifiée pour ONLINE l’option.

La modification de colonne en ligne a des exigences, restrictions et fonctionnalités similaires à la reconstruction d'index en ligne, notamment les suivantes :

• La reconstruction d'index en ligne n'est pas prise en charge quand la table contient des colonnes LOB ou filestream héritées, ou quand la table possède un index columnstore. Les mêmes limitations s'appliquent à la modification de colonne en ligne.
• Une colonne existante modifiée nécessite deux fois plus d'allocation d'espace : pour la colonne d'origine et la colonne masquée nouvellement créée.
• La stratégie de verrouillage lors d'une opération de modification de colonne en ligne suit le même modèle de verrouillage utilisé pour la construction d'index en ligne.

AVEC CHÈQUE | AVEC NOCHECK

Spécifie si les données de la table sont ou non validées par rapport à une contrainte ou FOREIGN KEY à une nouvelle CHECK extension. Si vous ne spécifiez pas, WITH CHECK est supposé pour les nouvelles contraintes et WITH NOCHECK est supposé pour les contraintes réactivé.

Si vous ne souhaitez pas vérifier les nouvelles CHECK ou FOREIGN KEY contraintes relatives aux données existantes, utilisez WITH NOCHECK. Ceci n'est pas recommandé, sauf dans quelques cas rares. La nouvelle contrainte est évaluée dans toutes les mises à jour ultérieures. Toute violation de contrainte supprimée lorsque WITH NOCHECK la contrainte est ajoutée peut entraîner l’échec des mises à jour futures si elles mettent à jour des lignes avec des données qui ne suivent pas la contrainte. L’optimiseur de requête ne prend pas en compte les contraintes définies WITH NOCHECK. De telles contraintes sont ignorées tant qu'elles n'ont pas été réactivées à l'aide de ALTER TABLE table WITH CHECK CHECK CONSTRAINT ALL. Pour plus d’informations, consultez Désactiver les contraintes de clé étrangère avec des instructions INSERT et UPDATE.

ALTER INDEX index_name

Spécifie que le nombre de compartiments pour index_name doit être modifié.

La syntaxe ALTER TABLE ... ADD/DROP/ALTER INDEX est prise en charge uniquement pour les tables optimisées en mémoire.

Important

Sans utiliser d’instruction ALTER TABLE , les instructions CREATE INDEX, DROP INDEX, ALTER INDEX et PAD_INDEX ne sont pas prises en charge pour les index sur les tables mémoire optimisées.

ADD

Spécifie qu'une ou plusieurs définitions de colonnes, définitions de colonnes calculées ou contraintes de tables sont ajoutées. Ou bien les colonnes utilisées par le système pour le contrôle de version sont ajoutées. Pour les tables optimisées en mémoire, vous pouvez ajouter un index.

Note

Les nouvelles colonnes sont ajoutées après toutes les colonnes existantes de la table à modifier.

Important

Sans utiliser d’instruction ALTER TABLE , les instructions CREATE INDEX, DROP INDEX, ALTER INDEX et PAD_INDEX ne sont pas prises en charge pour les index sur les tables mémoire optimisées.

POINT POUR SYSTEM_TIME ( system_start_time_column_name, system_end_time_column_name )

S’applique à : SQL Server 2017 (14.x) et versions ultérieures et Azure SQL Database.

Spécifie les noms des colonnes que le système utilise pour enregistrer la période pendant laquelle un enregistrement est valide. Vous pouvez spécifier des colonnes existantes ou créer de nouvelles colonnes dans le cadre de l’argument ADD PERIOD FOR SYSTEM_TIME . Configurez les colonnes avec le type de données datetime2 et définissez-les comme NOT NULL. Si vous définissez une colonne de point en tant que NULL, une erreur se produit. Vous pouvez définir un column_constraint et/ou spécifier des valeurs par défaut pour les colonnes system_start_time et system_end_time. Consultez l’Exemple A, dans les exemples suivants de Gestion système des versions ; il illustre l’utilisation d’une valeur par défaut pour la colonne system_end_time.

Utilisez cet argument avec l’argument SET SYSTEM_VERSIONING pour faire d’une table existante une table temporelle. Pour plus d’informations, consultez tables temporelles et prise en main des tables temporelles.

À partir de SQL Server 2017 (14.x), les utilisateurs peuvent marquer une ou les deux colonnes de période avec HIDDEN un indicateur pour masquer implicitement ces colonnes de sorte qu’elles SELECT * FROM <table_name> ne retournent pas de valeur pour les colonnes. Par défaut, les colonnes de période ne sont pas masquées. Pour pouvoir être utilisées, les colonnes masquées doivent être incluses explicitement dans toutes les requêtes qui référencent directement la table temporelle.

DROP

Spécifie qu’une ou plusieurs définitions de colonnes, définitions de colonnes calculées ou contraintes de tables sont supprimées, ou qu’il faut supprimer la spécification pour les colonnes que le système utilise pour la gestion système des versions.

Note

Les colonnes supprimées dans les tables de registre sont uniquement supprimées de manière réversible. Une colonne supprimée reste dans la table du registre, mais elle est marquée comme une colonne supprimée en définissant la dropped_ledger_table colonne sur sys.tables1. La vue du registre de la table de registre supprimée est également marquée comme supprimée en définissant la colonne dropped_ledger_view dans sys.tables comme étant 1. Une table de registre supprimée, sa table d’historique et sa vue de registre sont renommées en ajoutant un préfixe (MSSQL_DroppedLedgerTable, MSSQL_DroppedLedgerHistory, MSSQL_DroppedLedgerView) et en ajoutant un GUID au nom d’origine.

CONTRAINTE constraint_name

Spécifie que constraint_name est supprimé de la table. Vous pouvez spécifier plusieurs contraintes.

Vous pouvez 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 vues de catalogue sys.check_constraint, sys.default_constraints, sys.key_constraints et sys.foreign_keys.

Une PRIMARY KEY contrainte ne peut pas être supprimée si un index XML existe sur la table.

INDEX index_name

Spécifie que nom_index est supprimé de la table.

La syntaxe ALTER TABLE ... ADD/DROP/ALTER INDEX est prise en charge uniquement pour les tables optimisées en mémoire.

Important

Sans utiliser d’instruction ALTER TABLE , les instructions CREATE INDEX, DROP INDEX, ALTER INDEX et PAD_INDEX ne sont pas prises en charge pour les index sur les tables mémoire optimisées.

COLUMN_NAME COLUMN

Spécifie que constraint_name ou column_name est supprimé de la table. Vous pouvez spécifier plusieurs colonnes.

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

• Utilisé dans un index, qu’il s’agisse d’une colonne clé ou d’une INCLUDE
• Utilisé dans un CHECK, , FOREIGN KEYUNIQUEou PRIMARY KEY une contrainte.
• Associé à une valeur par défaut définie avec le DEFAULT mot clé ou liée à un objet par défaut.
• liée à une règle.

Note

La suppression d'une colonne ne permet pas de récupérer l'espace disque de la colonne. Vous devrez peut-être récupérer l’espace disque d’une colonne supprimée lorsque la taille de ligne d’une table est proche ou a dépassé sa limite. 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. Pour plus d’informations sur l’impact de la suppression de types de données LOB (Large Object), consultez l’entrée de blog CSS.

PÉRIODE DE SYSTEM_TIME

S’applique à : SQL Server 2016 (13.x) et versions ultérieures, et Azure SQL Database.

Supprime la spécification des colonnes que le système utilise pour le contrôle de version du système.

AVEC <drop_clustered_constraint_option>

Spécifie qu'une ou plusieurs options de suppression de contrainte cluster sont définies.

MAXDOP = max_degree_of_parallelism

S’applique à : SQL Server et Azure SQL Database.

Remplace l’option de configuration max degree of parallelism seulement pendant la durée de l’opération. Pour plus d’informations, consultez Configuration du serveur : degré maximal de parallélisme.

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

max_degree_of_parallelism peut prendre l’une des valeurs suivantes :

• 1

  Supprime la création de plans parallèles.

• >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 moins en fonction de la charge de travail système actuelle.

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

Note

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

ONLINE = { ON | OFF } <s’applique à drop_clustered_constraint_option>

Indique si les tables sous-jacentes et les index associés sont disponibles pour les requêtes et la modification de données pendant l'opération d'index. La valeur par défaut est OFF. Vous pouvez exécuter REBUILD 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. Pendant la phase principale de l’opération d’index, seul un verrou Intent Share (IS) est conservé sur la table source. Ceci 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) est maintenu sur l'objet source pendant une période de temps courte. À la fin de l’opération, pendant une courte période, un verrou S (partagé) est acquis sur la source si un index non-cluster est créé. Ou, un verrou Sch-M (modification de schéma) est acquis lorsqu’un index cluster est créé ou supprimé en ligne et lorsqu’un index cluster ou non cluster est reconstruit. ONLINE ne peut pas être défini ON sur le moment où un index est créé sur une table temporaire locale. Seule l'opération de reconstruction de segment monothread est autorisée.

  Pour exécuter la DDL pour ou la SWITCH reconstruction d’index en ligne, toutes les transactions bloquantes actives exécutées sur une table particulière doivent être terminées. Lors de l’exécution, l’opération SWITCH ou la reconstruction empêche les nouvelles transactions de démarrer et peut affecter considérablement le débit de la charge de travail et retarder temporairement l’accès à la table sous-jacente.

• OFF

  Des verrous de table s’appliquent 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. Ce verrou 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. Ce verrou empêche les mises à jour de la table sous-jacente, mais autorise les opérations de lecture, telles que SELECT les instructions. Les opérations de reconstruction de segment multithread sont autorisées.

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

  Note

  Les opérations d’index en ligne ne sont pas disponibles dans chaque édition de SQL Server. Pour plus d’informations, consultez Éditions et fonctionnalités prises en charge de SQL Server 2022.

MOVE TO { partition_scheme_name(column_name [ ,... n ] ) | groupe de fichiers | « default » }

S’applique à : SQL Server et Azure SQL Database.

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. Cette option s'applique uniquement aux contraintes qui créent un index cluster.

Note

Dans ce contexte, default n’est pas un mot clé. Il s’agit d’un identificateur pour le groupe de fichiers par défaut et doit être délimité, comme dans MOVE TO "default" ou MOVE TO [default]. Si "default" elle est spécifiée, l’option QUOTED_IDENTIFIER doit être ON pour la session active. Il s'agit du paramètre par défaut. Pour plus d’informations, voir SET QUOTED_IDENTIFIER.

{ CHECK | CONTRAINTE NOCHECK }

Spécifie que constraint_name est activé ou désactivé. Cette option ne peut être utilisée qu’avec et FOREIGN KEY avec CHECK des contraintes. Quand NOCHECK elle est spécifiée, la contrainte est désactivée et les prochaines insertions ou mises à jour de la colonne ne sont pas validées par rapport aux conditions de contrainte. DEFAULT, PRIMARY KEYet UNIQUE les contraintes ne peuvent pas être désactivées.

• ALL

  Spécifie que toutes les contraintes sont désactivées avec l’option NOCHECK ou activées avec l’option CHECK .

{ ENABLE | DÉSACTIVER } TRIGGER

Spécifie que trigger_name est activé ou désactivé. Lorsqu’un déclencheur est désactivé, il reste défini pour la table. Toutefois, lorsque INSERT, UPDATEou DELETE des instructions s’exécutent sur la table, les actions du déclencheur ne sont pas effectuées tant que le déclencheur n’est pas 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 | DÉSACTIVER } CHANGE_TRACKING

S’applique à : SQL Server et Azure SQL Database.

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 les options ALTER DATABASE SET.

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

AVEC ( TRACK_COLUMNS_UPDATED = { ON | OFF } )

S’applique à : SQL Server et Azure SQL Database.

Spécifie, si le Moteur de base de données effectue un suivi des modifications, quelles colonnes de suivi des modifications ont été mises à jour. La valeur par défaut est OFF.

BASCULE [ PARTITION source_partition_number_expression ] VERS [ schema_name. ] target_table [PARTITION target_partition_number_expression ]

S’applique à : SQL Server et Azure SQL Database.

Insère 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, vous devez spécifier source_partition_number_expression. Si targe_table est une table partitionnée, vous devez spécifier source_partition_number_expression. Lors de la 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.

Lors de la réaffectation des données d'une partition pour constituer une seule table, la table cible doit déjà exister 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. 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. Ces arguments ne peuvent pas référencer des expressions Transact-SQL.

Une table partitionnée avec un index columnstore cluster se comporte comme un segment partitionné :

• La clé primaire doit inclure la clé de partition.
• Un index unique doit inclure la clé de partition. Le fait d’inclure la clé de partition dans un index unique existant peut cependant changer l’unicité.
• Pour changer de partition, tous les index non-cluster doivent inclure la clé de partition.

Pour SWITCH plus d’informations sur la restriction lors de l’utilisation de la réplication, consultez Répliquer des tables et des index partitionnés.

Les index columnstore non clusters ont été générés au format de lecture seule avant SQL Server 2016 (13.x) et pour SQL Database avant la version V12. Vous devez reconstruire des index columnstore non cluster au format actuel (qui est pouvant être mis à jour) avant que toutes les PARTITION opérations puissent être exécutées.

Limitations

Si les deux tables sont partitionnés de manière identique, y compris les index non cluster et que la table cible n’a pas d’index non cluster, vous pouvez recevoir une erreur 4907.

Exemple de sortie :

Msg 4907, Level 16, State 1, Line 38
'ALTER TABLE SWITCH' statement failed. The table 'MyDB.dbo.PrtTable1' has 4 partitions while index 'MS1' has 6 partitions.

SET ( FILESTREAM_ON = { partition_scheme_name filestream_filegroup_name | | « default » | « NULL » })

S’applique à : SQL Server. Azure SQL Database ne prend pas en charge FILESTREAM.

Spécifie où les données FILESTREAM sont stockées.

ALTER TABLE avec la SET FILESTREAM_ON clause réussit uniquement si la table n’a pas de colonnes FILESTREAM. Vous pouvez ajouter des colonnes FILESTREAM à l’aide d’une deuxième ALTER TABLE instruction.

Si vous spécifiez partition_scheme_name, les règles en vigueur pour CREATE TABLE s’appliquent. Assurez-vous que la table est déjà partitionnée pour les données de lignes et que son schéma de partition utilise les mêmes fonction et colonnes de partition que le schéma de partition FILESTREAM.

filestream_filegroup_name spécifie le nom d’un groupe de fichiers FILESTREAM. Le groupe de fichiers doit avoir un fichier défini pour le groupe de fichiers à l’aide d’une instruction CREATE DATABASE ou ALTER DATABASE , ou vous obtenez une erreur.

"default" spécifie le groupe de fichiers FILESTREAM avec le jeu de DEFAULT propriétés. S’il n’existe aucun groupe de fichiers FILESTREAM, vous obtenez une erreur.

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

SET ( SYSTEM_VERSIONING = { OFF | ON [ ( HISTORY_TABLE = schema_name . history_table_name [ , DATA_CONSISTENCY_CHECK = { ON | OFF } ] ] } )

S’applique à : SQL Server 2016 (13.x) et versions ultérieures, et Azure SQL Database.

Active ou désactive la gestion système des versions d’une table. Pour activer le contrôle de version système d’une table, le système vérifie que le type de données, la contrainte nullabilité et les exigences de contrainte de clé primaire pour le contrôle de version du système sont remplies. Le système enregistre l’historique de chaque enregistrement dans la table avec version système dans une table d’historique distincte. Si l’argument HISTORY_TABLE n’est pas utilisé, le nom de cette table d’historique est MSSQL_TemporalHistoryFor<primary_table_object_id>. Si la table d’historique n’existe pas, le système génère une nouvelle table d’historique correspondant au schéma de la table actuelle, crée un lien entre les deux tables et permet au système d’enregistrer l’historique de chaque enregistrement de la table actuelle dans la table d’historique. Si vous utilisez l’argument HISTORY_TABLE pour créer un lien vers une table d’historique existante et utiliser cette table, le système crée un lien entre la table actuelle et la table spécifiée. Lorsque vous créez un lien vers une table de l’historique existante, vous pouvez choisir d’effectuer une vérification de cohérence des données. Cette vérification de cohérence des données garantit que les enregistrements existants ne se chevauchent pas. La vérification de cohérence des données est effectuée par défaut. Utilisez l’argument SYSTEM_VERSIONING = ON sur une table qui est définie avec la clause PERIOD FOR SYSTEM_TIME pour transformer la table existante en table temporelle. Pour plus d’informations, consultez tables temporelles.

HISTORY_RETENTION_PERIOD = { INFINITE | number { DAY | JOURS | SEMAINE | SEMAINES | MOIS | MOIS | ANNÉE | YEARS } } }

S’applique à  : SQL Server 2017 (14.x) et Azure SQL Database.

Spécifie la rétention finie ou infinie des données d’historique dans une table temporelle. Si vous l’omettez, la rétention infinie est appliquée.

DATA_DELETION

S’applique à : Azure SQL Edge uniquement

Active le nettoyage basé sur la stratégie de rétention des données anciennes dans les tables d’une base de données. Pour plus d’informations, consultez Activer et désactiver la rétention des données. Les paramètres suivants doivent être spécifiés pour que la rétention des données soit activée.

• FILTER_COLUMN = { column_name }

  Spécifie la colonne à utiliser pour déterminer si les lignes de la table sont obsolètes ou non. Les types de données suivants sont autorisés pour la colonne de filtre.

  • date
  • datetime
  • datetime2
  • smalldatetime
  • datetimeoffset

• RETENTION_PERIOD = { INFINITE | number { DAY | JOURS | SEMAINE | SEMAINES | MOIS | MOIS | ANNÉE | YEARS } } }

  Spécifie la stratégie de la période de rétention pour la table. La période de rétention est spécifiée sous forme de combinaison d’une valeur entière positive et de l’unité de la partie date.

SET ( LOCK_ESCALATION = { AUTO | TABLEAU | DÉSACTIVER } )

S’applique à : SQL Server et Azure SQL Database.

Spécifie les méthodes autorisées d'escalade de verrous 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 autorisée à la granularité du tas ou de l’arbre B (HoBT). En d’autres termes, l’escalade est autorisée au niveau de la partition. Une fois le verrou passé au niveau HoBT, le verrou ne sera pas réaffecté ultérieurement à TABLE la granularité.

  • Si la table n’est pas partitionnée, l’escalade de verrous est effectuée à la TABLE granularité.

• TABLE

  L'escalade de verrous continue jusqu'à la granularité TABLE, que la table soit ou non partitionnée. 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 ne contenant 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 REBUILD WITH syntaxe pour reconstruire une table entière, y compris 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 exécuté en tant qu’opération ONLINE .

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

PARTITION = TOUS

S’applique à : SQL Server et Azure SQL Database.

Reconstruit toutes les partitions lors de la modification des paramètres de compression de la partition.

RECONSTRUIRE AVEC ( <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.

Lorsqu’un paramètre de compression spécifique n’est pas spécifié avec l’opération REBUILD , le paramètre de compression actuel de la partition est utilisé. Pour retourner la valeur actuelle, interrogez la colonne data_compression dans la vue de catalogue sys.partitions.

Pour une description complète des options de reconstruction, consultez l’article ALTER TABLE index_option.

DATA_COMPRESSION

S’applique à : SQL Server et Azure SQL Database.

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

  Les partitions de table ou spécifiées ne sont pas compressées. Cette option ne s'applique pas aux tables columnstore.

• RAMER

  Les partitions de table ou spécifiées sont compressées à l’aide de la compression de ligne. Cette option ne s'applique pas aux tables columnstore.

• PAGE

  Les partitions de table ou spécifiées sont compressées à l’aide de la compression de page. Cette option ne s'applique pas aux tables columnstore.

• COLUMNSTORE

  S’applique à : SQL Server 2014 (12.x) et versions ultérieures et Azure SQL Database.

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

• COLUMNSTORE_ARCHIVE

  S’applique à : SQL Server 2014 (12.x) et versions ultérieures et Azure SQL Database.

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

  Pour reconstruire plusieurs partitions en même temps, consultez l’article index_option. 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 Compression des données.

  ALTER TABLE REBUILD PARTITION WITH DATA COMPRESSION = ROW ou PAGE n’est pas autorisé sur la base de données SQL dans Microsoft Fabric.

XML_COMPRESSION

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

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

• ON

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

• OFF

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

ONLINE = { ON | OFF } <comme appliqué à single_partition_rebuild_option>

Spécifie si une seule partition des tables sous-jacentes et les index associés sont disponibles pour modifier des requêtes et des données pendant l'opération d'index. La valeur par défaut est OFF. Vous pouvez exécuter REBUILD 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. Un verrou S sur la table est requis au début de la reconstruction de l'index, et un verrou Sch-M sur la table à la fin de la reconstruction de l'index en ligne. Bien que les deux verrous soient des verrous de métadonnées courtes, le verrou Sch-M doit attendre que toutes les transactions bloquantes soient terminées. Pendant le temps d’attente, le verrou Sch-M bloque toutes les autres transactions en attente derrière ce verrou en cas d’accès à la même table.

  Note

  La reconstruction d’index en ligne peut définir les options low_priority_lock_wait décrites ultérieurement dans cette section.

• OFF

  Des verrous de table sont appliqués pendant l'opération d'indexation. Cela empêche tous les utilisateurs d'accéder à la table sous-jacente pendant la durée de l'opération.

column_set_name COLUMN_SET XML POUR ALL_SPARSE_COLUMNS

S’applique à : SQL Server et Azure SQL Database.

Nom du jeu de colonnes. Un jeu de colonnes est une représentation XML non typée qui combine toutes les colonnes éparses d'une table dans une sortie structurée. Un jeu de colonnes ne peut pas être ajouté à une table qui contient des colonnes éparses. Pour plus d’informations sur les jeux de colonnes, consultez Utiliser des jeux de colonnes.

{ ENABLE | DÉSACTIVER } FILETABLE_NAMESPACE

S’applique à : SQL Server.

Active ou désactive les contraintes définies par le système sur un FileTable. Peut être utilisé uniquement avec un FileTable.

SET ( FILETABLE_DIRECTORY = directory_name )

S’applique à : SQL Server. Azure SQL Database ne prend pas en charge FileTable.

Spécifie le nom de répertoire FileTable compatible Windows. Ce nom doit être unique parmi tous les noms de répertoire FileTable de la base de données. La comparaison d'unicité n'est pas sensible à la casse, malgré les paramètres de classement SQL. Peut être utilisé uniquement avec un FileTable.

REMOTE_DATA_ARCHIVE

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

Active ou désactive Stretch Database pour une table. Pour plus d'informations, consultez Stretch Database.

Important

Stretch Database est déprécié dans SQL Server 2022 (16.x) et Azure SQL Database. Cette fonctionnalité sera supprimée dans une version future de moteur de base de données. Évitez d'utiliser cette fonctionnalité dans de nouveaux travaux de développement, et prévoyez de modifier les applications qui utilisent actuellement cette fonctionnalité.

Activer Stretch Database pour une table

Quand vous activez Stretch pour une table en spécifiant ON, vous devez aussi spécifier MIGRATION_STATE = OUTBOUND pour commencer à migrer les données immédiatement, ou MIGRATION_STATE = PAUSED pour reporter la migration des données. La valeur par défaut est MIGRATION_STATE = OUTBOUND. Pour plus d’informations sur l’activation de Stretch pour une table, consultez Activer Stretch Database pour une table.

Prerequisites. Avant d’activer Stretch pour une table, vous devez l’activer sur le serveur et sur la base de données. Pour plus d'informations, consultez Enable Stretch Database for a database.

Permissions. L’activation de Stretch pour une table ou une base de données nécessite les autorisations db_owner. L’activation de Stretch pour une table nécessite ALTER également des autorisations sur la table.

Désactiver Stretch Database pour une table

Quand vous désactivez Stretch pour une table, vous avez deux options pour les données distantes qui ont déjà été migrées vers Azure. Pour plus d’informations, consultez Désactiver Stretch Database et récupérer les données distantes.

• Pour désactiver Stretch Database pour une table et copier les données distantes de la table à partir d’Azure vers SQL Server, exécutez la commande suivante. Cette commande ne peut pas être annulée.

  ALTER TABLE <table_name>
     SET ( REMOTE_DATA_ARCHIVE ( MIGRATION_STATE = INBOUND ) ) ;
  

Cette opération entraîne des coûts de transfert de données et ne peut pas être annulée. Pour plus d'informations, consultez la rubrique Détails de la tarification des transferts de données.

Une fois que toutes les données distantes ont été copiées d'Azure vers SQL Server, Stretch est désactivée pour la table.

• Pour désactiver Stretch pour une table et abandonner les données distantes, exécutez la commande suivante.

  ALTER TABLE <table_name>
     SET ( REMOTE_DATA_ARCHIVE = OFF_WITHOUT_DATA_RECOVERY ( MIGRATION_STATE = PAUSED ) ) ;
  

Une fois que vous avez désactivé Stretch Database pour une table, la migration des données s’arrête et les résultats de requête n’incluent plus les résultats de la table distante.

La désactivation de Stretch ne supprime pas la table distante. Si vous souhaitez supprimer la table distante, vous devez utiliser le Portail Azure.

[ FILTER_PREDICATE = { null | prédicat } ]

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

Spécifie éventuellement un prédicat de filtre pour sélectionner des lignes à migrer à partir d’une table qui contient des données historiques et actuelles. Le prédicat doit appeler une fonction table inline déterministe. Pour plus d’informations, consultez les articles Activer Stretch Database pour une table et Sélectionner les lignes à migrer à l’aide d’une fonction de filtre(Stretch Database).

Important

Si vous fournissez un prédicat de filtre qui fonctionne mal, la migration des données fonctionne mal également. Stretch Database applique le prédicat de filtre à la table à l’aide de l’opérateur CROSS APPLY .

Si vous ne spécifiez aucun prédicat de filtre, la table entière est migrée.

Lorsque vous spécifiez un prédicat de filtre, vous devez également spécifier MIGRATION_STATE.

MIGRATION_STATE = { OUTBOUND | ENTRANT | PAUSED }

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

• Spécifiez OUTBOUND pour migrer des données de SQL Server vers Azure.

• Spécifiez INBOUND pour copier les données distantes pour la table d’Azure vers SQL Server, et pour désactiver Stretch pour la table. Pour plus d’informations, consultez Désactiver Stretch Database et récupérer les données distantes.

  Cette opération entraîne des coûts de transfert de données et ne peut pas être annulée.

• Spécifiez PAUSED pour interrompre ou reporter la migration des données. Pour plus d’informations, consultez l’article Suspension et reprise de la migration de données (Stretch Database).

WAIT_AT_LOW_PRIORITY

S’applique à : SQL Server 2014 (12.x) et versions ultérieures et Azure SQL Database.

Une reconstruction d'index en ligne doit attendre les opérations de blocage sur cette table. WAIT_AT_LOW_PRIORITY indique que l’opération de reconstruction d’index en ligne attend les verrous de faible priorité, ce qui permet à d’autres opérations de continuer pendant l’attente de l’opération de génération d’index en ligne. Omettre l’option WAIT AT LOW PRIORITY est identique à WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE).

MAX_DURATION = heure [ MINUTES ]

S’applique à : SQL Server 2014 (12.x) et versions ultérieures et Azure SQL Database.

Temps d’attente, qui est une valeur entière spécifiée en minutes, que l’index SWITCH ou l’index en ligne régénère attend avec une priorité faible lors de l’exécution de la commande DDL. Si l’opération est bloquée pour le MAX_DURATION moment, l’une des ABORT_AFTER_WAIT actions s’exécute. MAX_DURATION l’heure est toujours en minutes, et vous pouvez omettre le mot MINUTES.

ABORT_AFTER_WAIT = { AUCUN | SELF | BLOQUEURS }

S’applique à : SQL Server 2014 (12.x) et versions ultérieures et Azure SQL Database.

• NONE

  Continuez à attendre le verrou avec la priorité normale.

• SELF

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

• BLOCKERS

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

  Nécessite ALTER ANY CONNECTION une autorisation.

S’IL EXISTE

S’applique à : SQL Server 2016 (13.x) et versions ultérieures, et Azure SQL Database.

Supprime de manière conditionnelle la colonne ou contrainte uniquement si elle existe déjà.

RESUMIBLE = { ON | OFF}

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

Spécifie si une opération ALTER TABLE ADD CONSTRAINT peut être reprise. Une opération d’ajout de contrainte de table peut être reprise quand ON. Une opération d’ajout de contrainte de table ne peut pas être reprise quand OFF. La valeur par défaut est OFF. L’option RESUMABLE peut être utilisée dans le cadre de ALTER TABLE index_option dans ALTER TABLE table_constraint.

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

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

Remarks

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 sur les lignes existantes, utilisez l’instruction UPDATE.

S’il existe des plans d’exécution dans le cache de procédure qui référencent la table, ALTER TABLE les marque à recompiler lors de leur prochaine exécution.

Dans la base de données SQL de Microsoft Fabric, certaines fonctionnalités de table peuvent être créées mais ne sont pas reflétées dans Fabric OneLake. Pour plus d’informations, voir Limitations pour le miroirement de bases de données SQL Fabric.

Changer 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. Utilisez la ALTER COLUMN clause. 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, vous ne pouvez pas définir la colonne dans un index, sauf si la colonne est un type de données varchar, nvarchar ou varbinary et que l’index n’est pas le résultat d’une PRIMARY KEY contrainte. Consultez l’exemple dans la courte section intitulée Modification d’une définition de colonne.

Verrous et ALTER TABLE

Modifications que vous spécifiez immédiatement dans ALTER TABLE l’implémentation. Si les modifications nécessitent des modifications des lignes de la table, ALTER TABLE met à jour les lignes. ALTER TABLE acquiert un verrou de modification de schéma (Sch-M) sur la table pour s’assurer 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 verrou court 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 grandes tables, telles que la suppression d’une colonne ou, dans certaines éditions de SQL Server, l’ajout d’une NOT NULL colonne avec une valeur par défaut, peut prendre beaucoup de temps pour terminer et générer de nombreux enregistrements de journal. Exécutez ces ALTER TABLE instructions avec le même soin que n’importe quel INSERT, UPDATEou DELETE instruction qui affecte de nombreuses lignes.

S’applique à Warehouse dans Microsoft Fabric.

ALTER TABLE ne peut pas faire partie d’une transaction explicite.

Événements étendus (XEvents) pour le commutateur de partition

Les XEvents suivant sont liés à et pour partitionner ALTER TABLE ... SWITCH PARTITION et regénèrent l’index en ligne.

• lock_request_priority_state
• process_killed_by_abort_blockers
• ddl_with_wait_at_low_priority

Ajouter des colonnes NOT NULL en tant qu'opération en ligne

Dans SQL Server 2012 (11.x) Édition Entreprise et versions ultérieures, l’ajout d’une colonne avec une NOT NULL valeur par défaut est une opération en ligne lorsque la valeur par défaut est une constante runtime. Cela signifie que l’opération est terminée presque instantanément malgré le nombre de lignes de la table, car les lignes existantes de la table ne sont pas mises à jour pendant l’opération. À la place, la valeur par défaut est stockée uniquement dans les métadonnées de la table et la valeur se trouve autant que nécessaire dans les requêtes qui accèdent à ces lignes. Ce comportement est automatique. Aucune syntaxe supplémentaire n’est nécessaire pour implémenter l’opération en ligne au-delà de la ADD COLUMN syntaxe. Une constante d'exécution est une expression qui produit la même valeur au moment de l'exécution pour chaque ligne dans la table malgré son déterminisme. Par exemple, l’expression "My temporary data"constante ou la fonction GETUTCDATETIME() système sont des constantes runtime. Par opposition, les fonctions NEWID() ou NEWSEQUENTIALID() ne sont pas des constantes d’exécution car une valeur unique est produite pour chaque ligne de la table. L’ajout d’une NOT NULL colonne avec une valeur par défaut qui n’est pas une constante d’exécution est toujours exécutée hors connexion et un verrou exclusif (Sch-M) est acquis pendant la durée de l’opération.

Alors que les lignes existantes référencent la valeur stockée dans les métadonnées, la valeur par défaut est stockée dans la ligne pour toutes les nouvelles lignes qui sont insérées et ne spécifient pas d’autre valeur pour la colonne. La valeur par défaut stockée dans les métadonnées passe à une ligne existante lorsque la ligne est mise à jour (même si la colonne réelle n’est pas spécifiée dans l’instruction UPDATE ) ou si l’index table ou cluster est reconstruit.

Les colonnes de type varchar(max), nvarchar(max), varbinary(max), xml, text, ntext, image, hierarchyid, geometry, geography ou CLR définis par l’utilisateur ne peuvent pas être ajoutées dans une opération en ligne. Une colonne ne peut pas être ajoutée en ligne si cela entraîne le dépassement de la limite de 8 060 octets pour la taille de la ligne. Dans ce cas, la colonne est ajoutée en tant que traitement en différé.

Exécution d'un plan en parallèle

Dans SQL Server 2012 (11.x) Édition Entreprise et versions ultérieures, le nombre de processeurs utilisés pour exécuter une instruction unique 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 la charge de travail actuelle. 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 . Pour plus d’informations, consultez Configuration du serveur : degré maximal de parallélisme.

tables partitionnées ;

En plus d’effectuer des opérations SWITCH qui impliquent des tables partitionnés, utilisez ALTER TABLE pour modifier l’état des colonnes, des contraintes et des déclencheurs d’une table partitionnée comme elle est utilisée pour les tables nonpartitionnelles. Cependant, cette instruction ne peut pas être utilisée 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 qui s’appliquent aux instructions sur les tables avec des vues liées au ALTER TABLE schéma sont identiques aux restrictions actuellement appliquées lors de la modification de tables avec 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 nécessite 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 l’article CREATE VIEW.

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. Les index qui CREATE INDEX ont été créés doivent être supprimés avec DROP INDEX. Utilisez l’instruction ALTER INDEX pour reconstruire une partie d’index d’une définition de contrainte ; la contrainte n’a pas besoin d’être supprimée et ajoutée à nouveau avec ALTER TABLE.

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

Lorsque vous supprimez une contrainte qui a créé un index cluster, 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 obtenue vers un autre groupe de fichiers ou schéma de partition dans une seule transaction en spécifiant l’option MOVE TO . L’option MOVE TO a les restrictions suivantes :

• MOVE TO n’est pas valide pour les vues indexées ou les index non cluster.

• Le schéma de partition ou le groupe de fichiers doit déjà exister.

• Si MOVE TO elle n’est pas spécifiée, la table se trouve dans le même schéma de partition ou le même groupe de fichiers que celui défini pour l’index cluster.

Lorsque vous supprimez un index cluster, spécifiez l’option ONLINE = ON afin que la DROP INDEX transaction ne bloque pas les requêtes et les modifications apportées aux données sous-jacentes et aux index non cluster associés.

ONLINE = ON a les 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 à l’aide ONLINE = OFFde .
• 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 les tables temporaires locales.
• ONLINE = ON n’est pas valide pour les index columnstore.

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.

Note

Les options répertoriées sous <drop_clustered_constraint_option> s’appliquent aux index cluster sur les tables et ne peuvent pas être appliquées aux index cluster sur les vues ou les index non cluster.

Répliquer les modifications de schéma

Lorsque vous exécutez ALTER TABLE une table publiée sur un serveur de publication SQL Server, par défaut, cette modification se propage à tous les abonnés SQL Server. Cette fonctionnalité comporte des restrictions. Vous pouvez la désactiver. Pour plus d’informations, consultez Modifier le schéma dans les bases de données de publication.

Compression des données

Les tables système ne peuvent pas être activées pour la compression. Si la table est un tas, l’opération de reconstruction pour ONLINE le mode est à thread unique. Utilisez OFFLINE le mode pour une opération de reconstruction de tas multithread. Pour plus d’informations sur la compression des données, consultez Compression des données.

Pour évaluer comment la modification de l’état de compression affecte une table, un index ou une partition, utilisez la procédure stockée système 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.

Supprimer des colonnes ntext

Lors de la suppression de colonnes à l’aide du type de données ntext déconseillé, le nettoyage des données supprimées se produit en tant qu’opération sérialisée sur toutes les lignes. Le nettoyage peut prendre beaucoup de temps. Lors de la suppression d’une colonne ntext dans une table avec un grand nombre de lignes, mettez à jour la colonne ntext en NULL valeur, puis supprimez la colonne. Vous pouvez exécuter cette option avec des opérations parallèles et la rendre beaucoup plus rapide.

Reconstruction d’index en ligne

Pour exécuter l'instruction DDL pour une reconstruction d'index en ligne, toutes les transactions bloquantes actives qui s'exécutent sur une table particulière doivent être terminées. Lorsque la reconstruction d'index en ligne est lancée, elle bloque toutes les nouvelles transactions qui sont prêtes à s'exécuter sur cette table. Bien que la durée du verrou pour la reconstruction de l'index en ligne soit courte, le fait d'attendre que toutes les transactions ouvertes sur une table spécifique soient exécutées, et le fait de bloquer les nouvelles transactions qui doivent démarrer, peuvent avoir un impact important sur le débit. Ceci peut entraîner un ralentissement ou un délai d'expiration de la charge de travail et limiter significativement l’accès à la table sous-jacente. L’option WAIT_AT_LOW_PRIORITY permet aux administrateurs de base de données de gérer les verrous S-lock et Sch-M requis pour les reconstructions d’index en ligne. Dans les trois cas suivants : NONE, SELFet BLOCKERS, si pendant le temps d’attente ((MAX_DURATION = n [minutes])) il n’y a aucune activité bloquante, la reconstruction d’index en ligne est exécutée immédiatement sans attendre et l’instruction DDL est terminée.

Prise en charge de la compatibilité

L’instruction ALTER TABLE ne prend en charge que les noms de tables en deux parties (schema.object). Dans SQL Server, la spécification d'un nom de table à l'aide des formats suivants échoue au moment de la compilation, avec l'erreur 117.

• server.database.schema.table
• .database.schema.table
• ..schema.table

Dans les versions antérieures, la spécification du format server.database.schema.table retournait l'erreur 4902. La spécification du format .database.schema.table ou du format ..schema.table a réussi.

Pour résoudre le problème, supprimez l'utilisation d'un préfixe en quatre parties.

Permissions

Nécessite ALTER une autorisation sur la table.

ALTER TABLE les autorisations s’appliquent aux deux tables impliquées dans une ALTER TABLE SWITCH instruction. Toute donnée basculée hérite de la sécurité de la table cible.

Si vous avez défini des colonnes dans l’instruction ALTER TABLE pour qu’elles soient d’un type clR (Common Language Runtime) défini par l’utilisateur ou d’un type de données alias, REFERENCES l’autorisation sur le type est requise.

L’ajout ou la modification d’une colonne qui met à jour les lignes de la table nécessite UPDATE une autorisation sur la table. Par exemple, l’ajout d’une NOT NULL colonne avec une valeur par défaut ou l’ajout d’une colonne d’identité lorsque la table n’est pas vide.

Examples

Les exemples de code de cet article utilisent la base de données ou AdventureWorks2025 l’exemple AdventureWorksDW2025 de base de données, que vous pouvez télécharger à partir de la page d’accueil microsoft SQL Server Samples and Community Projects.

Category	Éléments syntaxiques proposés
Ajout de colonnes et de contraintes	ADD; PRIMARY KEY avec les options d’index, les colonnes éparses et les jeux de colonnes
Suppression de colonnes et de contraintes	DROP
Modification d’une définition de colonne	modifier le type de données ; modifier la taille des colonnes ; Classement
Modification d’une définition de table	DATA_COMPRESSION; SWITCH PARTITION; LOCK ESCALATION suivi des modifications
Désactivation et activation des contraintes et des déclencheurs	CHECK; NO CHECK; ENABLE TRIGGERDISABLE TRIGGER
Opérations en ligne	ONLINE
Contrôle de version du système	SYSTEM_VERSIONING

Ajouter des colonnes et des contraintes

Les exemples fournis dans cette section expliquent comment ajouter des colonnes et des contraintes à une table.

A. Ajout d'une nouvelle colonne

L’exemple suivant ajoute une colonne qui autorise les valeurs Null et n’a aucune valeur fournie par le biais d’une DEFAULT définition. Dans la nouvelle colonne, chaque ligne a NULL.

CREATE TABLE dbo.doc_exa (column_a INT);
GO

ALTER TABLE dbo.doc_exa
    ADD column_b VARCHAR (20) NULL;
GO

B. Ajouter 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

EXECUTE sp_help doc_exc;
GO

DROP TABLE dbo.doc_exc;
GO

C. Ajouter 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

EXECUTE sp_help doc_exd;
GO

DROP TABLE dbo.doc_exd;
GO

D. Ajouter une contrainte DEFAULT à une colonne existante

L’exemple suivant crée une table avec deux colonnes et insère une valeur dans la première colonne, et l’autre colonne reste 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

E. Ajouter 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 a PRIMARY KEY identity column.
    ADD 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 FOREIGN KEY 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

EXECUTE sp_help doc_exe;
GO

DROP TABLE dbo.doc_exe;
GO

F. Ajouter 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 WITH VALUES elle n’est pas utilisée, chaque ligne a la valeur NULL dans la nouvelle colonne.

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
        CONSTRAINT AddDateDflt DEFAULT GETDATE() WITH VALUES NULL;
GO

DROP TABLE dbo.doc_exf;
GO

G. Créer une contrainte PRIMARY KEY avec des options d’index ou de compression des données

L’exemple suivant crée la PRIMARY KEY contrainte PK_TransactionHistoryArchive_TransactionID et définit les options FILLFACTOR, et .ONLINEPAD_INDEX L’index cluster obtenu porte le même nom que la contrainte.

S’applique à : SQL Server et Azure SQL Database.

USE AdventureWorks2022;
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

Cet exemple similaire applique la compression de page lors de l’application de la clé primaire en cluster.

USE AdventureWorks2022;
GO

ALTER TABLE Production.TransactionHistoryArchive WITH NOCHECK
    ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID
        PRIMARY KEY CLUSTERED (TransactionID) WITH (DATA_COMPRESSION = PAGE);
GO

H. Ajouter une colonne éparse

Les exemples suivants illustrent l'ajout et la modification des colonnes éparses dans la table T1. Le code pour créer la table T1 est 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 éparse supplémentaire C5, exécutez l'instruction suivante.

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

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

ALTER TABLE T1
    ALTER COLUMN C4 ADD SPARSE;
GO

Pour convertir la colonne éparse C4 en colonne non éparse, exécutez l’instruction suivante.

ALTER TABLE T1
    ALTER COLUMN C4 DROP SPARSE;
GO

I. Ajouter 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 éparses. Le code pour 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

J. Ajouter une colonne chiffrée

L’instruction suivante ajoute une colonne chiffrée nommée PromotionCode.

ALTER TABLE Customers
    ADD PromotionCode NVARCHAR (100)
        ENCRYPTED WITH (
            COLUMN_ENCRYPTION_KEY = MyCEK,
            ENCRYPTION_TYPE = RANDOMIZED,
            ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
        );

K. Ajouter une clé primaire avec une opération pouvant être reprise

Opération ALTER TABLE pouvant être reprise pour l’ajout d’une clé primaire en cluster sur la colonne (a) avec MAX_DURATION égal à 240 minutes.

ALTER TABLE table1
ADD CONSTRAINT PK_Constrain PRIMARY KEY CLUSTERED (a)
WITH (ONLINE = ON, MAXDOP = 2, RESUMABLE = ON, MAX_DURATION = 240);

Supprimer des colonnes et des contraintes

Les exemples fournis dans cette section expliquent comme supprimer des colonnes et des contraintes.

A. Supprimer une ou plusieurs colonnes

Le premier exemple supprime une colonne dans une table. Le second exemple supprime plusieurs colonnes.

CREATE TABLE dbo.doc_exb
(
    column_a INT,
    column_b VARCHAR (20) NULL,
    column_c DATETIME,
    column_d INT
);
GO

-- Remove a single column.
ALTER TABLE dbo.doc_exb DROP COLUMN column_b;
GO

-- Remove multiple columns.
ALTER TABLE dbo.doc_exb DROP COLUMN column_c, column_d;

B. Supprimer des contraintes et des colonnes

Le premier exemple supprime une contrainte UNIQUE d'une table. Le second exemple supprime deux contraintes et une seule colonne.

CREATE TABLE dbo.doc_exc
(
    column_a INT NOT NULL
        CONSTRAINT my_constraint UNIQUE
);
GO

-- Example 1. Remove a single constraint.
ALTER TABLE dbo.doc_exc DROP my_constraint;
GO

DROP TABLE dbo.doc_exc;
GO

CREATE TABLE dbo.doc_exc
(
    column_a INT NOT NULL
        CONSTRAINT my_constraint UNIQUE,
    column_b INT NOT NULL
        CONSTRAINT my_pk_constraint PRIMARY KEY
);
GO

-- Example 2. Remove two constraints and one column
-- The keyword CONSTRAINT is optional. The keyword COLUMN is required.
ALTER TABLE dbo.doc_exc
    DROP CONSTRAINT my_constraint, my_pk_constraint, COLUMN column_b;
GO

C. Supprimer une contrainte PRIMARY KEY en mode ONLINE

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

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

D. Ajouter et supprimer 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 Person.Person, puis en supprimant la contrainte FOREIGN KEY.

CREATE TABLE Person.ContactBackup (ContactID INT);
GO

ALTER TABLE Person.ContactBackup
    ADD CONSTRAINT FK_ContactBackup_Contact
        FOREIGN KEY (ContactID) REFERENCES Person.Person (BusinessEntityID);
GO

ALTER TABLE Person.ContactBackup
    DROP CONSTRAINT FK_ContactBackup_Contact;
GO

DROP TABLE Person.ContactBackup;

Modifier une définition de colonne

A. Modifier le 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

B. Changer 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 peut toujours être augmentée, car le type de col_a données est un varchar et l’index n’est pas le résultat d’une PRIMARY KEY contrainte.

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

C. Modifier le classement de colonne

L'exemple suivant indique comment modifier le classement d'une colonne. En premier lieu, une table est créée avec le classement de l’utilisateur par défaut.

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

Ensuite, le classement C2 de la colonne est modifié en Latin1_General_BIN. 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

D. Chiffrer une colonne

L’exemple suivant montre comment chiffrer une colonne à l’aide d’Always Encrypted avec enclaves sécurisées.

Tout d’abord, une table est créée sans aucune colonne chiffrée.

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

Ensuite, la colonne « C2 » est chiffrée avec une clé de chiffrement de colonne, nommée CEK1, et un chiffrement aléatoire. Pour que l’instruction suivante réussisse :

• la clé de chiffrement de colonne doit prendre en charge les enclaves. Cela signifie qu’il doit être chiffré avec une clé principale de colonne (CMK) qui autorise les calculs d’enclave.
• L’instance SQL Server cible doit prendre en charge Always Encrypted avec des enclaves sécurisées.
• L’instruction doit être émise via une connexion définie pour Always Encrypted avec des enclaves sécurisées et à l’aide d’un pilote de client pris en charge.
• L’application appelante doit avoir accès à la clé CMK, en protégeant CEK1.

ALTER TABLE T3 ALTER COLUMN C2 VARCHAR (50)  ENCRYPTED WITH (
     COLUMN_ENCRYPTION_KEY = [CEK1],
     ENCRYPTION_TYPE = RANDOMIZED,
     ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
    ) NULL;
GO

Modifier une définition de table

Les exemples présentés dans cette section montrent comment modifier la définition d'une table.

A. Modifier une table pour modifier la compression

L'exemple suivant modifie la compression d'une table non partitionnée. L’index tas ou cluster est reconstruit. Si la table est un tas, tous les index non cluster 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 provoque uniquement la reconstruction de la partition numéro 1.

S’applique à : SQL Server.

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

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

S’applique à : SQL Server.

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

Pour obtenir des exemples de compression de données supplémentaires, consultez compression des données.

B. Modifier une table columnstore pour modifier la compression d'archives

L'exemple suivant compresse davantage une partition de table columnstore en appliquant un algorithme de compression supplémentaire. Cette compression réduit la taille de la table, mais augmente également le temps nécessaire pour le stockage et la récupération. Cela est utile pour l'archivage, ou d'autres situations qui nécessitent moins d'espace de stockage et supportent plus de temps pour le stockage et la récupération.

S’applique à : SQL Server 2014 (12.x) et versions ultérieures et Azure SQL Database.

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

L’exemple suivant décompose une partition de table columnstore qui a été compressée avec COLUMNSTORE_ARCHIVE l’option. Lorsque les données sont restaurées, elles continuent à être compressées à l'aide de la compression columnstore utilisée pour toutes les tables columnstore.

S’applique à : SQL Server 2014 (12.x) et versions ultérieures et Azure SQL Database.

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

C. Basculer des 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

D. Autoriser 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 est définie au TABLE niveau.

S’applique à : SQL Server et Azure SQL Database.

ALTER TABLE dbo.T1 SET (LOCK_ESCALATION = AUTO);
GO

E. Configurer le suivi des modifications sur une table

L'exemple suivant active le suivi des modifications sur la table Person.Person.

S’applique à : SQL Server et Azure SQL Database.

USE AdventureWorks2022;

ALTER TABLE Person.Person 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.

S’applique à : SQL Server.

USE AdventureWorks2022;
GO

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

L'exemple suivant désactive le suivi des modifications sur la table Person.Person.

S’applique à : SQL Server et Azure SQL Database.

USE AdventureWorks2022;
GO

ALTER TABLE Person.Person DISABLE CHANGE_TRACKING;

Désactiver et activer les contraintes et les déclencheurs

A. Désactiver et réactiver une contrainte

L'exemple suivant désactive la contrainte définissant les salaires pouvant être inclus dans les données. L'option NOCHECK CONSTRAINT est utilisée avec ALTER TABLE pour désactiver la contrainte et permettre une insertion qui entraîne généralement une violation de 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 fails.
ALTER TABLE dbo.cnst_example CHECK CONSTRAINT salary_cap;
INSERT INTO dbo.cnst_example VALUES (4, 'Eric James', 110000);

B. Désactiver et réactiver 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é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;
           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

Opérations en ligne

A. Reconstruction d'index en ligne à l'aide d'options d'attente à basse priorité

L'exemple suivant montre comment effectuer une reconstruction d'index en ligne qui spécifie les options d'attente à basse priorité.

S’applique à : SQL Server 2014 (12.x) et versions ultérieures et Azure SQL Database.

ALTER TABLE T1 REBUILD WITH (
    PAD_INDEX = ON,
    ONLINE = ON (
        WAIT_AT_LOW_PRIORITY (MAX_DURATION = 4 MINUTES, ABORT_AFTER_WAIT = BLOCKERS)
    )
);

B. Modification de colonne en ligne

L’exemple suivant montre comment exécuter une opération de modification de colonne avec l’option ONLINE .

S’applique à : SQL Server 2016 (13.x) et versions ultérieures, et Azure SQL Database.

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) WITH (ONLINE = ON);
GO

EXECUTE sp_help doc_exy;
DROP TABLE dbo.doc_exy;
GO

Contrôle de version du système

Les quatre exemples suivants vous aident à vous familiariser avec la syntaxe d’utilisation du contrôle de version système. Pour obtenir une assistance supplémentaire, consultez Prise en main des tables temporelles avec version système.

S’applique à : SQL Server 2016 (13.x) et versions ultérieures, et Azure SQL Database.

A. Ajouter la gestion système des versions à des tables existantes

L’exemple suivant montre comment ajouter la gestion système des versions à une table existante, et comment créer une table d’historique future. Cet exemple part du principe qu’il existe une table nommée InsurancePolicy avec une clé primaire définie. Cet exemple remplit les colonnes de période nouvellement créées pour la gestion système des versions à l’aide des valeurs par défaut pour les heures de début et de fin, car ces valeurs ne peuvent pas être Null. Cet exemple utilise la HIDDEN clause pour garantir l’absence d’effet sur les applications existantes qui interagissent avec la table actuelle. Elle utilise HISTORY_RETENTION_PERIOD également cette option uniquement sur SQL Database.

--Alter non-temporal table to define periods for system versioning
ALTER TABLE InsurancePolicy
    ADD ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN
            DEFAULT SYSUTCDATETIME() NOT NULL,
        ValidTo DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN
            DEFAULT CONVERT (DATETIME2, '9999-12-31 23:59:59.99999999') NOT NULL,
        PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);

--Enable system versioning with 1 year retention for historical data
ALTER TABLE InsurancePolicy SET (
    SYSTEM_VERSIONING = ON (
        HISTORY_RETENTION_PERIOD=1 YEAR
    )
);

B. Migrer une solution existante pour utiliser la gestion système des versions

L’exemple suivant montre comment migrer vers la gestion système des versions à partir d’une solution qui utilise des déclencheurs pour reproduire la prise en charge temporelle. L’exemple suppose qu’il existe une solution existante qui utilise une ProjectTask table et une ProjectTaskHistory table pour sa solution existante, qui utilise les colonnes et Changed Date les Revised Date colonnes pour ses périodes, que ces colonnes de période n’utilisent pas le type de données datetime2 et que la ProjectTask table a une clé primaire définie.

-- Drop existing trigger
DROP TRIGGER ProjectTask_HistoryTrigger;

-- Adjust the schema for current and history table
-- Change data types for existing period columns
ALTER TABLE ProjectTask ALTER COLUMN [Changed Date] DATETIME2 NOT NULL;
ALTER TABLE ProjectTask ALTER COLUMN [Revised Date] DATETIME2 NOT NULL;
ALTER TABLE ProjectTaskHistory ALTER COLUMN [Changed Date] DATETIME2 NOT NULL;
ALTER TABLE ProjectTaskHistory ALTER COLUMN [Revised Date] DATETIME2 NOT NULL;

-- Add SYSTEM_TIME period and set system versioning with linking two existing tables
-- (a certain set of data checks happen in the background)
ALTER TABLE ProjectTask
    ADD PERIOD FOR SYSTEM_TIME ([Changed Date], [Revised Date]);

ALTER TABLE ProjectTask SET (
    SYSTEM_VERSIONING = ON (
        HISTORY_TABLE=dbo.ProjectTaskHistory, DATA_CONSISTENCY_CHECK=ON
    )
);

C. Désactiver et réactiver le contrôle de version du système pour modifier le schéma de table

Cet exemple montre comment désactiver la gestion système des versions sur la table Department, ajouter une colonne et réactiver la gestion système des versions. La désactivation de la gestion système des versions est nécessaire pour modifier le schéma de table. Effectuez ces étapes dans une transaction pour empêcher les mises à jour des deux tables lors de la mise à jour du schéma de table, ce qui permet à l’administrateur de base de données d’ignorer la vérification de cohérence des données pendant la réactivation de la gestion système des versions et d’obtenir un gain de performances. Les tâches telles que la création de statistiques, le changement de partition ou la compression de l’une ou des deux tables ne nécessitent pas la désactivation de la gestion système des versions.

BEGIN TRAN
/* Takes schema lock on both tables */
ALTER TABLE Department
    SET (SYSTEM_VERSIONING = OFF) ;
/* expand table schema for temporal table */
ALTER TABLE Department
     ADD Col5 int NOT NULL DEFAULT 0 ;
/* Expand table schema for history table */
ALTER TABLE DepartmentHistory
    ADD Col5 int NOT NULL DEFAULT 0 ;
/* Re-establish versioning again*/
ALTER TABLE Department
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE=dbo.DepartmentHistory,
                                 DATA_CONSISTENCY_CHECK = OFF)) ;
COMMIT

D. Supprimer le contrôle de version du système

Cet exemple montre comment supprimer complètement la gestion système des versions de la table Department, et comment supprimer la table DepartmentHistory. Si vous le souhaitez, vous pouvez aussi supprimer les colonnes de période utilisées par le système pour enregistrer les informations de gestion système des versions. Vous ne pouvez pas supprimer les tables Department ou DepartmentHistory pendant que la gestion système des versions est activée.

ALTER TABLE Department
    SET (SYSTEM_VERSIONING = OFF);

ALTER TABLE Department
    DROP PERIOD FOR SYSTEM_TIME;

DROP TABLE DepartmentHistory;

Exemples : Azure Synapse Analytics et Analytics Platform System (PDW)

Les exemples A à C suivants utilisent la table FactResellerSales de la base de données AdventureWorksPDW2022.

A. Déterminer si une table est partitionnée

La requête suivante renvoie une ou plusieurs lignes si la table FactResellerSales est partitionnée. Si la table n'est pas partitionnée, aucune ligne n'est retournée.

SELECT *
FROM sys.partitions AS p
     INNER JOIN sys.tables AS t
         ON p.object_id = t.object_id
WHERE p.partition_id IS NOT NULL
      AND t.name = 'FactResellerSales';

B. Déterminer les valeurs limites pour une table partitionnée

La requête suivante renvoie les valeurs limites pour chaque partition de la table FactResellerSales .

SELECT t.name AS TableName,
       i.name AS IndexName,
       p.partition_number,
       p.partition_id,
       i.data_space_id,
       f.function_id,
       f.type_desc,
       r.boundary_id,
       r.value AS BoundaryValue
FROM sys.tables AS t
     INNER JOIN sys.indexes AS i
         ON t.object_id = i.object_id
     INNER JOIN sys.partitions AS p
         ON i.object_id = p.object_id
        AND i.index_id = p.index_id
     INNER JOIN sys.partition_schemes AS s
         ON i.data_space_id = s.data_space_id
     INNER JOIN sys.partition_functions AS f
         ON s.function_id = f.function_id
     LEFT OUTER JOIN sys.partition_range_values AS r
         ON f.function_id = r.function_id
        AND r.boundary_id = p.partition_number
WHERE t.name = 'FactResellerSales'
      AND i.type <= 1
ORDER BY p.partition_number;

C. Déterminer la colonne de partition pour une table partitionnée

La requête suivante retourne le nom de la colonne de partitionnement de la FactResellerSales table.

SELECT t.object_id AS Object_ID,
       t.name AS TableName,
       ic.column_id AS PartitioningColumnID,
       c.name AS PartitioningColumnName
FROM sys.tables AS t
     INNER JOIN sys.indexes AS i
         ON t.object_id = i.object_id
     INNER JOIN sys.columns AS c
         ON t.object_id = c.object_id
     INNER JOIN sys.partition_schemes AS ps
         ON ps.data_space_id = i.data_space_id
     INNER JOIN sys.index_columns AS ic
         ON ic.object_id = i.object_id
        AND ic.index_id = i.index_id
        AND ic.partition_ordinal > 0
WHERE t.name = 'FactResellerSales'
      AND i.type <= 1
      AND c.column_id = ic.column_id;

D. Fusionner deux partitions

L’exemple suivant fusionne deux partitions sur une table.

La table Customer a la définition suivante :

CREATE TABLE Customer
(
    id INT NOT NULL,
    lastName VARCHAR (20),
    orderCount INT,
    orderDate DATE
)
WITH (
    DISTRIBUTION = HASH(id),
    PARTITION(orderCount RANGE LEFT
        FOR VALUES (1, 5, 10, 25, 50, 100)
    )
);

La commande suivante combine les limites de 10 et 25 partitions.

ALTER TABLE Customer MERGE RANGE (10);

La nouvelle DDL pour la table est la suivante :

CREATE TABLE Customer
(
    id INT NOT NULL,
    lastName VARCHAR (20),
    orderCount INT,
    orderDate DATE
)
WITH (
    DISTRIBUTION = HASH(id),
    PARTITION(orderCount RANGE LEFT
        FOR VALUES (1, 5, 25, 50, 100)
    )
);

E. Fractionner une partition

L’exemple suivant fractionne une partition sur une table.

La table Customer a la DDL suivante :

DROP TABLE Customer;

CREATE TABLE Customer
(
    id INT NOT NULL,
    lastName VARCHAR (20),
    orderCount INT,
    orderDate DATE
)
WITH (
    DISTRIBUTION = HASH(id),
    PARTITION(orderCount RANGE LEFT
        FOR VALUES (1, 5, 10, 25, 50, 100)
    )
);

La commande suivante crée une partition liée par la valeur 75, entre 50 et 100.

ALTER TABLE Customer SPLIT RANGE (75);

La nouvelle DDL pour la table est la suivante :

CREATE TABLE Customer (
   id INT NOT NULL,
   lastName VARCHAR(20),
   orderCount INT,
   orderDate DATE)
   WITH DISTRIBUTION = HASH(id),
   PARTITION ( orderCount (RANGE LEFT
      FOR VALUES (1, 5, 10, 25, 50, 75, 100))) ;

F. Utiliser SWITCH pour déplacer une partition vers une table d’historique

L’exemple suivant déplace les données dans une partition de la table Orders vers une partition dans la table OrdersHistory.

La table Orders a la DDL suivante :

CREATE TABLE Orders
(
    id INT,
    city VARCHAR (25),
    lastUpdateDate DATE,
    orderDate DATE
)
WITH (
    DISTRIBUTION = HASH(id),
    PARTITION(orderDate RANGE RIGHT
        FOR VALUES ('2004-01-01', '2005-01-01', '2006-01-01', '2007-01-01')
    )
);

Dans cet exemple, la table Orders a les partitions suivantes. Chaque partition contient des données.

Partition	Les données sont-elles ?	Plage de limites
1	Yes	OrderDate < '2004-01-01'
2	Yes	'2004-01-01' <= OrderDate < '2005-01-01'
3	Yes	'2005-01-01' <= OrderDate< '2006-01-01'
4	Yes	'2006-01-01'<= OrderDate < '2007-01-01'
5	Yes	'2007-01-01' <= OrderDate

• Partition 1 (contient des données) : OrderDate < '2004-01-01'
• Partition 2 (contient des données) : '2004-01-01' <= OrderDate < '2005-01-01'
• Partition 3 (contient des données) : '2005-01-01' <= OrderDate< '2006-01-01'
• Partition 4 (contient des données) : '2006-01-01'<= OrderDate < '2007-01-01'
• Partition 5 (contient des données) : '2007-01-01' <= OrderDate

La table OrdersHistory a la DDL suivante, qui a des colonnes et des noms de colonnes identiques à ceux de la table Orders. Toute deux sont distribuées par hachage sur la colonne id.

CREATE TABLE OrdersHistory
(
    id INT,
    city VARCHAR (25),
    lastUpdateDate DATE,
    orderDate DATE
)
WITH (
    DISTRIBUTION = HASH(id),
    PARTITION(orderDate RANGE RIGHT
        FOR VALUES ('2004-01-01')
    )
);

Bien que les colonnes et les noms de colonnes doivent être identiques, les limites de partition ne doivent pas obligatoirement être les mêmes. Dans cet exemple, la table OrdersHistory a les deux partitions suivantes, toutes deux vides :

• Partition 1 (aucune donnée) : OrderDate < '2004-01-01'
• Partition 2 (vide) : '2004-01-01' <= OrderDate

Pour les deux tables précédentes, la commande suivante déplace toutes les lignes avec OrderDate < '2004-01-01' de la table Orders vers la table OrdersHistory.

ALTER TABLE Orders SWITCH PARTITION 1 TO OrdersHistory PARTITION 1;

En conséquence, la première partition dans Orders est vide et la première partition dans OrdersHistory contient des données. Les tables apparaissent maintenant comme suit :

Table Orders

• Partition 1 (vide) : OrderDate < '2004-01-01'
• Partition 2 (contient des données) : '2004-01-01' <= OrderDate < '2005-01-01'
• Partition 3 (contient des données) : '2005-01-01' <= OrderDate< '2006-01-01'
• Partition 4 (contient des données) : '2006-01-01'<= OrderDate < '2007-01-01'
• Partition 5 (contient des données) : '2007-01-01' <= OrderDate

Table OrdersHistory

• Partition 1 (contient des données) : OrderDate < '2004-01-01'
• Partition 2 (vide) : '2004-01-01' <= OrderDate

Pour nettoyer la Orders table, vous pouvez supprimer la partition vide en fusionnant des partitions 1 et 2 comme suit :

ALTER TABLE Orders MERGE RANGE ('2004-01-01');

Après la fusion, la table Orders a les partitions suivantes :

Table Orders

• Partition 1 (contient des données) : OrderDate < '2005-01-01'
• Partition 2 (contient des données) : '2005-01-01' <= OrderDate< '2006-01-01'
• Partition 3 (contient des données) : '2006-01-01'<= OrderDate < '2007-01-01'
• Partition 4 (contient des données) : '2007-01-01' <= OrderDate

Supposez qu’une autre année s’écoule et que vous êtes prêt à archiver l’année 2005. Vous pouvez allouer une partition vide pour l’année 2005 dans la table OrdersHistory en fractionnant la partition vide comme suit :

ALTER TABLE OrdersHistory SPLIT RANGE ('2005-01-01');

Après le fractionnement, la table OrdersHistory a les partitions suivantes :

Table OrdersHistory

• Partition 1 (contient des données) : OrderDate < '2004-01-01'
• Partition 2 (vide) : '2004-01-01' < '2005-01-01'
• Partition 3 (vide) : '2005-01-01' <= OrderDate

Contenu connexe

• sys.tables
• sp_rename
• sp_help
• EVENTDATA (Transact-SQL)
• CRÉER TABLE (Transact-SQL)
• TABLE DÉBARQUABLE (Transact-SQL)
• TABLE ALTER column_constraint (Transact-SQL)
• TABLE ALTER column_definition (Transact-SQL)
• TABLE ALTER computed_column_definition (Transact-SQL)
• TABLE ALTER index_option (Transact-SQL)
• TABLE ALTER table_constraint (Transact-SQL)