SQL Server : Compression

The available types of compression are listed below.

  • Row-level Compression : Turns fixed width columns into variable width columns, so space padding is saved. Doesn’t store NULL or 0.
  • Prefix Compression : Looks for repeated patterns in the start of strings.
  • Dictionary Compression : Looks for repeated patterns throughout the page.
  • Page-level Compression : Does row-level compression, then adds prefix and dictionary compression.

You can estimate the benefits of compression using the following commands.

-- Syntax
sp_estimate_data_compression_savings 'schema', 'table', NULL, NULL, 'ROW';
sp_estimate_data_compression_savings 'schema', 'table', NULL, NULL, 'PAGE';

-- Example
sp_estimate_data_compression_savings 'sdowner', 'SDR_ACTIVITY_SETS', NULL, NULL, 'ROW';
sp_estimate_data_compression_savings 'sdowner', 'SDR_ACTIVITY_SETS', NULL, NULL, 'PAGE';

Here are some examples of compressing tables and partitions.

-- Table
ALTER TABLE schema.partition REBUILD
WITH
( DATA_COMPRESSION = row -- none, row, page, columnstore, columnstore_archive
)

-- All partitions.
ALTER TABLE schema.partition REBUILD PARTITION = ALL
WITH
( DATA_COMPRESSION = row -- none, row, page, columnstore, columnstore_archive
)

-- Individual partition.
ALTER TABLE schema.partition REBUILD PARTITION = partition-number
WITH
( DATA_COMPRESSION = row -- none, row, page, columnstore, columnstore_archive
)

For more information see: