Text in Row Data

Microsoft® SQL Server™ 2000 supports the ability to store small to medium text, ntext, and image values in a data row. The feature is best used for tables in which the data in text, ntext, and image columns is usually read or written in one unit and most statements referencing the table use the text, ntext, and image data.

Unless the text in row option is specified, text, ntext, or image strings are large character or binary strings (up to 2 gigabytes) stored outside a data row. The data row contains only a 16-byte text pointer that points to the root node of a tree built of internal pointers that map the pages in which the string fragments are stored. For more information about the storage of text, ntext, or image strings, see text, ntext, and image Data.

You can set a text in row option for tables containing text, ntext, or image columns. You can also specify a text in row option limit, from 24 through 7,000 bytes. With this option set, text, ntext, or image strings are stored 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 that are otherwise stored in the root node of the pointer tree are stored in the row. The pointers are stored in the row if:

• The amount of space needed to store the pointers is shorter than the specified text in row option limit.
  
  
• There is enough space available in the data row to hold the pointers.

When pointers are moved from the root node to the row itself, SQL Server does not have to use a root node. This can eliminate a page access when reading or writing the string, which speeds processing.

When root nodes are used, they are stored as one of the string fragments in a text, ntext, or image page and can hold up to five internal pointers. SQL Server needs 72 bytes of space in the row to store five pointers for an in-row string. If there is not enough space in the row to hold the pointers when the text in row option is on, SQL Server may have to allocate an 8-K page to hold them. You should not set the text in row limit to less than 72 unless you are certain that all strings stored in the column are either short or over 3 MB.

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 is inserted that is longer than 500 bytes, 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 with a 900-byte string for each text column, and enough data for all of the other columns in the table, leaving 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.

Enabling and Disabling the text in row Option

You can enable the text in row option for a table by using sp_tableoption:

sp_tableoption N'MyTable', 'text in row', 'ON'

Optionally, you can specify a maximum limit, from 24 through 7,000 bytes, for the length of a text, ntext, and image string that can be stored in a data row:

sp_tableoption N'MyTable', 'text in row', '1000'

If you specify on instead of a specific limit, the limit defaults to 256 bytes. This default value allows you most of the performance benefits that can be gained from the text in row option. Although you generally should not set the value below 72, you also should not set the value too high, especially for tables in which most statements do not reference the text, ntext, and image columns, or in which there are multiple text, ntext, and image columns. If you set a large text in row limit, and many strings are stored in the row itself, you can significantly reduce the number of data rows that fit on each page. If most statements referencing the table do not access the text, ntext, or image columns, decreasing the rows in a page can increase the pages that must be read to process queries. Reducing the rows per page can increase the size of indexes and the pages that might need to be scanned if the optimizer finds no usable index. The text in row limit default value of 256 is large enough to ensure that small strings and the root text pointers can be stored in the rows, but not so large that it decreases the rows per page enough to affect performance.

You can also use sp_tableoption to turn the option off by specifying an option value of either off or 0:

sp_tableoption N'MyTable', 'text in row', 'OFF'

Effects of the text in row Option

The text in row option has these effects:

• After you have turned on the text in row option, you cannot use the READTEXT, UPDATETEXT or WRITETEXT statements, to read or modify parts of any text, ntext, or image value stored in the table. In SELECT statements you can read an entire text, ntext, or image string, or use the SUBSTRING function to read parts of the string. All INSERT or UPDATE statements referencing the table must specify complete strings and cannot modify only a part of a text, ntext, or image string.
  
  
• When the text in row option is first enabled, existing text, ntext, or image strings are not immediately converted to in-row strings. The strings are converted to in-row strings only if they are subsequently updated. Any text, ntext, or image string inserted after the text in row option is turned on is inserted as an in-row string.
  
  
• Turning off the text in row option can be a long-running, logged operation. The table is locked and all in-row text, ntext, and image strings are converted to regular text, ntext, and image strings. The length of time the command must run and the amount of data modified depends on how many text, ntext, and image strings must be converted from in-row strings to regular strings.
  
  
• The text in row option does not affect the operation of the OLE DB Provider for SQL Server or the SQL Server ODBC driver, other than to speed access to the text, ntext, and image data.
  
  
• The DB-Library text and image functions, such as dbreadtext and dbwritetext, cannot be used on a table after the text in row option has been turned on.

The text in row option is set to 256 automatically for:

• Variables with a table data type.
  
  
• Tables returned by user-defined functions that return a table.

This setting cannot be changed.