ROW VS PAGE VS COLUMNSTORE VS ARHIVE DATA COMPRESSION on SQLServer

Storage and the size of your database can become a real danger if you don't actively monitor your table sizes, archive old data, and implement compression.
Left unchecked, a table won't just eat up your disk space it will choke your backups, impact buffer pool, drag down query speeds.

Moving from lowest to highest storage savings, you can start from ROW compression for active OLTP tables, to PAGE compression for read heavy tables, to COLUMNSTORE for data Warehouses, and finally to COLUMNSTORE ARCHIVE for rarely accessed data.

Keep in mind that savings vary heavily by table structure, uniform varchars, dates, and duplicate data compress good.
While standard row data provides predictable compression, Large Object (LOB) data like types XML or max types won't give you much.

🫡 Before you start anything, you can estimate your compressed size using the query below for every type.

Just change @data_compression input.

EXEC sp_estimate_data_compression_savings 
    @schema_name = 'dbo', 
    @object_name = 'yourTable', 
    @index_id = NULL, 
    @partition_number = NULL, 
    @data_compression = 'PAGE'; -- 'ROW', 'PAGE', 'COLUMNSTORE', 'COLUMNSTORE_ARCHIVE'

You need to always know your biggest storage consumers.
Run this query to scan your database, track down and pinpoint exactly which tables are ready for compression.

SELECT 
    s.name AS SchemaName,
    t.name AS TableName,
    SUM(p.rows) AS TotalRows,
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.object_id = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.is_ms_shipped = 0
    AND i.object_id > 255 
GROUP BY 
    s.name, t.name
ORDER BY 
    TotalSpaceMB DESC;

If you aren't sure how frequently a table is being used, run this query to check index usage. Keep in mind that these metrics reset every time SQL Server restarts. Avoid heavy compression on tables with high updates.

SELECT * FROM sys.dm_db_index_operational_stats(DB_ID(), OBJECT_ID('yourTable'), NULL, NULL);

SELECT
    i.name AS IndexName,
    us.user_seeks,
    us.user_scans,
    us.user_lookups,
    us.user_updates
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats us
    ON i.object_id = us.object_id
   AND i.index_id = us.index_id
   AND us.database_id = DB_ID()
WHERE i.object_id = OBJECT_ID('dbo.yourTable')
ORDER BY i.name;

🤔 Now that you know your targets, let us pick the right compression.

1. ROW: Converts fixed length storage to variable length and strips out empty bytes (zero and NULL) perfect for high write with low CPU overhead.

2. PAGE: ROW Compress plus adds prefix and dictionary to reuse repetitive data page. Ideal for read heavy tables.

3. COLUMNSTORE: Switches from row storage to uniform column blocks. Massively shrinks Data Warehouses and speeds reporting queries.

4. COLUMNSTORE_ARCHIVE: Applies secondary XPRESS compression algorithms on top of Columnstore data. Delivers ultimate storage savings for cold historical or compliance data.

⚠️ WARNING: Never rebuild on a tight disk. SQL Server creates a shadow copy during compression, so a 2 TB table requires at least 1 TB of free data and log to avoid a rollback.

-- Row Compression
ALTER TABLE yourTable
REBUILD WITH (DATA_COMPRESSION = ROW);

-- Page Compression
ALTER TABLE yourTable
REBUILD WITH (DATA_COMPRESSION = PAGE);

-- Columnstore Compression
-- Requires creating a Clustered Columnstore Index. You need to recreate your PK after this.
CREATE CLUSTERED COLUMNSTORE INDEX CCI_yourTable
ON yourTable 
WITH (DROP_EXISTING = ON);

-- Columnstore Archive Compression
ALTER TABLE yourTable 
REBUILD WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE);

😎 BONUS TIP: Application Level Column Compression

SQL Server provides native COMPRESS() and DECOMPRESS() functions that use the algorithm to compress values directly at the session level. It doesn't alter table properties or require index rebuilds, perfect for LOB data.

-- Text
INSERT INTO yourTable (DataColumn)
VALUES (COMPRESS('text or JSON string data'));

SELECT 
    CAST(DECOMPRESS(DataColumn) AS VARCHAR(MAX))
FROM yourTable;

-- XML
INSERT INTO yourTable (DataColumn)
VALUES (COMPRESS('XML string data'));
SELECT 
    CAST(DECOMPRESS(BinaryDataColumn) AS XML)
FROM yourTable;

Reference:

• https://learn.microsoft.com/en-us/sql/relational-databases/data-compression/data-compression?view=sql-server-ver17