SQL Server’s DATA_COMPRESSION feature is a powerful way to reduce storage space and improve I/O performance.

Types of DATA_COMPRESSION in SQL Server

Compression Type	Description	Best Use Cases
NONE	No compression applied.	Default setting.
ROW	Compresses fixed-length data types by storing them more efficiently.	Good for OLTP workloads with frequent inserts/updates.
PAGE	Includes ROW compression + additional compression across entire pages.	Best for read-heavy workloads and large tables.
COLUMNSTORE	Uses columnar storage and compression.	Ideal for data warehousing and analytics.
COLUMNSTORE_ARCHIVE	Further compresses column store data for archival.	Best for rarely accessed historical data.

How to Apply Compression

You can apply compression using T-SQL:

-- Apply ROW compression
ALTER TABLE dbo.YourTable
REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = ROW);

-- Apply PAGE compression
ALTER INDEX ALL ON dbo.YourTable
REBUILD WITH (DATA_COMPRESSION = PAGE);

-- Apply COLUMNSTORE compression
CREATE CLUSTERED COLUMNSTORE INDEX CCI_YourTable
ON dbo.YourTable
WITH (DATA_COMPRESSION = COLUMNSTORE);

How to Estimate Compression Savings

Use sp_estimate_data_compression_savings:

EXEC sp_estimate_data_compression_savings
    'dbo', 'YourTable', NULL, NULL, 'PAGE';

This will give you an estimate of space savings before applying compression.

Data Compression in Practice

Data compression in SQL Server is a feature that allows you to save storage space by reducing the size of the database. However, it has the drawback of increasing CPU usage because the data must be compressed and decompressed when being accessed

You can configure data compression on the following:

• Clustered tables
• Heap tables (a heap is a table without a clustered index)
• Non-clustered indexes
• Indexed views
• Individual partitions of a partitioned table or index

There are three forms of data compression you can use with SQL Server:

• Row-level compression: This works by using more efficient storage formats for fixed-length data. It stores fixed-length numeric data types and CHAR data types as though they were variable-length data types, does not store NULL or 0 values, and reduces metadata required to store data
• Unicode compression: This is used to compress Unicode data types
• Page-level compression: This provides more storage improvements compared to row-level compression

For example, you can compress an index using the following SQL command:

ALTER INDEX [index_name] ON [table_name] REBUILD WITH (DATA_COMPRESSION=ROW, ONLINE=ON, SORT_IN_TEMPDB=ON, PAD_INDEX=ON, FILLFACTOR=95)
GO
select  object_name(object_id ),* from sys.partitions
where [data_compression] <>0
select distinct object_name(object_id ) from sys.partitions
where [data_compression] <>0
select * from sys.dm_db_persisted_sku_features

Data Compression: Quick Notes

You can configure data compression on the following:

• Clustered tables
• Heap tables (a heap is a table without a clustered index)
• Non-clustered indexes
• Indexed views
• Individual partitions of a partitioned table or index

There are three forms of data compression you can use with SQL Server:

• Unicode compression: This is used to compress Unicode data types.

Example od index compression:

ALTER INDEX [index_name] ON [table_name] REBUILD WITH (DATA_COMPRESSION=ROW,ONLINE=ON,SORT_IN_TEMPDB=ON,PAD_INDEX=ON,FILLFACTOR=95)
GO

From my SQL Server DBA handbook.