In-Row Data
Small-to-medium large value types (varchar(max), nvarchar(max), varbinary(max), and xml) and large object (LOB) data types (text, ntext, and image) can be stored in a data row. This behavior is controlled by using two options in the sp_tableoption system stored procedure: the large value types out of row option for large value types, and the text in row option for large object types. These options are best used for tables in which the data values of any one of these data types are typically read or written in one unit, and most statements that reference the table refer to this kind of data. Depending on usage or workload characteristics, storing data in-row may not be useful.
Important
The text in row option will be removed in a future version of SQL Server. Avoid using this option in new development work, and plan to modify applications that currently use text in row. We recommend that you store large data by using the varchar(max), nvarchar(max), or varbinary(max) data types. To control in-row and out-of-row behavior of these data types, use the large value types out of row option.
Unless the text in row option is set to ON or to a specific in-row limit, text, ntext, or image strings are large character or binary strings (up to 2 gigabytes) that are 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. These pointers map the pages in which the string fragments are stored. For more information about the storage of text, ntext, or image strings, see Using text and image Data.
You can set a text in row option for tables that contain LOB data type columns. You can also specify a text in row option limit, from 24 through 7,000 bytes.
Similarly, unless the large value types out of row option is set to ON, varchar(max), nvarchar(max), varbinary(max), and xml columns are stored, if it is possible, inside the data row. If this is the case, the SQL Server Database Engine tries to fit the specific value if it can, and will push the value off-row otherwise. If large value types out of row is set to ON, the values are stored off-row and only a 16-byte text pointer is stored in the record.
Note
The maximum in-row storage for large value data types is set to 8,000 bytes when large value types out of row is OFF. Unlike the text in row option, you cannot specify the in-row limit for columns in the table.
When a table is configured to store either large value types or large object data types directly in the data row, the actual column values will be in-row if either of the following conditions exist:
The length of the string is shorter than the specified limit for text, ntext, and image columns.
There is sufficient space available in the data row to hold the string.
When a large value type or a large object data type column value is stored in the data row, the Database Engine does not have to access a separate page or set of pages to read or write the character or binary string. This makes reading and writing the in-row strings about as fast as reading or writing limited size varchar, nvarchar, or varbinary strings. Similarly, when the values are stored off-row, the Database Engine incurs an additional page read or write.
For large object data types, if the 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 either of the following conditions exist:
The amount of space needed to store the pointers is shorter than the specified text in row option limit.
There is sufficient space available in the data row to hold the pointers.
When pointers are moved from the root node to the row itself, the Database Engine does not have to use a root node. This can eliminate a page access when reading or writing the string. This improves performance.
When root nodes are used, they are stored as one of the string fragments in a LOB page and can contain up to five internal pointers. The Database Engine needs 72 bytes of space in the row to store five pointers for an in-row string. If there is insufficient space in the row to hold the pointers when the text in row option is ON or the large value types out of row option is OFF, the Database Engine may have to allocate an 8-K page to hold them. If the data length of the value exceeds 40,200 bytes, more than five in-row pointers are required, at which point only 24 bytes are stored in the main row and an additional data page is allocated on the LOB storage space.
When large strings are stored in the row, they are stored similarly to variable-length strings. The Database Engine sorts columns in decreasing size order and pushes values off-row until the remaining columns fit in the data page (8K).
Enabling and Disabling the large value types out of row Option
You can enable the large value types out of row option for a table by using sp_tableoption in the following way:
sp_tableoption N'MyTable', 'large value types out of row', 'ON'
If you specify OFF, the in-row limit for varchar(max), nvarchar(max), varbinary(max), and xml columns is set to 8,000 bytes. Only a 16-byte root pointer is stored in-row and the value is stored in the LOB storage space. We recommend setting this option to ON for tables in which most statements do not reference the large value types columns. Storing these columns out-of-row implies that more rows can be fit per page, therefore reducing the number of I/O operations required to scan the table.
With the value of this option set to OFF, many strings may end up stored in the row itself, potentially reducing the number of data rows that fit on each page. If most statements that reference the table do not access the varchar(max), nvarchar(max), varbinary(max), and xml 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 number of pages that might have to be scanned if the optimizer finds no usable index.
You can also use sp_tableoption to disable the out-of-row option:
sp_tableoption N'MyTable', 'large value types out of row', 'OFF'
When the large value types out of row option value is changed, existing varchar(max), nvarchar(max), varbinary(max), and xml values are not immediately converted. The storage of the strings is changed as they are subsequently updated. Any new values inserted into a table are stored according to the table option in effect.
To examine the value of the large value types out of row option for a specific table, query the large_value_types_out_of_row column of the sys.tables catalog view. This column is 0 if the table does not have large value types out of row enabled, and 1 if large value types are stored out of row.
Enabling and Disabling the text in row Option
You can enable the text in row option for a table by using sp_tableoption in the following way:
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 provides most of the performance benefits that can be gained by using the text in row option. Although you generally should not set the value lower than 72, you also should not set the value too high. This especially applies 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 that reference 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 have to be scanned if the optimizer finds no usable index. The default value of 256 for the text in row limit is large enough to make sure 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.
The text in row option is automatically set to 256 for variables with a table data type and for tables returned by user-defined functions that return a table. This setting cannot be changed.
You can also use sp_tableoption to disable the option by specifying an option value of either OFF or 0:
sp_tableoption N'MyTable', 'text in row', 'OFF'
To examine the value of the text in row option for a specific table, query the text_in_row_limit column of the sys.tables catalog view. This column is 0 if the table does not have text in row enabled, and a value greater than 0 if the in-row limit has been set.
Effects of Using the text in row Option
The text in row option has the following effects:
After you have enabled the text in row option, you can use the TEXTPTR, 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 the whole text, ntext, or image string, or use the SUBSTRING function to read parts of the string. All INSERT or UPDATE statements that reference 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 enabled is inserted as an in-row string.
Disabling 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 SQL Server Native Client OLE DB Provider or the SQL Server Native Client 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 enabled.