SQL Server Data Compression: ROW vs PAGE vs COLUMNSTORE

One history table has taken most of the drive, and most of its rows date from years you rarely query. SQL Server can store that table in fewer pages, and you choose between three settings: ROW, PAGE, or columnstore. The trade is the same in all three cases, and Microsoft's data compression documentation states both halves of it: queries read fewer pages from disk, and the server spends extra CPU compressing and decompressing the data.

What SQL Server data compression does

Row and page compression apply to rowstore tables and indexes. You can set either one on:

• a whole table stored as a heap
• a whole clustered index
• a whole nonclustered index
• a whole indexed view
• individual partitions of a partitioned table or index, which don't have to agree with each other

Columnstore is a different story. Columnstore tables and indexes are always stored compressed, and that isn't configurable. What you can add on top is columnstore archival compression, which runs the Microsoft XPRESS algorithm over the data for a further reduction.

Two defaults trip people up. A nonclustered index doesn't inherit the compression property of its table, so an index you want compressed has to be named in its own statement. And a heap configured for page compression only page-compresses the rows that arrive one of these ways:

• a bulk import with bulk optimizations enabled
• an INSERT INTO ... WITH (TABLOCK) on a table with no nonclustered index
• an ALTER TABLE ... REBUILD carrying the PAGE option

New pages allocated by ordinary inserts stay uncompressed until the heap is rebuilt.

If the table is also large enough to benefit from partitioning, the two features work together, because you can compress older partitions harder than the one still taking writes.

Edition and version notes

Data compression is not an Enterprise-only feature. The feature list for SQL Server 2022 shows data compression, table and index partitioning, and columnstore available in Enterprise, Standard, Web, Express with Advanced Services, and Express. Data compression reached every edition in SQL Server 2016 (13.x) SP1, which the SQL Server 2016 feature list marks as the service pack that brought it into the common programmability surface area. On an older release, read the feature list for that release before planning a rollout.

The limits below are not about editions and apply everywhere:

• system tables can't be compressed
• a table can't be enabled for compression when its maximum row size plus the compression overhead exceeds the 8,060-byte row limit
• a table with sparse columns can't be compressed, and a sparse column can't be added to a compressed table

ROW vs PAGE vs COLUMNSTORE

Row compression changes the physical storage format only, so no application change follows from turning it on. It stores numeric types (int, decimal, float, and the types built on them such as datetime and money) in a variable-length format, drops the blank padding from fixed character strings, and stores NULL and 0 values in no bytes at all across every data type. What it leaves alone is worth knowing before you predict a number: varchar, nvarchar, text, varbinary, image, uniqueidentifier and xml are unaffected.

Page compression is row compression plus two more passes over the leaf level, run in a fixed order: prefix compression, which pulls a common prefix per column into a structure after the page header and leaves references behind, then dictionary compression, which looks for values repeated anywhere on the page, across columns, and stores each once. Non-leaf index pages get only row compression. Arriving rows are row-compressed as they land, and the page compression pass runs when the page is full and the next row arrives; its result is kept only if it frees enough space to matter. That is why page compression pays on data that repeats itself, and why a page that never fills gains nothing from it.

Columnstore is for the table that gets scanned and aggregated rather than seeked into. Add archival compression on top of it only for partitions you rarely touch, because the documentation is direct that an index carrying archival compression performs slower than one without it.

For broader design decisions, see SQL Server: How to Create a Table and SQL Server: How to Create an Index.

Estimate savings before changing production

The savings depend on the column types, on how much the values repeat, and on the fill factor, so the only number worth quoting for your table is the one you measure on your table. sys.sp_estimate_data_compression_savings measures it without touching the object: it samples the source, loads the sample into an equivalent table in tempdb, compresses that copy to the setting you asked for, and reports both sizes. The examples below run against a history table with a date index:

CREATE TABLE dbo.OrderHistory (
    OrderID    bigint        NOT NULL PRIMARY KEY,
    OrderDate  date          NOT NULL,
    CustomerID int           NOT NULL,
    Status     char(12)      NOT NULL,
    Amount     decimal(12,2) NOT NULL
);

CREATE NONCLUSTERED INDEX IX_OrderHistory_OrderDate
ON dbo.OrderHistory (OrderDate);

EXEC sys.sp_estimate_data_compression_savings
    @schema_name = 'dbo',
    @object_name = 'OrderHistory',
    @index_id = NULL,
    @partition_number = NULL,
    @data_compression = 'PAGE';

Passing NULL for both @index_id and @partition_number returns a row for every index and partition of the object, so one call covers the table and the date index together. Each row carries four sizes in KB:

Run it once per candidate setting and compare the second column:

EXEC sys.sp_estimate_data_compression_savings
    'dbo', 'OrderHistory', NULL, NULL, 'ROW';

EXEC sys.sp_estimate_data_compression_savings
    'dbo', 'OrderHistory', NULL, NULL, 'PAGE';

What the procedure can estimate depends on the release. COLUMNSTORE and COLUMNSTORE_ARCHIVE became valid values for @data_compression in SQL Server 2019 (15.x); on SQL Server 2017 (14.x) and earlier the procedure doesn't apply to columnstore indexes at all, in either direction.

Two results are worth reading carefully. If the estimate for an uncompressed object comes back larger than the current size, the rows are already using nearly the full precision of their types and the compression overhead costs more than it saves, and the documentation's advice for that case is not to enable compression. If the requested setting equals the current one, the number you get back is the size with no fragmentation, which tells you what a plain rebuild alone would recover.

The procedure needs SELECT on the table plus VIEW DATABASE STATE and VIEW DEFINITION on both the database and tempdb, and it takes an intent shared lock on the table while it samples.

The statements that set compression in sqlcmd

Every statement below rebuilds the object it names. Enabling or disabling row and page compression can run online or offline, and it needs as much disk space as creating or rebuilding an index does; on a partitioned object you can reduce that requirement by doing one partition at a time.

Apply ROW compression to a table

ALTER TABLE dbo.OrderHistory
REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = ROW);

Apply PAGE compression to an index

ALTER INDEX IX_OrderHistory_OrderDate
ON dbo.OrderHistory
REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = PAGE);

Compress only older partitions

Where OrderHistory is partitioned by month, the newest partition can stay on ROW while the closed months move to PAGE. Naming a single partition rebuilds only that one:

ALTER INDEX IX_OrderHistory_OrderDate
ON dbo.OrderHistory
REBUILD PARTITION = 1 WITH (DATA_COMPRESSION = PAGE);

ALTER INDEX IX_OrderHistory_OrderDate
ON dbo.OrderHistory
REBUILD PARTITION = 12 WITH (DATA_COMPRESSION = ROW);

Use columnstore for analytics

CREATE CLUSTERED COLUMNSTORE INDEX CCI_SalesFact
ON dbo.SalesFact;

Add archival compression to a partition you rarely read

ALTER TABLE dbo.SalesFact
REBUILD PARTITION = 1
WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE);

Rebuilding the same partition with DATA_COMPRESSION = COLUMNSTORE takes the archival compression back off and leaves the data columnstore-compressed. To read back what is set where, query the data_compression column of sys.partitions.

If your design is headed toward archive-heavy workloads or large warehouse tables, plan compression together with database sharding and partition design rather than after them.

Where the compression decision is recorded in DbSchema

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download

Compression is decided per object, which means the decision has to be recorded somewhere other than a script that ran once.

1. Connect DbSchema through the SQL Server JDBC driver and reverse-engineer the schema, so the candidate tables, their indexes and their partitions are visible in the diagram and in the tree panel before you pick targets.
2. Run the estimation and the rebuild statements in the DbSchema SQL Editor, where Execute Query returns the estimate as a table you can read column by column. Those statements go straight to the live database.
3. Write the reason a table is on PAGE into the table's Description field, which DbSchema stores in the .dbs model file rather than in SQL Server.
4. Export the schema documentation from DbSchema in any format, and the description comes out with it; in the HTML5 output it is also the mouse-over tooltip on the table.

Measure first with sys.sp_estimate_data_compression_savings, apply the setting to one object, and keep the reason next to the table rather than in a ticket. Download DbSchema at https://dbschema.com/download.html and connect to your SQL Server database to run the estimates: the SQL Editor and the diagrams are in the free Community Edition, while saving the model as a .dbs file, the HTML5 documentation and schema synchronization are in Pro.

FAQ

Does compression always improve performance?

Not always, and the CPU cost is only half the reason: enabling compression can change query plans, because the data then sits in a different number of pages with a different number of rows per page. Read the plans of the queries that hit the table after a rollout, not only the size the table now takes.

Can I use different compression on different partitions?

Yes, and the setting follows the partition when the boundaries move. Splitting a partition gives both halves the compression attribute of the original, merging two gives the result the attribute of the destination partition, and switching a partition in requires its compression property to match the table's.