Data Storage Internals - Pro SQL Server Internals - page 15

Let's create the table and insert one row of data, as shown in Listing 1-12. We need to cast the first argument of
the replicate function to varchar(max) . Otherwise, the result of the replicate function would be limited to 8,000
bytes.

Listing 1-12. LOB data: Table creation

create table dbo.TextData
(
ID int not null,
Col1 text null
);

insert into dbo.TextData(ID, Col1) values (1, replicate(convert(varchar(max),'a'),16000));

The page allocation for the table is shown in Figure 1-13 .

Figure 1-13. LOB data: DBCC IND result

As you see, the table has one data page for in-row data and three data pages for LOB data. I am not going to
examine the structure of the data row for in-row allocation; it is similar to the ROW_OVERFLOW allocation. However,
with the LOB allocation, it stores less metadata information in the pointer and uses 16 bytes rather than the 24 bytes
required by the ROW_OVERFLOW pointer.
The result of DBCC PAGE command for the page that stores the LOB root structure is shown in Listing 1-13.

Listing 1-13. LOB data: DBCC PAGE results for the LOB page with the LOB root structure

Blob row at: Page (1:3046835) Slot 0 Length: 84 Type: 5 (LARGE_ROOT_YUKON)

Blob Id: 131661824 Level: 0 MaxLinks: 5 CurLinks: 2

Child 0 at Page (1:3046834) Slot 0 Size: 8040 Offset: 8040
Child 1 at Page (1:3046832) Slot 0 Size: 7960 Offset: 16000

As you see, there are two pointers to the other pages with LOB data blocks, which are similar to the blob data
shown in Listing 1-11.
The format, in which SQL Server stores the data from the (MAX) columns, such as varchar(max) , nvarchar(max) ,
and varbinary(max) , depends on the actual data size. SQL Server stores it in-row when possible. When in-row
allocation is impossible, and data size is less or equal to 8,000 bytes, it stored as row-overflow data. The data that
exceeds 8,000 bytes is stored as LOB data.

■ text , ntext , and image data types are deprecated, and they will be removed in future versions of SQL Server.
Use varchar(max) , nvarchar(max) , and varbinary(max) columns instead.

Note