SQL Server database compression

You can specify SQL Server database compression options for PolicyCenter by using the <mssql-compression> element of <mssql-db-ddl> in database-config.xml:

<database>
  ...
  <upgrade>
    <mssql-db-ddl>
      <mssql-compression table-compression="NONE|PAGE|ROW" index-compression="NONE|PAGE|ROW">
    </mssql-db-ddl>
  </upgrade>
</database>

The <mssql-compression> element accepts the attributes table-compression and index-compression. You can specify one or both attributes. Attributes that you specify for <mssql-compression> apply to all tables and indexes in the database.

Settings for table-compression and index-compression can apply to the entire database, a table, or an index, depending on the XML element to which the attribute is applied. In general, these values mean the following:

  • A value of NONE specifies that the database or table is not compressed.
  • A value of PAGE specifies that the database or table uses page-level compression. SQL Server applies page compression only if the page becomes full. For page compression, the following operations happen in the listed order:
    • Row compression
    • Prefix compression
    • Dictionary compression
  • A value of ROW specifies that the database or table uses row compression. Row compression drastically reduces the metadata needed for variable-length columns.

See also