Data Storage Internals - Pro SQL Server Internals - page 11

The next four bytes are used to store fixed-length data, which is the ID column in our case. After that, there is
the two-byte value that shows that the data row has four columns followed by a one-byte NULL bitmap. With just
four columns, one byte in the bitmap is enough. It stores the value of 04 , which is 00000100 in the binary format. It
indicates that the third column in the row contains a NULL value.
The next two bytes store the number of variable-length columns in the row, which is 3 ( 0300 in byte-swapped
order). It follows by offset array, in which each two bytes stores the offset where variable-length column data ends.
As you see, even though Col2 is NULL, it still uses the slot in the offset-array. Finally, there is the actual data from the
variable-length columns.
Now let's look at the second data row. Listing 1-7 shows DBCC PAGE output, and Figure 1-9 shows the row data.
Listing 1-7. DBCC PAGE output for the second row
Slot 1 Offset 0x87 Length 27
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 27
Memory Dump @0x000000000EABA087
0000000000000000: 30000800 02000000 04000a02 0011001b 00626262 0................bbb
0000000000000014: 62626262 626262 bbbbbbb
Slot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4
ID = 2
Slot 1 Column 2 Offset 0x0 Length 0 Length (physical) 0
Col1 = [NULL]
Slot 1 Column 3 Offset 0x11 Length 10 Length (physical) 10
Col2 = bbbbbbbbbb
Slot 1 Column 4 Offset 0x0 Length 0 Length (physical) 0
Col3 = [NULL]
Figure 1-9. Second data row data
The NULL bitmap in the second row represents a binary value of 00001010 , which shows that Col1 and Col3 are
NULL. Even though the table has three variable-length columns, the number of variable-length columns in the row
indicates that there are just two columns/slots in the offset-array. SQL Server does not maintain the information about
the trailing NULL variable-length columns in the row.