You can use the ALTER TABLE statement to enable compression on an existing table in SQL Server.

To do this, you need to use the REBUILD WITH option, while specifying your desired compression type.

Example

Here’s an example to demonstrate.

ALTER TABLE Cats
REBUILD WITH (DATA_COMPRESSION = ROW);

In this case I rebuilt the table using row compression.

The following rebuilds it with page compression.

ALTER TABLE Cats
REBUILD WITH (DATA_COMPRESSION = PAGE);

Apply Compression to a Single Partition

If your table is partitioned, you can specify that partition to rebuild with compression.

ALTER TABLE Cats
REBUILD PARTITION = 1 WITH (DATA_COMPRESSION = ROW);

How to Remove Compression

You can remove the compression by using NONE as the compression type.

ALTER TABLE Cats
REBUILD WITH (DATA_COMPRESSION = NONE);

Columnstore Tables

If you’re using columnstore tables (tables stored with a clustered columnstore index), the above compression types don’t apply. In this case, your compression options are COLUMNSTORE and COLUMNSTORE_ARCHIVE.

Limitations/Restrictions

System tables can’t be enabled for compression.

If the table is a heap (a table without a clustered index), the rebuild operation for ONLINE mode will be single threaded. For a multi-threaded heap rebuild operation, use OFFLINE mode.

Also, when using partitioned tables, the following restrictions apply:

• You can’t change the compression setting of a single partition if the table has nonaligned indexes.
• The ALTER TABLE <table> REBUILD PARTITION ... syntax rebuilds the specified partition.
• The ALTER TABLE <table> REBUILD WITH ... syntax rebuilds all partitions.