Home Forums SQL Server 2008 SQL Server 2008 - General If I compress a primary key, am I compressing the whole table --> P-key with DATA_COMPRESSION RE: If I compress a primary key, am I compressing the whole table --> P-key with DATA_COMPRESSION

  • It is also worth understanding how this table is used and if it normally resides in memory or not.

    If it is routinely scanned, then you may choose accept lower savings figures from page compression (say 50% instead of 80%)

    If it is routinely used for key lookups, you may choose to err on the side of row compression

    If it is normally in memory anyway, you may decide to leave it uncompressed.

    If the only way to fit it in memory is through compression, you may evaluate otherwise.

    Same with volatility - if 10% of the table is written every day, compression is less attractive compared to it being read-only.

    Test with representative load and then you'll know what works best for you;-)

    Cheers,

    JohnA

    MCM: SQL2008