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.

Make no mistake! i dont have any error on alter table. it happens after edit or add column and when i want to return system_versioning to a previous state (set it to 'ON') this error occurs. — Aiyoub A., Nov 01 '16 at 10:07
I did this by deleting fields _[SysStartTime]_ and _[SysEndTime]_. So It seems not right. — Aiyoub A., Nov 06 '16 at 06:43
Changes in the type of tables does not need for disable/enable system_versioning. — Aiyoub A., Nov 08 '16 at 12:10

TheGameiswar · Answer 1 · 2016-11-01T08:41:46.637

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

Make no mistake! i dont have any error on alter table. it happens after edit or add column and when i want to return system_versioning to a previous state (set it to 'ON') this error occurs. thank you — Aiyoub A., Nov 01 '16 at 10:54
Note that adding columns after you turn on temporal tables can cause sp_rename to fail. Great tip on doing this in a transaction! I have been using temporal tables for awhile and the only complaint I have is system periods have concurrent update issues, which limit throughput when batch updating records. — John Zabroski, Jun 24 '20 at 15:20

score 2 · Accepted Answer · answered May 03 '17 at 04:05

2

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

answered May 03 '17 at 04:05

Aiyoub A.

5,261
8
25
38

1

The history table tracks with the main table, so if you change the data type on the main table, the history table automatically changes to match. You don't have to turn System Versioning off and back on. You still do, however, have to deal with PK, FK and other constraints. – Mike Nicewarner Dec 01 '20 at 15:49
Although you may need to turn versioning off & on again when making some changes, e.g. if you make a nullable column non-nullable and there's null values in the data then you need to turn off versioning, update the historical data, turn on versioning again – Rory Apr 21 '22 at 13:58

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

2 Answers2