Now that we know what the “normal” data rows looks like, let’s add row compression and check the size again.

ALTER TABLE dbo.SalesOrderDetail

EXEC sp_spaceused N'dbo.SalesOrderDetail';

There is a significant size reduction:

The table is now 54% of its original size ie: a 46% overall reduction in size.

Let’s look into the page contents again:

DECLARE @DatabaseID int = DB_ID();
DECLARE @PageToDisplay int =
    (SELECT TOP(1) allocated_page_page_id
     FROM sys.dm_db_database_page_allocations
         (@DatabaseID, OBJECT_ID(N'dbo.SalesOrderDetail'), 1, NULL, 'DETAILED')
     WHERE page_type_desc = N'DATA_PAGE'
     AND previous_page_page_id IS NULL);

DBCC PAGE (@DatabaseID, 1, @PageToDisplay, 1);

The page header is quite similar:

Note however, that pminlen (fixed data length) is now 6, and there are now 214 rows per page (m_slotCnt), even though the m_freeCnt is now 26 (slightly more free space).

The slot array at the end is the basically the same except it has more entries:

But look at the data in the rows. It has become a series of compressed data array entries:

The values have been stored in shorter locations, and note that the string value that was nvarchar now is using single bytes not double bytes. The overall sizes for the rows shown are now 36 bytes each.

This shows how effective ROW compression is, without really rearranging the page structure all that much. Rows can still be read/written much the same way as with uncompressed data. We generally find the performance of ROW compression indistinguishable from uncompressed data. On systems with I/O bottlenecks however, we usually see a notable performance improvement.