Compress tables and indexes

Since SQL Server 2016 SP1, compression is available in every edition of SQL Server, including Express.

Compression unfortunately has to be applied individually, to each table and each index, either when the object is created or when it is rebuilt.

There are two compression modes.

ROW compression

ROW compression is not compression in the sense of a specific algorithm. It is closer to making fixed-length types variable, plus Unicode compression.

In summary:

• very useful for correcting mistakes and over-generous data type choices;
• it converts the data type of each value on the fly, transparently inside the storage engine. The value 1 stored in an INT column, for instance, is converted on the fly to a TINYINT;
• it applies a Unicode conversion algorithm, very useful for correcting unnecessary NVARCHAR column choices, stored as UTF-16 on two bytes per character.

It can save a great deal of space, I/O and RAM, with no noticeable downside on CPU load. Enable it freely.

PAGE compression

PAGE compression adds a dictionary-based compression algorithm on each 8 KB page, on top of ROW compression. It costs slightly more CPU, but the cost stays moderate. There is generally enough spare CPU on a database server to absorb it without trouble.

This compression is useful in tables and indexes containing many duplicate values:

• tables in a data mart;
• indexes;
• poorly normalised tables.

Finding the uncompressed objects

You can list the uncompressed objects with this query.

The cmd column of the result gives you the compression code for each object. Select that column alone and switch the results to text output (Ctrl+T) in SSMS, to generate the commands with the GO batch separators on their own lines.

Estimating the gain

You can estimate what either compression mode would save on a table or an index.

• With SSMS: in Object Explorer, right-click the object, table or index, then Storage ➥ Manage Compression. The Calculate button gives you an estimate of the gain for ROW or PAGE compression.
• With the sp_estimate_data_compression_savings stored procedure, which is not available on Azure SQL Database.
• On my GitHub there is a script built on that procedure which estimates the compression benefit on a table and all of its indexes.

Compressing objects

You can compress a table or an index when the object is created. Compression only has to be applied once: the storage engine then maintains it on that object.

For example:

-- ROW compression
CREATE TABLE mytable (id INT)
WITH (DATA_COMPRESSION = ROW);

-- PAGE compression
CREATE TABLE mytable (id INT)
WITH (DATA_COMPRESSION = PAGE);

And for an index:

-- ROW compression
CREATE INDEX ix ON mytable (id)
WITH (DATA_COMPRESSION = ROW);

-- PAGE compression
CREATE INDEX ix ON mytable (id)
WITH (DATA_COMPRESSION = PAGE);

Compressing existing objects

You can rebuild a table or an index to compress it after the fact:

-------- Table ---------
-- ROW compression
ALTER TABLE mytable REBUILD WITH (DATA_COMPRESSION = ROW);
-- or PAGE compression
ALTER TABLE mytable REBUILD WITH (DATA_COMPRESSION = PAGE);

-------- Index ---------
-- ROW compression
ALTER INDEX ix ON mytable REBUILD WITH (DATA_COMPRESSION = ROW);
-- or PAGE compression
ALTER INDEX ix ON mytable REBUILD WITH (DATA_COMPRESSION = PAGE);

Avoiding blocking on the Enterprise edition

On the Enterprise edition you can use the ONLINE option to rebuild a table or an index without blocking.

-------- Table ---------
-- ROW compression
ALTER TABLE mytable REBUILD WITH (ONLINE = ON, DATA_COMPRESSION = ROW);
-- or PAGE compression
ALTER TABLE mytable REBUILD WITH (ONLINE = ON, DATA_COMPRESSION = PAGE);

-------- Index ---------
-- ROW compression
ALTER INDEX ix ON mytable REBUILD WITH (ONLINE = ON, DATA_COMPRESSION = ROW);
-- or PAGE compression
ALTER INDEX ix ON mytable REBUILD WITH (ONLINE = ON, DATA_COMPRESSION = PAGE);