Let’s start with the same table as last time but we’ll recreate it in a permanent database, rather than in tempdb.

USE master;

IF EXISTS (SELECT 1 FROM sys.databases WHERE name = N'CompressionTest')
    DROP DATABASE CompressionTest;

CREATE DATABASE CompressionTest;

USE CompressionTest;

CREATE TABLE dbo.SalesOrderDetail
        SalesOrderID int NOT NULL,
        SalesOrderDetailID int NOT NULL,
        CarrierTrackingNumber nvarchar(25) NULL,
        OrderQty smallint NOT NULL,
        ProductID int NOT NULL,
        SpecialOfferID int NOT NULL,
        UnitPrice money NOT NULL,
        UnitPriceDiscount money NOT NULL,
    CONSTRAINT PK_dbo_SalesOrderDetail
        PRIMARY KEY (SalesOrderID, SalesOrderDetailID)

INSERT dbo.SalesOrderDetail
    (SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber,
     OrderQty, ProductID, SpecialOfferID, UnitPrice,
SELECT SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber,
       OrderQty, ProductID, SpecialOfferID, UnitPrice,
FROM AdventureWorks2012.Sales.SalesOrderDetail;

We can then check the space that is being occupied by the table:

EXEC sp_spaceused N'dbo.SalesOrderDetail';

This command displays the following output:

Let’s take a look at the data that will be on the first page of that table. We know which rows that will be because we’ll order our SELECT by the clustered index:

FROM dbo.SalesOrderDetail
ORDER BY SalesOrderID, SalesOrderDetailID;

These are the first three rows on the first page:

We’ll need them to refer back to later.

Next, let’s take a look at the contents of the data page:


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);

Turning on trace flag 3604 redirects DBCC output to the client instead of to the SQL Server logs. We’re then using sys.dm_db_database_page_allocations to locate all the pages for the table, and then filtering to retrieve only data pages. The data pages are double-linked, so to find the first page, I’ve then looked for a page that has no previous page.

Once, I’ve found a suitable page number to look at, I’ve used DBCC PAGE to display its contents. (It’s parameters are the database, the file (in this case 1), the page number, and the type of output required. The value 1 will give us the formatted output that we need here.

It contains 3 basic sections. The first section is the header:

While this contains lots of interesting information, the parts that are of interest to us now are:

  • m_type = 1 indicates that this is in fact a data page

  • m_level = 0 is the index level and in this case it’s the leaf level of the clustered index

  • pminlen = 38 indicates the number of fixed length bytes in each row

  • m_slotCnt = 114 shows the number of rows (slots) on the page

  • m_freeCnt = 2 indicates that there are only 2 free bytes on the page. The page is quite full.

The end of the page contains the third section which is the slot array. It is stored in reverse order:

(I removed many rows to keep this short).

But the main area of interest for us today is the 2nd section, which is the actual data:

Each byte is represented as two hexadecimal digits. Values are stored in least significant byte first. In the first row of the first slot (ie: slot 0), you can see a record size of 69 bytes. You can see that the data for each row starts with the same marker value in each row.

The first 38 bytes are the fixed data region. As it’s in hexadecimal, it will help if we convert some of the values from the first row:

SELECT sys.fn_varbintohexstr(43659), sys.fn_varbintohexstr(776);

This returns the following output:

If you look carefully at the value in the first row above, you can see it is 8baa0000 and that’s the same value with the bytes in the reverse order. If you wander through the other values, you’ll find that the int, and smallint values are stored first. Later, the nvarchar value for the carrier tracking number 4911-403C-98 is stored. You can see it later in the data. Keep in mind that it is stored as double-byte Unicode characters. That’s why there are dots shown in between the characters.