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?
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.
Adding a computed column
Adding an IDENTITY column
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.
Adding a COLUMN_SET
Adding a ROWGUIDCOL column or changing existing column to be ROWGUIDCOL