Data Storage Internals - Pro SQL Server Internals - page 9

Let's create a table, populate it with some data, and look at the actual row data. The code is shown in Listing 1-4.
The Replicate function repeats the character provided as the first parameter 255 times.

Listing 1-4. The data row format: Table creation

create table dbo.DataRows
(
ID int not null,
Col1 varchar(255) null,
Col2 varchar(255) null,
Col3 varchar(255) null
);

insert into dbo.DataRows(ID, Col1, Col3) values (1,replicate('a',255),replicate('c',255));

insert into dbo.DataRows(ID, Col2) values (2,replicate('b',255));

dbcc ind
(
'SQLServerInternals' /*Database Name*/
,'dbo.DataRows' /*Table Name*/
,-1 /*Display information for all pages of all indexes*/
);

An undocumented, but well-known DBCC IND command returns the information about table page allocations.
You can see the output of this command in Figure 1-7 .

Figure 1-7. DBCC IND output

There are two pages that belong to the table. The first one with PageType=10 is a special type of the page called an
IAM allocation map . This page tracks the pages that belong to a particular object. Do not focus on that now, however,
as we will cover allocation map pages later in the chapter.

■ SQL Server 2012 introduces another undocumented data management function (DMF), sys.dm_db_database_
page_allocations , which can be used as a replacement for the DBCC IND command. The output of this DMF provides
more information when compared to DBCC IND , and it can be joined with other system DMVs and/or catalog views.

Note

The page with PageType=1 is the actual data page that contains the data rows. The PageFID and PagePID columns
show the actual file and page numbers for the page. You can use another undocumented command, DBCC PAGE , to
examine its contents, as shown in Listing 1-5.

Listing 1-5. The data row format: DBCC PAGE call

-- Redirecting DBCC PAGE output to console
dbcc traceon(3604)
dbcc page