text, ntext, and image Data When text in row Is Set to ON
You enable the text in row option for a table by using sp_tableoption. With the text in row option set to ON, Microsoft® SQL Server™ 2000 stores text, ntext, or image strings directly in the data row if:
- The length of the string is shorter than the specified limit.
- There is enough space available in the data row to hold the string.
When the text, ntext, or image string is stored in the data row, SQL Server does not have to access a separate page or set of pages to read or write the string. This makes reading and writing the text, ntext, or image in-row strings about as fast as reading or writing varchar, nvarchar, or varbinary strings.
If a text, ntext, or image string is longer than the text in row option limit or the available space in the row, the set of pointers otherwise stored in the root node of the pointer tree are stored in the row. Moving the root node to the row itself allows SQL Server to eliminate a page access each time it references the string value, which speeds processing.
A full root structure placed in a data row requires 72 bytes to hold five pointers. If the text in row option limit is less than 72 bytes, or if there are fewer than 72 bytes available in the row, SQL Server puts as many pointers as it can in the row. The lowest limit is 24 bytes, which holds a root node with only one pointer.
Reducing the number of pointers in the root structure truncates the top level of the tree structure used to store the text, ntext, or image string. For example, if the root structure has only three pointers, the top level of the tree structure can only contain three nodes, not five. Reducing the size of the root structure can introduce extra layers in the tree structure. Setting the text in row option limit under 72 can also cause the top level to be truncated.
When text, ntext, or image strings are stored in the row, they are stored similarly to variable-length strings. For example, if the text in row option limit is 500 bytes and you store a 200-byte string in a row, SQL Server uses only the number of bytes needed to store the string. If a string longer than 500 bytes is inserted, so that pointers are stored in the row, SQL Server uses only enough space to hold the pointers and not the entire 500 bytes.
If a table has multiple text, ntext, or image columns, and you attempt to insert multiple text, ntext, or image strings, SQL Server assigns space to the strings one at a time in sequence based on column ID. For example, assume you have a table containing four text columns and you have set the text in row option limit to 1000. You then insert a row where with a 900-byte string for each text column, and enough data for all of the other columns in the table so there is only 3,000 bytes of free space in the row to hold the text strings. The strings for the first three text columns are stored in the row, using 2,700 bytes of the 3,000 bytes available. The string for the fourth text column is not stored in the row, but the pointers from the root node are stored in the row.
Setting the text in row option on has several side effects in regards to processing text, ntext, or image data. For more information, see Managing ntext, text, and image Data.