Parameter Interactions with Data Compression

The various select parameters behave differently when working with compression.

  • max_rows_per_page applies only to allpages-locked compressed tables; you cannot use it on data-only-locked tables.

  • You can use exp_row_size on allpages- and data-only-locked tables with variable-length columns. However, the table must be able to expand updates on the same page without causing page splits on allpages-locked tables or or row forwarding on data-only-locked tables.

    exp_row_size computes space limits identically for compressed and uncompressed tables. However, because compression results in a page with a lot of free space, the space set aside for exp_row_size may cause far fewer rows to fit on the page than would fit on this page if you did not set exp_row_size.

    You cannot use exp_row_size on tables with fixed-length columns, even though the SAP ASE server may convert some fixed-length columns to variable-length columns during the compression process.

  • fillfactor applies determines the space used on the data pages after compression. This is relevant only when you are creating clustered indexes, an operation which requires a sort of the data pages.