When you compress a partitioned table in SQL Server, you can compress all partitions, some, or just one partition.

To do this, use the REBUILD PARTITION syntax within the ALTER TABLE statement.

When doing this, you can rebuild just the specified partition or all partitions. Alternatively, you can rebuild all partitions, while compressing only a specific partition or list of partitions.

Example 1 – Rebuild One Partition

In this first example, we rebuild and compress just one partition in the table.

Estimate Compression Savings

Imagine we want to compress a single partition in a table called Movies.

First, we can use the sp_estimate_data_compression_savings system stored procedure to estimate the savings we would get from compressing the table.

EXEC sp_estimate_data_compression_savings 
    @schema_name = 'dbo', 
    @object_name = 'Movies', 
    @index_id = NULL, 
    @partition_number = 3, 
    @data_compression = 'ROW';

Result (using vertical output):

object_name                                        | Movies
schema_name                                        | dbo
index_id                                           | 1
partition_number                                   | 3
size_with_current_compression_setting(KB)          | 120
size_with_requested_compression_setting(KB)        | 88
sample_size_with_current_compression_setting(KB)   | 128
sample_size_with_requested_compression_setting(KB) | 96

According to sp_estimate_data_compression_savings, the partition size should reduce from 120 KB to 88 KB once we’ve compressed it.

Compress the Partition

Let’s go ahead and compress it.

ALTER TABLE Movies
REBUILD PARTITION = 3 WITH (DATA_COMPRESSION = ROW);

In this case I used row compression.

Verify the Compression Savings

Now, if we query sp_estimate_data_compression_savings again, we’ll see that the current size is now exactly as was estimated previously (88 KB).

EXEC sp_estimate_data_compression_savings 
    @schema_name = 'dbo', 
    @object_name = 'Movies', 
    @index_id = NULL, 
    @partition_number = 3, 
    @data_compression = 'NONE';

Result (using vertical output):

object_name                                        | Movies
schema_name                                        | dbo
index_id                                           | 1
partition_number                                   | 3
size_with_current_compression_setting(KB)          | 88
size_with_requested_compression_setting(KB)        | 112
sample_size_with_current_compression_setting(KB)   | 96
sample_size_with_requested_compression_setting(KB) | 128

In this example, I’m getting an estimate of the space requirements if I were to remove the compression (i.e. set it to NONE).

Verify Which Partitions use Compression

We can also use the sys.partitions view to verify that the partition has compression.

SELECT
    [partition_number],
    [data_compression],
    [data_compression_desc]
FROM sys.partitions
WHERE object_id = OBJECT_ID('Movies');

Result:

+--------------------+--------------------+-------------------------+
| partition_number   | data_compression   | data_compression_desc   |
|--------------------+--------------------+-------------------------|
| 1                  | 0                  | NONE                    |
| 2                  | 0                  | NONE                    |
| 4                  | 0                  | NONE                    |
| 3                  | 1                  | ROW                     |
+--------------------+--------------------+-------------------------+

Remove Compression

Before we go on to example 2, let’s remove the compression from the partition.

ALTER TABLE Movies
REBUILD PARTITION = 3 WITH (DATA_COMPRESSION = NONE);

Example 2 – Compress Multiple Partitions

In this example, we rebuild all partitions, but specify just the partitions we want to be compressed.

This is an alternative method to our first example. Using this syntax, we can specify a list of partitions to compress.

ALTER TABLE Movies
REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = ROW ON PARTITIONS(2,3));

In this case I rebuilt all partitions, but I only compressed partitions 2 and 3.

Again, we can use sys.partitions to verify that they were compressed.

SELECT
    [partition_number],
    [data_compression],
    [data_compression_desc]
FROM sys.partitions
WHERE object_id = OBJECT_ID('Movies');

Result:

+--------------------+--------------------+-------------------------+
| partition_number   | data_compression   | data_compression_desc   |
|--------------------+--------------------+-------------------------|
| 1                  | 0                  | NONE                    |
| 2                  | 1                  | ROW                     |
| 3                  | 1                  | ROW                     |
| 4                  | 0                  | NONE                    |
+--------------------+--------------------+-------------------------+