Modifier le schéma d’une table temporelle avec version système

  • Article

S’applique à : SQL Server 2016 (13.x) et versions ultérieures Azure SQL DatabaseAzure SQL Managed Instance

Utilisez l’instruction ALTER TABLE pour ajouter, modifier ou supprimer une colonne.

Exemples

Voici quelques exemples illustrant comment modifier le schéma d’une table temporelle.

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 ValidFrom ADD HIDDEN;

ALTER TABLE dbo.Department
    ALTER COLUMN ValidTo ADD HIDDEN;

Remarques importantes

  • L’autorisationCONTROL sur les tables actuelles et historiques est nécessaire pour modifier le schéma de la table temporelle.

  • Pendant une ALTER TABLE opération, le système détient un verrou de schéma sur les deux 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).

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

  • Si la taille de ligne après l’ajout de colonne dépasse la limite de taille de ligne, de nouvelles colonnes ne peuvent pas être ajoutées en ligne.

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

  • L’option en ligne (WITH (ONLINE = ON) n’a aucun effet sur ALTER TABLE ALTER COLUMN les tables temporelles avec version système. La colonne ALTER n’est pas exécutée en ligne, quelle que soit la valeur spécifiée pour l’option ONLINE.

  • Vous pouvez utiliser ALTER COLUMN pour modifier IsHidden la propriété des colonnes de période.

  • Vous ne pouvez pas utiliser direct ALTER pour les modifications de schéma suivantes. Pour ces types de modifications, définissez SYSTEM_VERSIONING = OFF.

    • Ajout d’une colonne calculée
    • Ajout d’une colonne IDENTITY
    • Ajout d’une colonne SPARSE ou modification de la colonne existante en SPARSE lorsque la table d’historique est définie sur DATA_COMPRESSION = PAGE ou DATA_COMPRESSION = ROW, qui est la valeur par défaut de la table d’historique.
    • Ajout d’un COLUMN_SET
    • Ajout d’une colonne ROWGUIDCOL ou modification d’une colonne en ROWGUIDCOL
    • Modification d’une colonne NULL à NOT NULL si la colonne contient des valeurs Null dans la table active ou historique

L’exemple suivant illustre la modification du schéma où la définition SYSTEM_VERSIONING = OFF est toujours requise (ajout d’une colonne IDENTITY ). Cet exemple désactive la vérification de la cohérence des données. 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.

BEGIN TRANSACTION
    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
        CONSTRAINT DF_Cntr DEFAULT 0;
    ALTER TABLE [dbo].[CompanyLocation] SET
    (
        SYSTEM_VERSIONING = ON
        (HISTORY_TABLE = [dbo].[CompanyLocationHistory])
    );
COMMIT;

Étapes suivantes