column compression

[MCMartin]

The documentation recommends not to compress columns with less than 130 chars. Anyway the sa_column_stats procedure has a value num_values_compressed. So there seems to be a mechanism which prevents to compress rows which have a value of less than 130 chars and still compress rows with values where compression would be beneficial.

Is this correct?

[VolkerBarth]

Which version do you use? AFAIK, there have been relevant changes w.r.t. column compression (in V10, I think). The CREATE TABLE statement has the INLINE and PREFIX clauses for this, cf. http://dcx.sybase.com/index.html#1200en/dbreference/create-table-statement.htm

[MCMartin]

Oh, I wasn't aware of the changes, the version I am referring to is 11.0.1 Will the default for prefix mean that everything > 8 Bytes will be compressed? Or is there still an additional margin, because the compression overhead is said to be at least 12 Bytes...

[Breck_Carter]

@Volker: The INLINE and PREFIX values affect how a blob is stored (inline with the rest of the row or separately in a blob page). AFAIK they have nothing to do with COMPRESSED.

[MCMartin]

From the documentation it seems, that at least prefix is also affecting compression: The prefix data for a compressed column is stored uncompressed, so if all the data required to satisfy a request is stored in the prefix, no decompression is necessary

[graeme_perrow · Accepted solution]

Breck's statement that the INLINE and PREFIX values are not related to column compression is incorrect. Values shorter than the INLINE value of the column (default 256) are not compressed, and the statement that "The prefix data for a compressed column is stored uncompressed, so if all the data required to satisfy a request is stored in the prefix, no decompression is necessary" is correct.

The bit in the docs that says "Do not use column compression on columns containing values under 130 bytes" is more of a guideline, since attempting to compress values that small will likely result in a larger value being stored because of the compression overhead. It only applies if you've set your INLINE value to something less than 130 anyway.

[MCMartin · Accepted solution]

Great to know, so normally leaving everything default will mean that only the data in the extended pages will be compressed for varchar columns. Maybe this should be stated more explicit in the documentation.