Nonmaterialized Column Storage

Once nonmaterialized columns are instantiated, SAP ASE stores them in the same way as any other row in the table.

When SAP ASE instantiates a fixed-length, nonmaterialized column, the row occupies more space than an equivalent fixed-length column that is materialized, and instantiated nonmaterialized columns require more space than equivalent fixed-length columns. For dataonly-locked (DOL) tables, the column overhead is 2 bytes. For allpages- locked tables, the column overhead is 1 or more bytes, depending on the column’s length and its physical placement in the row.

Columns are nonmaterialized only in rows that were present in the table when you altered the table to include nonmaterialized columns. As you update rows, SAP ASE instantiates any columns that are nonmaterialized.

Nonmaterialized columns can be followed by null columns or other nonmaterialized columns, but cannot be followed by materialized data. When you instantiate a column within a row, SAP ASE instantiates nonmaterialized columns that appear before that row. Nonmaterialized columns are also instantiated in new rows added to the table.

When you instantiate a nonmaterialized column, the row becomes larger. When this happens in DOL tables, SAP ASE forwards the row if the containing page does not have enough reserved space to accommodate the expanded row (this is equivalent to replacing a null column with a non-NULL value).