Use alter table . . . not materialized to create nonmaterialized columns:
alter table table_name add column_name datatype default constant_expression not null [not materialized]
See the Reference Manual: Commands.
You cannot use the not materialized with
the null parameter.
For example, to add the nonmaterialized column alt_title to
the titles table, with a default of aaaaa,
enter:
alter table titles add alt_title varchar(24) default 'aaaaa' not null not materialized
Adaptive Server creates a default for column column_name using the specified value (if one does not already exist), and inserts an entry in syscolumns for the new column, associating that default with the column.
Adaptive Server does not alter the table’s physical data.
You can combine alter table clauses that specify nonmaterialized columns with clauses creating other nonmaterialized columns or creating nullable columns. You cannot combine alter table to create nonmaterialized columns with clauses that perform a full data copy (such as alter table to drop a column or to add a non-nullable column).
When used in an alter table statement, constant_expression must be a constant (for example, 6), and cannot be an expression. constant_expression cannot be an expression such as “6+4”, a function (such as getdate), or the keyword “user” for a column specified in an alter table command.
Nonmaterialized columns require a default, and the default cannot be NULL. You can include the default by:
Explicitly
specifying it in the command (for example, int
default 0), or
Implicitly supplying it with a user-defined datatype that has a bound default
If you supply an invalid default value that cannot be
converted to that column’s correct datatype, alter
table raises an error
Use the column default cache size to configure the memory pool for column defaults.
