The best mechanism for alter columns of system versioning tables (Temporal Table)?

Question

I have a system-versioning table with history table related as follows:

CREATE TABLE [dbo].[ExpenseCenter_Archive](
    [ExpenseCenterId] [tinyint] NOT NULL,
    [Name] [nvarchar](200) NOT NULL,
    [LineCode] [smallint] NOT NULL,
    [SysStartTime] [datetime2](2) NOT NULL,
    [SysEndTime] [datetime2](2) NOT NULL
) ON [FG_HISTORY]
GO
-------
CREATE TABLE [dbo].[ExpenseCenter](
    [ExpenseCenterId] [tinyint] NOT NULL,
    [Name] [nvarchar](200) NOT NULL,
    [LineCode] [smallint] NOT NULL,
    [SysStartTime] [datetime2](2) GENERATED ALWAYS AS ROW START NOT NULL,
    [SysEndTime] [datetime2](2) GENERATED ALWAYS AS ROW END NOT NULL,
 CONSTRAINT [PK_ExpenseCenter] PRIMARY KEY CLUSTERED 
(
    [ExpenseCenterId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [FG_DATA],
CONSTRAINT [UK_ExpenseCenterName] UNIQUE NONCLUSTERED 
(
    [Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [FG_INDEX],
    PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime])
) ON [FG_DATA]
WITH
(
SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[ExpenseCenter_Archive] , DATA_CONSISTENCY_CHECK = ON )
)
GO

Now, I want alter data type of 'LineCode' in system-version table and history. After changes once again enabling it as follows:

--- Before edit column
ALTER TABLE [dbo].[ExpenseCenter] SET (SYSTEM_VERSIONING = OFF);  
-- ## Edit column in ssms ##

--- After edit column
ALTER TABLE [dbo].[ExpenseCenter] 
SET    
(   
SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[ExpenseCenter_Archive])   
);   

But I get the following error:

Cannot set SYSTEM_VERSIONING to ON when SYSTEM_TIME period is not defined.

How do I solve this issue.

Solution 1

for alter system versioning table you don't need set SYSTEM_VERSIONING = OFF, but directly use ALTER TABLE ...

Solution 2

From your question ,you are saying that ExpenseCenter_archive is the temporal table for ExpenseCenter..but error message says

you don't have system versioned table [dbo].[ExpenseCenter] ,if you want system versioned table ,Add system_time to it

so here are the steps,i would follow to make a table Temporal table of other..

if its for a new table ..

CREATE TABLE Department   
(    
     DeptID int NOT NULL PRIMARY KEY CLUSTERED  
   , DeptName varchar(50) NOT NULL  
   , ManagerID INT  NULL  
   , ParentDeptID int NULL  
   , SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL  
   , SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL  
   , PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)     
)    
WITH (SYSTEM_VERSIONING = ON)   
;  

if i need to alter data type for this newly created table..

MSDN recommends doing it in a transaction..

BEGIN TRAN   
ALTER TABLE [dbo].[CompanyLocation] SET (SYSTEM_VERSIONING = OFF);  

ALTER TABLE [CompanyLocation] ADD Cntr INT IDENTITY (1,1);   

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

If i want to make an existing table Temporal,then i would do like below

ALTER TABLE dbo.Product
ADD StartTime DATETIME2 GENERATED ALWAYS AS ROW START
  HIDDEN DEFAULT GETUTCDATE(),
 EndTime  DATETIME2 GENERATED ALWAYS AS ROW END
  HIDDEN DEFAULT
     CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'),
 PERIOD FOR SYSTEM_TIME (StartTime, EndTime)

Now finally set Temporal ON

ALTER TABLE dbo.Product
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE=dbo.ProductHistory))
GO

References:
http://sqlhints.com/tag/modify-existing-table-as-system-versioned-temporal-table/
https://msdn.microsoft.com/en-us/library/mt590957.aspx