Modifying the generated or identity property of a column

You can add and drop the generated or identity property of a column in a table using the ALTER COLUMN clause in the ALTER TABLE statement.

You can do one of the following actions:

• When working with an existing non-generated column, you can add a generated expression attribute. The modified column then becomes a generated column.
• When working with an existing generated column, you can drop a generated expression attribute. The modified column then becomes a normal, non-generated column.
• When working with an existing non-identity column, you can add a identity attribute. The modified column then becomes an identity column.
• When working with an existing identity column, you can drop the identity attribute. The modified column then becomes a normal, non-generated, non-identity column.
• When working with an existing identity column, you can alter the column from being GENERATED ALWAYS to GENERATED BY DEFAULT. The reverse is also true; that is, you can alter the column from being GENERATED BY DEFAULT to GENERATED ALWAYS. This is only possible when working with an identity column.
• You can drop the default attribute from the user-defined default column. When you do this, the new default value is null.
• You can drop the default, identity, or generation attribute and then set a new default, identity, or generation attribute in the same ALTER COLUMN statement.
• For both the CREATE TABLE and ALTER TABLE statements, the ALWAYS keyword is optional in the generated column clause. This means that GENERATED ALWAYS is equivalent to GENERATED.