Alter GENERATED ALWAYS colum into a GENERATED BY DEFAULT identity column - Sql Server

Question

I need to change the Existing Table Column, which is configured as GENERATED ALWAYS into a GENERATED BY DEFAULT.

Sample Table Structure

CREATE TABLE [dbo].[Contact](
    [ContactID] [uniqueidentifier] NOT NULL,
    [ContactNumber] [nvarchar](50) NOT NULL,
    [SequenceID] [int] IDENTITY(1,1) NOT NULL,
    [SysStartTime] [datetime2](0) GENERATED ALWAYS AS ROW START NOT NULL,
    [SysEndTime] [datetime2](0) GENERATED ALWAYS AS ROW END NOT NULL,
 CONSTRAINT [PK_Contact] PRIMARY KEY NONCLUSTERED 
(
    [ContactID] ASC
)WITH (PAD_INDEX = OFF, 
       STATISTICS_NORECOMPUTE = OFF, 
       IGNORE_DUP_KEY = OFF, 
       ALLOW_ROW_LOCKS = ON, 
       ALLOW_PAGE_LOCKS = ON) 
   ON [PRIMARY],
PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime])
) ON [PRIMARY]
WITH
(
    SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[ContactHistory],
                            DATA_CONSISTENCY_CHECK = ON )
)

This is the table I'm already having now I need to change the column [SysStartTime] to GENERATED BY DEFAULT from GENERATED ALWAYS

I tried the following code

ALTER TABLE dbo.Contact ALTER column SysStartTime SET GENERATED BY DEFAULT

But it throws an error

Msg 156, Level 15, State 1, Line 19 Incorrect syntax near the keyword 'SET'.

Kindly assist me.

Answer 1

I know this is an old question, but I stumbled on a similar problem and I found a way to achieve an end result equivalent to the one desired here (have the records tell the correct story of when they were created and also properly work with temporal queries).

What I did was:

1. Created both tables (the primary and it's history)
2. Loaded the primary table with it's data
3. Updated all records in the primary table without changing any data. This causes an equivalent record to be created in the history table
4. Disabled system versioning in the table with ALTER TABLE Contact SET (SYSTEM_VERSIONING = OFF);
5. Updated the ValidFrom field in the history table to properly reflect the date the records where actually originally inserted. (this can only be done if the system versioning for the table is disabled)
6. Re enabled the system versioning in the table using ALTER TABLE Contact SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[ContactHistory], DATA_CONSISTENCY_CHECK = ON ))

This causes the records to be identical (except for the ValidFrom/To fields), but allow for any query using FOR SYSTEM_TIME AS OF 'YYYY-MM-DD THH:mm:SS.0000000'; to work properly and represent the data exactly as it was in the moment requested.

Things I found out when trying this:

• You can't change an existing field to be GENERATED ALWAYS AS ROW START/END. It needs to be a field set that way from the beginning.
• You can't use DATETIMEOFFSET fields for the period fields.
• There is no way to set the value in the period fields in the main table
• There is no way to manipulate the data in the history table if versioning is enabled

Additional documentation: https://docs.microsoft.com/en-us/sql/relational-databases/tables/querying-data-in-a-system-versioned-temporal-table?view=sql-server-ver15