Data Storage Internals - Pro SQL Server Internals - page 13

Figure 1-10. ROW_OVERFLOW data: DBCC IND results
Now you can see two different sets of IAM and data pages. The data page with PageType=3 represents the data
page that stores ROW_OVERFLOW data.
Let's look at data page 214647, which is the in-row data page that stores main row data. The partial output of the
DBCC PAGE command for the page (1:214647) is shown in Listing 1-10.
Listing 1-10. ROW_OVERFLOW data: DBCC PAGE results for IN_ROW data
Slot 0 Offset 0x60 Length 8041
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 8041
Memory Dump @0x000000000FB7A060
0000000000000000: 30000800 01000000 03000002 00511f69 9f616161 0............Q.iŸaaa
0000000000000014: 61616161 61616161 61616161 61616161 61616161 aaaaaaaaaaaaaaaaaaaa
0000000000000028: 61616161 61616161 61616161 61616161 61616161 aaaaaaaaaaaaaaaaaaaa
000000000000003C: 61616161 61616161 61616161 61616161 61616161 aaaaaaaaaaaaaaaaaaaa
0000000000000050: 61616161 61616161 61616161 61616161 61616161 aaaaaaaaaaaaaaaaaaaa
<Skipped>
0000000000001F2C: 61616161 61616161 61616161 61616161 61616161 aaaaaaaaaaaaaaaaaaaa
0000000000001F40: 61616161 61616161 61616161 61616161 61020000 aaaaaaaaaaaaaaaaa...
0000000000001F54: 00010000 00290000 00401f00 00754603 00010000 .....)…@…uF.....
0000000000001F68: 00
As you see, SQL Server stores Col1 data in-row. Col2 data, however, has been replaced with a 24-byte value. The
first 16 bytes are used to store off-row storage metadata, such as type, length of the data, and a few other attributes.
The last 8 bytes is the actual pointer to the row on the row-overflow page, which is the file, page, and slot number.
Figure 1-11 shows this in detail. Remember that all information is stored in byte-swapped order.
Figure 1-11. ROW_OVERFLOW data: Row-overflow page pointer structure
As you see, the slot number is 0, file number is 1, and page number is the hexadecimal value 0x00034675 , which
is decimal 214645 . The page number matches the DBCC IND results shown in Figure 1-10 .
The partial output of the DBCC PAGE command for the page (1:214645) is shown in Listing 1-11.