Creating Compressed Tables

Considerations for creating compressed tables.

Unless you state otherwise, the SAP ASE server:
- Sets data compression to NULL when you create a table.
- Preserves the existing compression level when you modify a table.
- Sets all partitions to the compression level specified in the create table clause.
You can create a table with table-level compression but leave some partitions uncompressed, which allows you to maintain uncompressed data in an active partitions format, and to periodically compress the data when appropriate.
The SAP ASE server supports partition-level compression for all forms of partitioning except round-robin partitions.
Columns marked as not compressed are not selected for row or page compression. However, in-row columns (including materialized computed columns) are eligible for compression:
- All fixed-length data smaller than 4 bytes is ineligible for row compression. However, the SAP ASE server may compress these datatypes during page-index compression.
- All data, fixed or with a variable length of 4 bytes or larger, is eligible for row compression.
By default, the SAP ASE server creates uncompressed nonmaterialized computed columns.
The SAP ASE server first compresses the columns eligible for compression at the row level. If the compressed row is longer than the uncompressed row, the SAP ASE server discards the compressed row and stores the uncompressed row on disk, ensuring that compression does not waste space.
Data pages may simultaneously contain compressed and uncompressed data rows.
You may compress fixed-length columns.
You can use the with exp_row_size clause to create compressed data-only-locked (DOL) tables only for fixed-length rows. You cannot use the with exp_row_size clause on allpages-locked (APL) tables.
If you specify an expected row size, but the uncompressed row length is smaller than the expected row size, the SAP ASE server does not compress the row.
After you enable compression for a table, all bcp and DML operations that are executed on the table compress the data.
Compression may allow you to store more rows on a page, but it does not change the maximum row size of a table. However, it can change the effective minimum row size of a table.
Use not compressed for columns that could be row- or page-index compressed, but for which the nature of the column makes compression inapplicable or meaningless (for example, columns that use the bit datatype, encryption, or a timestamp column).
Compressing a table does not compress its indexes.