Create Nonmaterialized, Non-Null Columns

Nonmaterialized columns exist virtually, but are not physically stored in the row. Use nonmaterialized columns the same as any other column, selecting, updating, and referring to them in SQL queries, or using them as index keys.

SAP ASE treats nonmaterialized columns the same way it treats null columns: if a column is not physically present in the row, SAP ASE supplies a default. The default for a nullable column is null, but the default for a nonmaterialized column is a user-defined non-NULL value.

Converting a nonmaterialized column to a physically present column is called “instantiating” the column.

• Add Nonmaterialized Columns
  Use alter table . . . not materialized to create nonmaterialized columns.
• Tables That Already Have Nonmaterialized Columns
  SAP ASE immediately instantiates all nonmaterialized columns if you specify a command that includes clauses that require a full data copy to the table. This includes commands such as reorg rebuild and alter table to add a non-nullable column.
• Nonmaterialized Column Storage
  Once nonmaterialized columns are instantiated, SAP ASE stores them in the same way as any other row in the table.
• Alter Nonmaterialized Columns
  Use alter table .... replace to changed the defaults of nonmaterialized columns. However, this command does not change the defaults for columns using the previous default. Instead, alter table .... replace changes the defaults for columns that you add or update.
• Limitations for Nonmaterialized Columns
  alter table … modify does not support converting existing materialized columns to nonmaterialized columns. Running alter table ... modify against a column instantiates all nonmaterialized columns in the table.