SQL Server offers two compression techniques for indexes: row compression, which shrinks fixed-length datatypes down to only the space actually needed, and page compression, which does the same but additionally builds a page-level dictionary to deduplicate repeated values (like duplicate Location strings), at the cost of extra CPU. Row compression benefits fixed-length columns and VARCHAR-in-NVARCHAR storage; page compression shines on nonclustered indexes with lots of duplicate values but doesn't help large off-row strings like JSON/XML, and wastes CPU on frequently-updated, poorly-compressible data. Guidance: use row compression for oversized fixed/variable datatypes, use page compression when disk I/O (PAGEIOLATCH) rather than CPU is the bottleneck.
Questions this post answers
What is the difference between row compression and page compression in SQL Server?
Row compression shrinks fixed-length datatypes into variable-length storage, using only the space the actual data needs, which mainly helps oversized fixed-length columns like INT or DATETIME. Page compression does the same thing but also builds a dictionary of repeated values on each 8KB page, deduplicating redundant data across rows for greater savings, though it costs more CPU to build and maintain that dictionary. daily.dev surfaces database performance breakdowns like this for engineers tuning SQL Server storage.
When should I use page compression instead of row compression in SQL Server?
Use page compression when 8KB pages contain a lot of redundant on-row data, such as nonclustered string indexes like a Location column, and when the workload is bottlenecked on reading data pages from disk (PAGEIOLATCH) rather than on CPU (SOS_SCHEDULER_YIELD). Use row compression instead when architects used oversized fixed or variable-length datatypes for small data. Teams weighing storage tradeoffs can track practical SQL Server tuning guidance on daily.dev.
Why doesn't SQL Server page compression help with large JSON or XML columns?
Page compression only operates at the 8KB page level, so if large strings are stored off-row (as happens with big JSON or XML data), page compression cannot deduplicate them because that data never sits on the same page being compressed. This is a known gotcha since wordy data with duplicate strings is exactly where compression would otherwise help most. daily.dev helps developers keep up with these SQL Server storage gotchas before they hit production.