Export (0) Print
Expand All

sp_tableoption (Azure SQL Database)

Updated: November 21, 2014

This topic is OBSOLETE. You can find the most current version in the SQL 14 Transact-SQL Reference.

This topic is not maintained. For the current version, see sp_tableoption.

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, or image columns.

Syntax Conventions (Azure SQL Database)

sp_tableoption [ @TableNamePattern = ] 'table' 
     , [ @OptionName = ] 'option_name' 
     ,[ @OptionValue =] 'value'

[ @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 cannot be set at the same time. table 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 be one of the following values.


Value Description

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.

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 (binary large object: text, ntext, or image data) will be changed to text in row format when the BLOB value is updated.

large value types out of row

1 = varchar(max), nvarchar(max), varbinary(max), and xml columns in the table are stored out of row, with a 16-byte pointer to the root.

0 = varchar(max), nvarchar(max), varbinary(max), and xml values are stored directly in the data row, up to a limit of 8000 bytes and as long as the value can fit in the record. If the value does not fit in the record, a pointer is stored in-row and the rest is stored out of row in the LOB storage space. 0 is the default value.

vardecimal storage format

Not supported.

[ @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.

For more information about the arguments and the sp_tableoption system stored procedure, see sp_tableoption in SQL Server Books Online.

Returns 0 for success or an error number for failure.

Executing sp_tableoption requires ALTER permission on the table.

To examine the option values for a specific table, query the sys.tables catalog view.

When an unsupported option vardecimal storage format is used, the following error message is returned: "40512: Deprecated feature 'Vardecimal storage format' is not supported in this version of SQL Server". Similarly, error "15600: An invalid parameter or option was specified for procedure 'sys.sp_tableoption'" occurs when the insert row lock option name is used.

© 2015 Microsoft