sp_tableoption (Transact-SQL)sp_tableoption (Transact-SQL)

适用于: 是SQL Server是Azure SQL 数据库否Azure SQL 数据仓库否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

设置用户定义表的选项值。Sets option values for user-defined tables. sp_tableoption 可用于控制包含的表的行内行为varchar (max)nvarchar (max)varbinary (max)xml文本ntext映像,或大型用户定义类型列。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.


SQL ServerSQL Server 的未来版本中,将删除 text in row 功能。The text in row feature will be removed in a future version of SQL ServerSQL Server. 若要存储大值数据,我们建议你使用的varchar (max)nvarchar (max)varbinary (max) 数据类型。To store large value data, we recommend that you use of the varchar(max), nvarchar(max) and varbinary(max) data types.

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions


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


[ @TableNamePattern =] '[ @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. nvarchar(776) ,无默认值。table is nvarchar(776), with no default.

[ @OptionName =] 'option_name[ @OptionName = ] 'option_name'
表选项名称。Is a table option name. option_namevarchar(35) ,无默认值为 NULL。option_name is varchar(35), with no default of NULL. option_name可以是下列值之一。option_name can be one of the following values.

ReplTest1Value 描述Description
table lock on bulk loadtable 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 lockinsert row lock 不再支持。No longer supported.

此选项对 SQL ServerSQL Server 的锁定行为没有影响,保留此选项只是为了与现有脚本和过程兼容。This option has no effect on the locking behavior of SQL ServerSQL Server and is included only for compatibility of existing scripts and procedures.
text in rowtext in row 如果为 OFF 或 0(禁用,默认值),则不更改当前行为,且在行中不存在 BLOB。When OFF or 0 (disabled, the default), it does not change current behavior, and there is no BLOB in row.

当指定为和@OptionValue为 ON (启用) 或介于 24 到 7000,新的整数值文本ntext,或映像字符串直接存储在数据行。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. 现有的所有 BLOB (二进制大型对象:文本ntext,或图像数据) 更新了 BLOB 值将更改为 text in 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. 有关详细信息,请参阅“备注”。For more information, see Remarks.
large value types out of rowlarge value types out of row 1 = varchar (max)nvarchar (max)varbinary (max)xml和表中的大型用户定义类型 (UDT) 列存储在行外,用一个 16 字节指针指向根目录。1 = varchar(max), nvarchar(max), varbinary(max), xml and large user-defined type (UDT) columns in the table are stored out of row, with a 16-byte pointer to the root.

0 = varchar (max)nvarchar (max)varbinary (max)xml和大型 UDT 值直接存储在数据行,最大限制8000 个字节,只要记录中可以容纳该值。0 = varchar(max), nvarchar(max), varbinary(max), xml and large UDT 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. 如果记录中容纳不下该值,则指针存储在行内,其余内容存储在 LOB 存储空间内的行外。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。0 is the default value.

大型用户定义类型 (UDT) 适用范围:SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017Large user-defined type (UDT) applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017.

使用的 TEXTIMAGE_ON 选项CREATE TABLE指定较大的数据类型存储的位置。Use the TEXTIMAGE_ON option of CREATE TABLE to specify a location for storage of large data types.
Vardecimal 存储格式vardecimal storage format 适用范围SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017.

为 TRUE、ON 或 1 时,将为 vardecimal 存储格式启用指定的表。When TRUE, ON, or 1, the designated table is enabled for vardecimal storage format. 为 FALSE、OFF 或 0 时,将不为 vardecimal 存储格式启用此表。When FALSE, OFF, or 0, the table is not enabled for vardecimal storage format. 仅当该数据库已通过使用启用了 vardecimal 存储格式时,可以启用 Vardecimal 存储格式sp_db_vardecimal_storage_formatVardecimal storage format can be enabled only when the database has been enabled for vardecimal storage format by using sp_db_vardecimal_storage_format. 在中SQL Server 2008SQL Server 2008及更高版本, vardecimal存储格式已弃用。In SQL Server 2008SQL Server 2008 and later, vardecimal storage format is deprecated. 请改用 ROW 压缩。Use ROW compression instead. 有关详细信息,请参阅 Data CompressionFor more information, see Data Compression. 默认值为 0。0 is the default value.

[ @OptionValue =] '[ @OptionValue =] 'value'
是是否option_name启用 (TRUE、 ON 或 1) 还是禁用 (FALSE、 OFF 或 0)。Is whether the option_name is enabled (TRUE, ON, or 1) or disabled (FALSE, OFF, or 0). varchar(12) ,无默认值。value is varchar(12), with no default. 是不区分大小写。value is case insensitive.

对于 text in row 选项,有效选项值是 0、ON、OFF,或从 24 到 7000 的整数。For the text in row option, valid option values are 0, ON, OFF, or an integer from 24 through 7000. 为 ON 时,默认为 256 个字节的限制值。When value is ON, the limit defaults to 256 bytes.

返回代码值Return Code Values

0(成功)或错误号(失败)0 (success) or error number (failure)


sp_tableoption 仅可用于设置用户定义表的选项值。sp_tableoption can be used only to set option values for user-defined tables. 若要显示表属性,请使用 OBJECTPROPERTY。To display table properties, use OBJECTPROPERTY.

sp_tableoption 中的 text in row 选项只能对包含文本列的表启用或禁用。The text in row option in sp_tableoption can be enabled or disabled only on tables that contain text columns. 如果表不含文本列,SQL ServerSQL Server 将引发错误。If the table does not have a text column, SQL ServerSQL Server raises an error.

当启用了 text in row 选项时,@OptionValue参数允许用户指定要为 BLOB 存储在行中的最大大小。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. 默认值为 256 字节,但是值可以介于 24 到 7000 个字节之间。The default is 256 bytes, but values can range from 24 through 7000 bytes.

文本ntext,或图像字符串存储在数据行中,如果以下条件适用:text, ntext, or image strings are stored in the data row if the following conditions apply:

  • text in row 已启用。text in row is enabled.

  • 字符串的长度小于中指定的限制 @OptionValueThe length of the string is shorter than the limit specified in @OptionValue

  • 数据行中有足够的可用空间。There is enough space available in the data row.

当在数据行中存储 BLOB 字符串时,读取和写入文本ntext,或图像字符串可以与读取或写入字符和二进制字符串一样快。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 ServerSQL Server 不必访问不同的页就可读取或写入 BLOB 字符串。does not have to access separate pages to read or write the BLOB string.

如果文本ntext,或图像字符串大于指定的限制或行中的可用空间,而是指针存储在行。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. 在行中存储 BLOB 字符串的条件依然适用:在数据行来存放指针中必须有足够的空间。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 字符串和指针被视为类似于可变长度字符串。BLOB strings and pointers stored in the row of a table are treated similarly to variable-length strings. SQL ServerSQL Server 仅使用存储字符串或指针所需的字节数。uses only the number of bytes required to store the string or the pointer.

如果先启用了 text in row,则不会立即转换现有的 BLOB 字符串。Existing BLOB strings are not converted immediately when text in row is first enabled. 仅当字符串被更新时才将其转换。The strings are converted only when they are updated. 同样,text in row 选项限制已增加,文本ntext,或图像字符串数据行中已将不会转换以遵从新限制直到它们进行更新的时间。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.


禁用 text in row 选项或减少该选项的限制值需要转换所有的 BLOB;因此,此过程可能需要较长的时间,具体时间则取决于必须转换的 BLOB 字符串数。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.

表变量(包括返回表变量的函数)的 text in row 选项会自动启用,并将内联限制值默认为 256 个字节。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 选项支持 TEXTPTR、 WRITETEXT、 UPDATETEXT 和 READTEXT 函数。The text in row option supports the TEXTPTR, WRITETEXT, UPDATETEXT, and READTEXT functions. 用户可以使用 SUBSTRING() 函数读取部分 BLOB,但是必须记住,各个行内文本指针之间具有不同的持续时间和数量限制。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.

若要将表从 vardecimal 存储格式改回为正常的十进制存储格式,数据库必须处于 SIMPLE 恢复模式。To change a table from vardecimal storage format back to the normal decimal storage format, the database must be in SIMPLE recovery mode. 更改恢复模式将断开用于备份目的的日志链,因此在从表中删除 vardecimal 存储格式后,应创建完整数据库备份。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.

如果要将现有 LOB 数据类型列 (text、 ntext 或 image) 转换为小型到中型大值类型 (varchar (max)、 nvarchar (max),或 varbinary(max)) 和大多数语句是否不引用你的环境中的大值类型列,请考虑更改large_value_types_out_of_row1以获得最佳性能。If you are converting an existing LOB data type column (text, ntext, or image) to small-to-medium large value types (varchar(max), nvarchar(max), or varbinary(max)) , and most statements do not reference the large value type columns in your environment, consider changing large_value_types_out_of_row to 1 to gain optimal performance. large_value_types_out_of_row选项值已更改,现有 varchar (max)、 nvarchar (max)、 varbinary (max),并不会立即转换 xml 值。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. 对于直接结果,可以制作一份数据和更改后再重新填充表large_value_types_out_of_row设置或更新到其自身的每个小中大型值类型列,以便的存储字符串是有效更改的表选项。For immediate results, either make a copy of the data and then repopulate the table after changing the large_value_types_out_of_row setting or update each small-to-medium large value types column to itself so that the storage of the strings is changed with the table option in effect. 考虑在更新或重新填充后重新生成表的索引,以压缩表格。Consider rebuilding the indexes on the table after the update or repopulation to condense the table.


执行 sp_tableoption 要求对表拥有 ALTER 权限。To execute sp_tableoption requires ALTER permission on the table.


A.A. 将 xml 数据存储在行外Storing xml data out of the row

下面的示例指定xml中的数据HumanResources.JobCandidate表将存储在行外。The following example specifies that the xml data in the HumanResources.JobCandidate table be stored out of row.

USE AdventureWorks2012;  
EXEC sp_tableoption 'HumanResources.JobCandidate', 'large value types out of row', 1;  

B.B. 在表中启用 vardecimal 存储格式Enabling vardecimal storage format on a table

下面的示例修改Production.WorkOrderRouting表以存储decimal中的数据类型vardecimal存储格式。The following example modifies the Production.WorkOrderRouting table to store the decimal data type in the vardecimal storage format.

USE master;  
-- 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 'AdventureWorks2012', 'ON';  
USE AdventureWorks2012;  
EXEC sp_tableoption 'Production.WorkOrderRouting',   
   'vardecimal storage format', 'ON';  

请参阅See Also

sys.tables (Transact-SQL) sys.tables (Transact-SQL)
系统存储过程 (Transact-SQL) System Stored Procedures (Transact-SQL)
数据库引擎存储过程(Transact SQL)Database Engine Stored Procedures (Transact-SQL)