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

While this is not a direct answer to this old question, I've been met with a problem talked about by Kelps Sousa Alux, where I needed to have a table column not be 'generated always' in the start so that I could explicitly insert the ValidFrom/To values, but after a series of other operations those columns needed to be end up as 'generated always'.

While not directly, it is possible to do so

First create a table and the history table separately, insert the data you need, then add the PERIOD FOR SYSTEM_TIME, set the columns as hidden if you wish to, then set the system versioning on.

CREATE TABLE Test (
    ID IDENTITY(1,1) PRIMARY KEY NOT NULL,
    Value VARCHAR(10),
    ValidFrom DATETIME2 DEFAULT(GETUTCDATE()) NOT NULL,
    ValidTo DATETIME2 DEFAULT(CONVERT[DATETIME2], '9999-12-31 23:59:59.999') NOT NULL
);

CREATE TABLE TestHistory (
    ID IDENTITY(1,1) PRIMARY KEY NOT NULL,
    Value VARCHAR(10),
    ValidFrom DATETIME2 NOT NULL,
    ValidTo DATETIME2 NOT NULL
);
GO

-- At this point, you can insert data explicitly for all columns

ALTER TABLE Test ADD PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
GO

ALTER TABLE Test ALTER COLUMN ValidFrom ADD HIDDEN;
ALTER TABLE Test ALTER COLUMN ValidTo ADD HIDDEN;
GO

ALTER TABLE Test SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.TestHistory))

If you ever need to disable the temporal table, insert data explicitly to all columns, and then reattach the history table to the temporal one, you can also do that - like so:

ALTER TABLE Test SET (SYSTEM_VERSIONING = OFF);
GO

ALTER TABLE Test DROP PERIOD FOR SYSTEM_TIME;
GO

-- At this point, you can insert data explicitly into all columns
GO

ALTER TABLE Test ADD PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
ALTER TABLE Test SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.TestHistory))
GO

ALTER TABLE Test ALTER COLUMN ValidFrom ADD HIDDEN;
ALTER TABLE Test ALTER COLUMN ValidTo ADD HIDDEN;
GO

Remember about identity insert, if you need it. If after this you check the generated_always_type and generated_always_type_desc columns in sys.all_columns, you will see that they are set appropriately to AS_ROW_START/END

Answer 2

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://learn.microsoft.com/en-us/sql/relational-databases/tables/querying-data-in-a-system-versioned-temporal-table?view=sql-server-ver15