Greater compression can be obtained by compressing the entire page, rather than each row one at a time. So if ROW compression is excellent, is PAGE compression better? Let’s look at how it works. Let’s start by applying PAGE compression, and checking the size again.

ALTER TABLE dbo.SalesOrderDetail

EXEC sp_spaceused N'dbo.SalesOrderDetail';

This returns the following:

That’s 31% of the original size or a 69% reduction in table size.

Let’s look at 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);

Once again, the page header and the offset table are similar:

Note that the m_slotCnt (number of slots) value has gone up to 479 slots in the page. We are now holding far more rows in the same number of pages.

It’s the data section that has changed the most. Note that the data row detail has changed:

Note how few bytes are used per column, and that a big part of that reduction is from the introduction of symbols. These symbols point to a dictionary that’s held on the page. A prefix reduction technique has been used, and duplicate values have been replaced by symbols.

For example, note that the column 3 value (which was the carrier tracking number string) has been replaced by a single byte token. Where did its value go? We can see it in the dictionary section of the page dump: