text, ntext, and image Data
Individual text, ntext, and image values can be a maximum of 2-GB, which is too long to store in a single data row. In Microsoft® SQL Server™ 2000, small text, ntext, or image values can be stored directly in the row, but values too large to fit in the row are stored in a collection of pages separate from the pages holding the data for the other columns of the row.
The administrator uses the text in row option in sp_tableoption to specify whether small text, ntext, or image values are stored directly in a row:
- When text in row is OFF, SQL Server 2000 has the same ntext, text, and image behavior as SQL Server version 7.0. 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.
- When text in row is ON, SQL Server 2000 stores small text, ntext, and image values in the data row. Only text, ntext, or image values that cannot fit in the row are stored in a separate collection of pages.
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 SQL Server 2000, 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.
Although 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 2000, the pages are organized logically in a B-tree structure, and in SQL Server version 6.5 and earlier they are linked in a page chain. The advantage of the method used by SQL Server 2000 is that operations starting in the middle of the string are more efficient. SQL Server 2000 can quickly navigate the B-tree, and SQL Server version 6.5 must scan through the page chain.