You can use the ALTER TABLE statement to perform schema changes on system-versioned temporal tables. When you use it to add a new data type, change a data type, or remove an existing column, the system will automatically perform the action against both the current and the history table. However, some of these actions will not be metadata operations only; there are changes that will update the entire table. To check this, run the following code to create (and populate with sample data) the temporal table from the previous section:

USE tempdb; 
CREATE TABLE dbo.Product 
( 
   ProductId INT NOT NULL CONSTRAINT PK_Product PRIMARY KEY, 
   ProductName NVARCHAR(50) NOT NULL, 
   Price MONEY NOT NULL, 
   ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL, 
   ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL, 
   PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo) 
) 
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ProductHistory)); 
GO
INSERT INTO dbo.Product (ProductId,ProductName,Price)
SELECT message_id,'PROD' + CAST(message_id AS NVARCHAR), severity FROM sys.messages WHERE language_id = 1033;

Now, you will add three new columns into the temporal table:

• A column named Color, which allows NULL values
• A column named Category, where a non-nullable value is mandatory
• A Large Object (LOB) column named Description

But before you add them, you will create an Extended Events (XE) session to trace what happens under the hood when you add a new column to a temporal table. Use the following code to create and start the XE session:

CREATE EVENT SESSION AlteringTemporalTable ON SERVER 
ADD EVENT sqlserver.sp_statement_starting(
    WHERE (sqlserver.database_id = 2)),
ADD EVENT sqlserver.sp_statement_completed(
    WHERE (sqlserver.database_id = 2)),
ADD EVENT sqlserver.sql_statement_starting(
    WHERE (sqlserver.database_id = 2)),
ADD EVENT sqlserver.sql_statement_completed(
    WHERE (sqlserver.database_id = 2))
ADD TARGET package0.event_file (SET filename = N'AlteringTemporalTable') WITH (MAX_DISPATCH_LATENCY = 1 SECONDS)
GO
ALTER EVENT SESSION AlteringTemporalTable ON SERVER STATE = start; 
GO

Now you can issue the following three ALTER statements that correspond to the previously described new columns:

ALTER TABLE dbo.Product ADD Color NVARCHAR(15); 
ALTER TABLE dbo.Product ADD Category SMALLINT NOT NULL CONSTRAINT DF_Category DEFAULT 1; 
ALTER TABLE dbo.Product ADD Description NVARCHAR(MAX) NOT NULL CONSTRAINT DF_Description DEFAULT N'N/A'; 

The content collected by the XE session is shown as follows:

It clearly shows that the first two ALTER statements have been executed without additional actions. However, adding a LOB column triggers the updating of all rows with the default value as an offline operation.

The first statement adds a column that accepts null values, so the action is done instantly.

When you add a non-nullable column, the situation is different, as illustrated in the second statement. First, you need to provide a default constraint to ensure that all rows will have a value in this column. This action will be online (metadata operation) in the Enterprise and Developer editions only. In all the other editions, all rows in both the current and the history table will be updated to add additional columns with their values.

However, adding LOB or BLOB columns will cause a mass update in both the current and the history table in all SQL Server editions! This action will internally update all rows in both tables. For large tables, this can take a long time and during this time, both tables are locked.

You can also use the ALTER TABLE statement to add the HIDDEN attribute to period columns or to remove it. This code line adds the HIDDEN attribute to the columns ValidFrom and ValidTo:

ALTER TABLE dbo.Product ALTER COLUMN ValidFrom ADD HIDDEN; 
ALTER TABLE dbo.Product ALTER COLUMN ValidTo ADD HIDDEN;

Clearly, you can also remove the HIDDEN attribute:

ALTER TABLE dbo.Product ALTER COLUMN ValidFrom DROP HIDDEN; 
ALTER TABLE dbo.Product ALTER COLUMN ValidTo DROP HIDDEN; 

However, there are some changes that are not allowed for temporal tables:

• Adding an IDENDITY or computed column
• Adding a ROWGUIDCOL column or changing an existing column to it
• Adding a SPARSE column or changing an existing column to it, when the history table is compressed

When you try to add a SPARSE column, you will get an error, as in the following example:

ALTER TABLE dbo.Product ADD Size NVARCHAR(5) SPARSE;

The command ends up with the following error message:

Msg 11418, Level 16, State 2, Line 20
Cannot alter table 'ProductHistory' because the table either contains sparse columns or a column set column which are incompatible with compression. 

The same happens when you try to add an IDENTITY column, as follows:

ALTER TABLE dbo.Product ADD ProductNumber INT IDENTITY (1,1); 

And here is the error message:

Msg 13704, Level 16, State 1, Line 26
System-versioned table schema modification failed because history table 'WideWorldImporters.dbo.ProductHistory' has IDENTITY column specification. Consider dropping all IDENTITY column specifications and trying again.

If you need to perform schema changes to a temporal table not supported in the ALTER statement, you have to set its SYSTEM_VERSIONING attribute to false to convert the tables to non-temporal tables, perform the changes, and then convert back to a temporal table. The following code demonstrates how to add the identity column ProductNumber and the sparse column Size into the temporal table dbo.Product:

ALTER TABLE dbo.ProductHistory REBUILD PARTITION = ALL WITH (DATA_COMPRESSION=NONE);
GO 
BEGIN TRAN
ALTER TABLE dbo.Product SET (SYSTEM_VERSIONING = OFF); 
ALTER TABLE dbo.Product ADD Size NVARCHAR(5) SPARSE;
ALTER TABLE dbo.ProductHistory ADD Size NVARCHAR(5) SPARSE;
ALTER TABLE dbo.Product ADD ProductNumber INT IDENTITY (1,1); 
ALTER TABLE dbo.ProductHistory ADD ProductNumber INT NOT NULL DEFAULT 0; 
ALTER TABLE dbo.Product SET(SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo. ProductHistory));
COMMIT;

To perform ALTER TABLE operations, you need to have CONTROL permission on the current and history tables. During the changes, both tables are locked with schema locks.