sp_tableoption
Sets option values for user-defined tables. sp_tableoption may be used to turn on the text in row feature on tables with text, ntext, or image columns.
Syntax
sp_tableoption [ @TableNamePattern = ] 'table'
, [ @OptionName = ] 'option_name'
, [ @OptionValue = ] 'value'
Arguments
[@TableNamePattern =] 'table'
Is the qualified or nonqualified name of a user-defined database table. If a fully qualified table name, including a database name, is provided, the database name must be the name of the current database. Table options for multiple tables can not be set at the same time. table_pattern is nvarchar(776), with no default.
[@OptionName =] 'option_name'
Is a table option name. option_name is varchar(35), with no default of NULL. option_name can have these values.
| Value | Description |
|---|---|
| pintable | When disabled (the default), it marks the table as no longer RAM-resident. When enabled, marks the table as RAM-resident. |
| table lock on bulk load | When disabled (the default), it causes the bulk load process on user-defined tables to obtain row locks. When enabled, it causes the bulk load processes on user-defined tables to obtain a bulk update lock. |
| insert row lock | Not supported in Microsoft® SQL Server™ 2000.
For SQL Server version 6.5, enabled or disabled Insert Row Locking (IRL) operations on the specified table. Row-level locking is enabled by default in SQL Server version 7.0. The locking strategy of SQL Server is row locking with possible promotion to page or table locking. This option does not alter the locking behavior of SQL Server (it has no effect) and is included only for compatibility of existing scripts and procedures. |
| text in row | When OFF or 0 (disabled, the default), it does not change current behavior, and there is no BLOB in row.
When specified and @OptionValue is ON (enabled) or an integer value from 24 through 7000, new text, ntext, or image strings are stored directly in the data row. All existing BLOB (text, ntext, or image data) will be changed to text in row format when the BLOB value is updated. See Remarks section for more information. |
[@OptionValue =] 'value'
Is whether the option_name is enabled (true, on, or 1) or disabled (false, off, or 0). value is varchar(12), with no default. value is case insensitive.
For the text in row option, valid option values are 0, on, off, or an integer from 24 through 7000. When value is on, the limit defaults to 256 bytes.
Return Code Values
0 (success) or error number (failure)
Remarks
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 may 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 (binary large objects: text, ntext, or image data). The default is 256 bytes, but values may 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 still apply though: 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 needed 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.
Note Disabling the text in row option or reducing the option's limit will require the conversion of all BLOBs, so 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 keep in mind that in-row text pointers have different duration and number limits than other text pointers. For more information, see Managing ntext, text, and image Data.
Permissions
Only members of the sysadmin fixed server role can modify the pintable table option.
Members of the sysadmin fixed server role, the db_owner and db_ddladmin fixed database roles, and the table owner can modify the table lock on bulk load, and text in row options for any user-defined table. Other users can modify options only for tables they own.
Examples
A. Enable the 'text in row' option for table 'orders' in the Northwind database.
EXEC sp_tableoption 'orders', 'text in row', 'ON'
B. Enable the 'text in row' option for table 'orders' in the Northwind database, and set the inline limit to 1000.
EXEC sp_tableoption 'orders', 'text in row', '1000'
C. Enable the 'text in row' option for table 'orders' in the Northwind database, and set the inline limit to 23, which is beyond the allowable range.
sp_tableoption 'orders', 'text in row', '23'
You will get an error saying the parameter is out of range.
D. Disable the 'text in row' option for table 'orders' in the Northwind database.
EXEC sp_tableoption 'orders', 'text in row', 'off'
-or-
EXEC sp_tableoption 'orders', 'text in row', '0'