Add Nonmaterialized Columns

Use alter table . . . not materialized to create nonmaterialized columns.

For example:

alter table table_name
add column_name datatype default constant_expression 
not null [not materialized]

See the Reference Manual: Commands.

Note: 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

SAP ASE 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.

SAP ASE 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

Note: 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.