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.

`GENERATED BY DEFAULT` is not legal T-SQL syntax anywhere, and whether or not a column is `GENERATED ALWAYS` can't be changed after the fact, just like `IDENTITY`. As the column is used to produce a history for a temporal table, it's not even clear what you're trying to do. Are you trying to remove the temporal table aspect? — Jeroen Mostert, Sep 12 '17 at 11:45
@JeroenMostert - No, I need to seed some data with old dates. — , Sep 12 '17 at 11:49
Let me know this is a supportive question of my question https://stackoverflow.com/questions/46174372/seed-data-with-old-dates-in-temporal-table-sql-server Am I right ? — B.Balamanigandan, Sep 12 '17 at 11:52
@B.Balamanigandan - Its a child question of your question https://stackoverflow.com/questions/46174372/seed-data-with-old-dates-in-temporal-table-sql-server — , Sep 12 '17 at 11:53
@Mastero: then you're out of luck, since system-versioned temporal tables apparently don't support that. You could, of course, always change the system time on the server itself. Be careful if you go that route, because things like domain authentication stop working if you mess with the time too much. Also, it can apparently take a while before SQL Server catches up with the altered time. Consider creating the database in a VM or other isolated instance in this case. Create the table at the oldest time and always move the clock forward, never backwards. — Jeroen Mostert, Sep 12 '17 at 12:13

score 1 · Answer 1 · answered Apr 06 '22 at 21:24

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:

Created both tables (the primary and it's history)
Loaded the primary table with it's data
Updated all records in the primary table without changing any data. This causes an equivalent record to be created in the history table
Disabled system versioning in the table with ALTER TABLE Contact SET (SYSTEM_VERSIONING = OFF);
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)
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://learn.microsoft.com/en-us/sql/relational-databases/tables/querying-data-in-a-system-versioned-temporal-table?view=sql-server-ver15

"There is no way to set the value in the period fields in the main table" - I'm somewhat skeptical of this. Is it not possible to `ALTER` these columns to turn off `GENERATED ALWAYS` and then `ALTER` once finished? — Siddhartha Gandhi, Jan 30 '23 at 15:12

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

1 Answers1