How do you modify a temporal table in SQL Server 2016 or Azure?

Question

I've created some temporal tables in SQL Azure but I can't figure out how to modify them. What is the best approach to adding new columns or modifying existing ones?

Answer 1

MSDN has a lot of examples,In earlier versions of CTP,you cant alter Temporal table with out setting system_versioning to off.But starting with CTP3,you can do this...Here are few examples..

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

ALTER TABLE dbo.Department 
   ALTER COLUMN SysEndTime ADD HIDDEN;

MSDN also recommends to do any scheme change in a transaction like below..

BEGIN TRAN 
---set system versioning to off
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; 
--specifying history table is needed with out which ,SQL adds a new history table
ALTER TABLE [dbo].[CompanyLocation]  
SET  
( 
SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[CompanyLocationHistory]) 
); 
COMMIT ;

You may wonder why we need to set system versioning off ,as i said above it can be done with out it starting with ctp3.0,this is because of few limitations..

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

1. Adding a computed column

2. Adding an IDENTITY column

3. Adding a SPARSE column or changing existing column to be SPARSEwhen the history table is set to DATA_COMPRESSION = PAGE or DATA_COMPRESSION = ROW, which is the default for the history table.

4. Adding a COLUMN_SET

5. Adding a ROWGUIDCOL column or changing existing column to be ROWGUIDCOL