ntext, text, and image Data When text in row Is Set to OFF
The structure of the B-tree used to store text, ntext, or image data when the text in row option of sp_tableoption is set to OFF differs slightly if there is less than 32 KB of data than if there is more.
If there is less than 32 KB of data, the 16-byte text pointer in the data row points to an 84-byte text root structure. This forms the root node of the B-tree structure. The root node points to the blocks of text, ntext, or image data.
Although the data for text, ntext, and image columns is arranged logically in a B-tree, both the root node and the individual blocks of data are spread throughout the chain of text, ntext, and image pages for the table. They are placed wherever there is space available. The size of each block of data is determined by the size written by an application. Small blocks of data will be combined to fill a page. If there is less than 64 bytes of data, it is all stored in the root structure.
For example, if an application first writes 1 KB of image data, this is stored as the first 1-KB block of image data for the row. If the application then writes 12 KB of image data, then 7 KB is combined with the first 1-KB block so the first block becomes 8 KB. The remaining 5 KB forms the second block of image data. (The actual capacity of each ntext, text, or image page is 8080 bytes of data.)
Because the blocks of text, ntext, or image data and the root structures can all share space on the same text, ntext, or image pages, SQL Server 7.0 uses less space with small amounts of text, ntext, or image data than earlier versions of SQL Server. For example, if you insert 20 rows that each have 200 bytes of data in a text column, the data and all the root structures can all fit on the same 8-KB page.
If the amount of data for one occurrence of a text, ntext, or image column exceeds 32 KB, SQL Server starts building intermediate nodes between the data blocks and the root node.
The root structure and the data blocks are interleaved throughout the text, ntext, or image pages in the same manner as described earlier. The intermediate nodes, however, are stored in pages not shared between occurrences of text, ntext, or image columns. A page storing intermediate nodes contains only intermediate nodes for one ntext, text, or image data value in one data row.