Text, ntext, and image values are not stored as part of the data row but in a separate collection of pages of their own. For each text, ntext, or image value, all that is stored in the data row is a 16-byte pointer. For each row, this pointer points to the location of the text, ntext, or image data. A row containing multiple text, ntext, or image columns has one pointer for each text, ntext, or image column.
Each table has only one collection of pages to hold text, ntext, and image data. The sysindexes row that has indid = 255 is the anchor for the collection. The text, ntext, and image data for all the rows in the table is interleaved in this collection of text and image pages.
In Microsoft® SQL Server™ version 7.0, individual text, ntext, and image pages are not limited to holding data for only one occurrence of a text, ntext, or image column. A text, ntext, or image page can hold data from multiple rows; the page can even have a mix of text, ntext, and image data.
While the user always works with text, ntext, and image data as if it is a single long string of bytes, the data is not stored in that format. The data is stored in a collection of 8 KB pages that are not necessarily located next to each other. In SQL Server 7.0, the pages are organized logically in a B-tree structure, while in earlier versions of SQL Server they were linked together in a page chain. The advantage of the method used by SQL Server 7.0 is that operations starting in the middle of the string are more efficient. SQL Server 7.0 can quickly navigate the B-tree, while older versions of SQL Server had to scan through the page chain. The structure of the B-tree differs slightly depending on whether there is less than 32 KB of data or more.
If there is less than 32 KB of data, the 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.
While 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 text, ntext, 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, then 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 that are not shared between occurrences of text, ntext, or image columns. A page storing intermediate nodes contains only intermediate nodes for one text, ntext, or image data value in one data row.