Sets option values for user-defined tables. sp_tableoption can be used to control the in-row behavior of tables with varchar(max), nvarchar(max), varbinary(max), xml, text, ntext, image, or large user-defined type columns.
The text in row feature will be removed in a future version of SQL Server. To store large value data, we recommend that you use of the varchar(max), nvarchar(max) and varbinary(max) data types.
sp_tableoption can be used only to set option values for user-defined tables. To display table properties, use OBJECTPROPERTY.
The text in row option in sp_tableoption can be enabled or disabled only on tables that contain text columns. If the table does not have a text column, SQL Server raises an error.
When the text in row option is enabled, the @OptionValue parameter allows users to specify the maximum size to be stored in a row for a BLOB. The default is 256 bytes, but values can range from 24 through 7000 bytes.
text, ntext, or image strings are stored in the data row if the following conditions apply:
text in row is enabled.
The length of the string is shorter than the limit specified in @OptionValue
There is enough space available in the data row.
When BLOB strings are stored in the data row, reading and writing the text, ntext, or image strings can be as fast as reading or writing character and binary strings. SQL Server does not have to access separate pages to read or write the BLOB string.
If a text, ntext, or image string is larger than the specified limit or the available space in the row, pointers are stored in the row instead. The conditions for storing the BLOB strings in the row nonetheless apply: There must be enough space in the data row to hold the pointers.
BLOB strings and pointers stored in the row of a table are treated similarly to variable-length strings. SQL Server uses only the number of bytes required to store the string or the pointer.
Existing BLOB strings are not converted immediately when text in row is first enabled. The strings are converted only when they are updated. Likewise, when the text in row option limit is increased, the text, ntext, or image strings already in the data row will not be converted to adhere to the new limit until the time they are updated.
Disabling the text in row option or reducing the limit of the option will require the conversion of all BLOBs; therefore, the process can be long, depending on the number of BLOB strings that must be converted. The table is locked during the conversion process.
A table variable, including a function that returns a table variable, automatically has the text in row option enabled with a default inline limit of 256. This option cannot be changed.
text in row supports the TEXTPTR, WRITETEXT, UPDATETEXT, and READTEXT functions. Users can read parts of a BLOB with the SUBSTRING() function, but must remember that in-row text pointers have different duration and number limits from other text pointers. For more information, see Managing ntext, text, and image Data.
To change a table from vardecimal storage format back to the normal decimal storage format, the database must be in SIMPLE recovery mode. Changing the recovery mode will break the log chain for backup purposes, therefore you should create a full database backup after removing the vardecimal storage format from a table.
A. Storing xml data out of the row
The following example specifies that the xml data in the HumanResources.JobCandidate table be stored out of row.
USE AdventureWorks2008R2; GO EXEC sp_tableoption 'HumanResources.JobCandidate', 'large value types out of row', 1;
B. Enabling vardecimal storage format on a table
The following example modifies the Production.WorkOrderRouting table to store the decimal data type in the vardecimal storage format.
USE master; GO -- The database must be enabled for vardecimal storage format -- before a table can be enabled for vardecimal storage format EXEC sp_db_vardecimal_storage_format 'AdventureWorks2008R2', 'ON'; GO USE AdventureWorks2008R2; GO EXEC sp_tableoption 'Production.WorkOrderRouting', 'vardecimal storage format', 'ON';