SQL Server index compression

PolicyCenter uses a clustered primary key index. Since this index is actually the table data itself, PolicyCenter uses the compression setting for the table for the primary key index.

The compression setting of a table is not automatically applied to its non-clustered indexes. You must configure compression settings for indexes separately or in bulk.

You can override compression options for all indexes in the database by setting the index-compression attribute on the <mssql-compression> element to NONE, PAGE, or ROW.
For this syntax and a general description of the options for index-compression, see SQL Server database compression.

You can override compression options for all indexes on a specific table by setting the index-compression attribute on the <mssql-table-compression> element. For example:

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

You can override options for a specific index by adding an <mssql-index-ddl> element within the <mssql-table-ddl> element for the table that has the index.
- Specify an index by setting the key-columns attribute of the <mssql-index-ddl> element to a comma-delimited list of key columns in order.
- Specify DESC after a column name for descending sort order on that column.
For example:
```
<database>
  ...
  <upgrade>
    <mssql-db-ddl>
      <mssql-table-ddl table-name="pc_tableName">
        <mssql-index-ddl key-columns="column1,column2" index-compression="true|false" />
      </mssql-table-ddl>
    </mssql-db-ddl>
  </upgrade>
</database>
```

The following example specifies the following:

Row index compression for the entire database.
No compression for the pc_Activity index that contains key columns PublicID and Retired in key positions one and two, respectively.

Page compression for any indexes on the pc_Workflow table.

<database>
  ...
  <upgrade>
    <mssql-db-ddl>
      <mssql-compression index-compression="ROW">
      <mssql-table-ddl table-name="pc_Activity">
        <mssql-index-ddl key-columns="PublicID,Retired" index-compression="NONE" />
      </mssql-table-ddl>
      <mssql-table-ddl table-name="pc_Workflow">
        <mssql-table-compression index-compression="PAGE" />
      </mssql-table-ddl>
    </mssql-db-ddl>
  </upgrade>
</database>