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.