Database Reference
In-Depth Information
Listing 1-11.
ROW_OVERFLOW data: DBCC PAGE results for ROW_OVERFLOW data
Blob row at: Page (1:214645) Slot 0 Length: 8014 Type: 3 (DATA)
Blob Id:2686976
0000000008E0A06E: 62626262 62626262 62626262 62626262 bbbbbbbbbbbbbbbb
0000000008E0A07E: 62626262 62626262 62626262 62626262 bbbbbbbbbbbbbbbb
0000000008E0A08E: 62626262 62626262 62626262 62626262 bbbbbbbbbbbbbbbb
As you see,
Col2
data is stored in the first slot on the page.
LOB Storage
For the
text
,
ntext
, or
image
columns, SQL Server stores the data off-row by default. It uses another kind of page
called
LOB data pages
.
■
You can control this behavior up to a degree by using the “text in row” table option. For example,
exec sp_
table_option dbo.MyTable, 'text in row', 200
forces SQL Server to store LoB data less than or equal to 200 bytes
in-row. LoB data greater than 200 bytes would be stored in LoB pages.
Note
The logical LOB data structure is shown in Figure
1-12
.
Figure 1-12.
LOB data: Logical structure
Like ROW_OVERFLOW data, there is a pointer to another piece of information called the
LOB root structure
,
which contains a set of the pointers to other data pages/rows. When LOB data is less than 32 KB and can fit into five
data pages, the LOB root structure contains the pointers to the actual chunks of LOB data. Otherwise, the LOB tree
starts to include an additional, intermediate levels of pointers, similar to the index B-Tree, which we will discuss in
Chapter 2, “Tables and Indexes: Internal Structure and Access Methods.”
