Changing the schema of a system-versioned temporal table

Applies to: yesSQL Server 2016 (13.x) and later YesAzure SQL Database YesAzure SQL Managed Instance

Use the ALTER TABLE statement to add, alter or remove a column.


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

ALTER TABLE dbo.Department
    ADD TempColumn INT;


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 TABLE dbo.Department

Important remarks

  • CONTROL permission on current and history tables is required to change schema of temporal table.

  • During an ALTER TABLE operation, the system holds a schema lock on both tables.

  • Specified schema change is propagated to history table appropriately (depending on type of change).

  • Adding varchar(max), nvarchar(max), varbinary(max) or XML columns with defaults will be an update data operation on all editions of SQL Server.

  • If row size after column addition exceeds the row size limit, new columns cannot be added online.

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

  • Online option (WITH (ONLINE = ON) has no effect on ALTER TABLE ALTER COLUMN in case of system-versioned temporal table. ALTER column is not performed as online regardless of which value was specified for ONLINE option.

  • You can use ALTER COLUMN to change IsHidden property for period columns.

  • You cannot use direct ALTER for the following schema changes. For these types of changes, set SYSTEM_VERSIONING = OFF.

    • Adding a computed column
    • Adding an IDENTITY column
    • Adding a SPARSE column or changing existing column to be SPARSE when the history table is set to DATA_COMPRESSION = PAGE or DATA_COMPRESSION = ROW, which is the default for the history table.
    • Adding a COLUMN_SET
    • Adding a ROWGUIDCOL column or changing existing column to be ROWGUIDCOL
    • Altering a NULL column to NOT NULL if the column contains null values in the current or history table

The following example demonstrates changing the schema where setting SYSTEM_VERSIONING = OFF is still required (adding IDENTITY column). This example disables the data consistency check. This check is unnecessary when the schema change is made within a transaction as no concurrent data changes can occur.

        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]
           ( HISTORY_TABLE = [dbo].[CompanyLocationHistory])

Next steps