Row-Level Compression

Row-level compression compresses individual rows in a table.

Row-level compression is intended for fixed-length, regular data. For most fixed-length columns, data does not completely occupy the space reserved for the row. For example, a 32-bit integer with a value of 2 is represented by 0x10 in hexadecimal. Adaptive Server requires 1 byte to represent this value, but fills the other 3 bits of the row with zeros. Similarly, if a 50-byte fixed-length character column includes the character data “a”, Adaptive Server requires 1 byte for the character data, but completes the column with zeros.

Some fixed-length datatypes are not compressed because there is no benefit in doing so. For example, Adaptive Server uses only 1 byte to store a tinyint, so compressing a row using this datatype is not beneficial.

For example, if you create this uncompressed table:

create table t1 (col1 char(1) not null,
    col2 char(50) not null,
    col3 tinyint not null,
    col4 int not null,
    col5 varchar(20))
lock datapages

After changing the compression level to row:

alter table t1
set compression = row

Adaptive Server does not compress col1 and col3 because their length is 1 byte. Adaptive Server compresses col2 and col4 and stores required information about decompression for each column using the minimum space, if required.

If you insert these values into t1:

insert t1 values (
"a", "aaaaa", 1, 100, "NineBytes")

The compressed version of the columns comprises 17 bytes, nearly one-third the size of the uncompressed columns:

When uncompressed, the value of col2, char(50) is “aaaaa” with 45 blanks to fill out the rest of the column. After compression, the value of col2 is “aaaaa”, using one byte for each “a”.
The value of col4 is 100, and is represented with a single byte.
Trailing blanks are truncated from the value of col5; 9 bytes to store the value.