Edit

Change the schema of a system-versioned temporal table

Applies to: SQL Server 2016 (13.x) and later versions Azure SQL Database Azure SQL Managed Instance SQL database in Microsoft Fabric

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

Remarks

You need CONTROL permission on the current and history tables to change the schema of a temporal table.

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

The Database Engine propagates the schema change to the history table, depending on the type of change.

Adding varchar(max), nvarchar(max), varbinary(max), or xml columns with defaults is an update data operation on all editions of SQL Server.

If the row size after adding a column exceeds the row size limit, you can't add new columns online.

Once you extend a table with a new NOT NULL column, consider dropping the default constraint on the history table, as the system automatically populates all columns in that table.

The online option (WITH (ONLINE = ON) has no effect on ALTER TABLE ALTER COLUMN with temporal tables. The ALTER column operation doesn't run online, regardless of the value you specify for the ONLINE option.

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

You can't 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 an 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 an existing column to be ROWGUIDCOL
  • Altering a NULL column to NOT NULL if the column contains null values in the current or history table

Examples

A. Change the schema of a temporal table

Here are some examples that change the schema of a temporal table.

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

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

ALTER TABLE dbo.Department
    ADD TempColumn INT;
GO

ALTER TABLE dbo.Department
    DROP COLUMN TempColumn;

B. Add period columns using the HIDDEN flag

ALTER TABLE dbo.Department
    ALTER COLUMN ValidFrom ADD HIDDEN;

ALTER TABLE dbo.Department
    ALTER COLUMN ValidTo ADD HIDDEN;

You can use ALTER COLUMN <period_column> DROP HIDDEN to clear the hidden flag on a period column.

C. Change the schema with SYSTEM_VERSIONING set to OFF

The following example shows how to change the schema when you still need to set SYSTEM_VERSIONING = OFF (adding an IDENTITY column). This example disables the data consistency check. This check is unnecessary when you make the schema change within a transaction, because no concurrent data changes can occur.

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
        CONSTRAINT DF_Cntr DEFAULT 0 NOT NULL;

ALTER TABLE [dbo].[CompanyLocation]
SET (
    SYSTEM_VERSIONING = ON
    (HISTORY_TABLE = [dbo].[CompanyLocationHistory])
);

COMMIT TRANSACTION;

Related content

  • Last updated on