Compression can be enabled for individual tables or for all disk-based tables inside the database. The default behavior is controlled by the General.CompressAllTables parameter, see General section.

If General.CompressAllTables=yes, all tables are compressed and compression cannot be disabled for any tables by using the ALTER TABLE statement. If General.CompressAllTables=no, compression can be enabled or disabled for individual tables by using the COMPRESSED and NONCOMPRESSED properties with the CREATE TABLE and ALTER TABLE statements, see CREATE TABLE and ALTER TABLE.

In HotStandby environments, it is recommended to have the same setting in both databases.

Typically compression is enabled when a table is created. If compression is enabled after the table is created, existing pages (that are already full) are not a target for the compression algorithm. Even updating all the rows in a noncompressed table after the compression property is set does not necessarily lead to compressing the data. If the updated rows still fit into same page there is no page split and thus no compression of data. Hence, compressing an existing populated table or database is not a simple operation, see Compressing or decompressing an existing database.

Running backup or HotStandby netcopy does not change the compression status of pages. Compressed pages stay compressed and uncompressed pages stay uncompressed.