Modification du schéma d’une table temporelle à version contrôlée par le systèmeChanging the schema of a system-versioned temporal table

S’applique à :Applies to: ouiSQL Server 2016 (13.x)SQL Server 2016 (13.x)yesSQL Server 2016 (13.x)SQL Server 2016 (13.x) et ultérieures OuiAzure SQL DatabaseAzure SQL DatabaseYesAzure SQL DatabaseAzure SQL Database OuiAzure SQL Managed InstanceAzure SQL Managed InstanceYesAzure SQL Managed InstanceAzure SQL Managed InstanceS’applique à :Applies to: ouiSQL Server 2016 (13.x)SQL Server 2016 (13.x)yesSQL Server 2016 (13.x)SQL Server 2016 (13.x) and later OuiAzure SQL DatabaseAzure SQL DatabaseYesAzure SQL DatabaseAzure SQL Database OuiAzure SQL Managed InstanceAzure SQL Managed InstanceYesAzure SQL Managed InstanceAzure SQL Managed Instance

Utilisez l’instruction ALTER TABLE pour ajouter, modifier ou supprimer une colonne.Use the ALTER TABLE statement to add, alter or remove a column.

ExemplesExamples

Voici quelques exemples illustrant comment modifier le schéma d’une table temporelle.Here are some examples that change the schema of temporal table.

ALTER TABLE dbo.Department
    ALTER COLUMN DeptName varchar(100);

ALTER TABLE dbo.Department
    ADD WebAddress nvarchar(255) NOT NULL
    CONSTRAINT DF_WebAddress DEFAULT 'www.mycompany.com';

ALTER TABLE dbo.Department
    ADD TempColumn INT;

GO

ALTER TABLE dbo.Department
    DROP COLUMN TempColumn;

/* Setting IsHidden property for period columns.
Use ALTER COLUMN <period_column> DROP HIDDEN to clear IsHidden flag */

ALTER TABLE dbo.Department
    ALTER COLUMN SysStartTime ADD HIDDEN;

ALTER TABLE dbo.Department
    ALTER COLUMN SysEndTime ADD HIDDEN;

Remarques importantesImportant remarks

  • L’autorisationCONTROL sur les tables actuelles et historiques est nécessaire pour modifier le schéma de la table temporelle.CONTROL permission on current and history tables is required to change schema of temporal table.

  • Pendant une opération ALTER TABLE , le système verrouille le schéma des deux tables.During an ALTER TABLE operation, the system holds a schema lock on both tables.

  • La modification de schéma spécifiée est propagée à la table d’historique de manière appropriée (selon le type de modification).Specified schema change is propagated to history table appropriately (depending on type of change).

  • Si vous ajoutez une colonne n’acceptant la valeur Null ou modifiez une colonne de sorte qu’elle n’accepte pas la valeur Null, vous devez spécifier la valeur par défaut des lignes existantes.If you add a non-nullable column or alter existing column to become non-nullable, you must specify the default value for existing rows. Le système génère une valeur par défaut supplémentaire avec la même valeur et l’applique à la table historique.The system will generate an additional default with the same value and apply it to the history table. L’ajout de DEFAULT à une table non vide est une opération Taille des données dans toutes les éditions sauf dans SQL ServerSQL Server Enterprise Edition (pour laquelle c’est une opération de métadonnées).Adding DEFAULT to a non-empty table is a size of data operation on all editions other than SQL ServerSQL Server Enterprise Edition (on which it is a metadata operation).

  • L’ajout de varchar(max), nvarchar(max), varbinary(max) ou de colonnes XML avec des valeurs par défaut est une opération de mise à jour des données dans toutes les éditions de SQL ServerSQL Server.Adding varchar(max), nvarchar(max), varbinary(max) or XML columns with defaults will be an update data operation on all editions of SQL ServerSQL Server.

  • Si la taille de ligne après l’ajout de colonne dépasse la limite, les nouvelles colonnes ne peuvent pas être ajoutées en ligne.If row size after column addition exceeds the row size limit, new columns cannot be added online.

  • Lorsque vous ajoutez à une table une colonne NOT NULL, envisagez de supprimer la contrainte par défaut sur la table historique, car toutes les colonnes de cette table sont automatiquement renseignées par le système.Once you extend a table with a new NOT NULL column, consider dropping default constraint on the history table as all columns in that table are automatically populated by the system.

  • L’option Online (WITH (ONLINE = ON) n’a aucun effet sur ALTER TABLE ALTER COLUMN si la version de table temporelle est contrôlée par le système.Online option (WITH (ONLINE = ON) has no effect on ALTER TABLE ALTER COLUMN in case of system-versioned temporal table. L’opération ALTER COLUMN n’est pas effectuée en ligne, quelle que soit la valeur spécifiée pour l’option ONLINE.ALTER column is not performed as online regardless of which value was specified for ONLINE option.

  • Vous pouvez utiliser ALTER COLUMN pour modifier la propriété IsHidden pour les colonnes de période.You can use ALTER COLUMN to change IsHidden property for period columns.

  • Vous ne pouvez pas utiliser directement ALTER pour les modifications de schéma suivantes.You cannot use direct ALTER for the following schema changes. Pour ces types de modifications, définissez SYSTEM_VERSIONING = OFF.For these types of changes, set SYSTEM_VERSIONING = OFF.

    • Ajout d’une colonne calculéeAdding a computed column
    • Ajout d’une colonne IDENTITYAdding an IDENTITY column
    • Ajout d’une colonne SPARSE ou modification d’une colonne en SPARSElorsque la table historique est configurée avec DATA_COMPRESSION = PAGE ou DATA_COMPRESSION = ROW, qui est la valeur par défaut pour la table historique.Adding a SPARSE column or changing existing column to be SPARSEwhen the history table is set to DATA_COMPRESSION = PAGE or DATA_COMPRESSION = ROW, which is the default for the history table.
    • Ajout d’un COLUMN_SETAdding a COLUMN_SET
    • Ajout d’une colonne ROWGUIDCOL ou modification d’une colonne en ROWGUIDCOLAdding a ROWGUIDCOL column or changing existing column to be ROWGUIDCOL

L’exemple suivant illustre la modification du schéma où le paramètre SYSTEM_VERSIONING = OFF est toujours requis (ajout de la colonne IDENTITY ).The following example demonstrates changing the schema where setting SYSTEM_VERSIONING = OFF is still required (adding IDENTITY column). Cet exemple désactive la vérification de la cohérence des données.This example disables the data consistency check. Cette vérification n’est pas nécessaire lorsque la modification du schéma s’effectue dans une transaction, car aucune modification simultanée de données n’est possible.This check is unnecessary when the schema change is made within a transaction as no concurrent data changes can occur.

    BEGIN TRAN
        ALTER TABLE [dbo].[CompanyLocation] SET (SYSTEM_VERSIONING = OFF);
        ALTER TABLE [CompanyLocation] ADD Cntr INT IDENTITY (1,1);
        ALTER TABLE [dbo].[CompanyLocationHistory] ADD Cntr INT NOT NULL DEFAULT 0;
        ALTER TABLE [dbo].[CompanyLocation]
    SET
         (
            SYSTEM_VERSIONING = ON
           ( HISTORY_TABLE = [dbo].[CompanyLocationHistory])
         );
    COMMIT ;

Étapes suivantesNext steps