Change the schema of a system-versioned temporal table
Applies to: SQL Server 2016 (13.x) and later Azure SQL Database Azure SQL Managed Instance
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 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;
CONTROL permission on current and history tables is required to change schema of temporal table.
ALTER TABLEoperation, 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 can't 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.
The online option (
WITH (ONLINE = ON) has no effect on
ALTER TABLE ALTER COLUMNwith system-versioned temporal tables. ALTER column isn't performed as online, regardless of which value was specified for ONLINE option.
You can use
ALTER COLUMNto change
IsHiddenproperty for period columns.
You can't use direct
ALTERfor 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 = PAGEor
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 an 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.
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;
- Temporal Tables
- Getting Started with System-Versioned Temporal Tables
- Manage Retention of Historical Data in System-Versioned Temporal Tables
- System-Versioned Temporal Tables with Memory-Optimized Tables
- ALTER TABLE (Transact-SQL)
- Creating a System-Versioned Temporal Table
- Modifying Data in a System-Versioned Temporal Table
- Querying Data in a System-Versioned Temporal Table
- Stopping System-Versioning on a System-Versioned Temporal Table
Submit and view feedback for