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

适用对象:是SQL Server 是Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

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

语法Syntax

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

参数Arguments

[@TableNamePattern =]"table"[ @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. tablenvarchar (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.

“值”Value 描述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 (enabled)或从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 值时,所有现有 BLOB (二进制大型对象: textntextimage数据)都将改为行格式的文本。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 2008 和更高版本。Large user-defined type (UDT) applies to: SQL Server 2008SQL Server 2008 and later.

使用CREATE TABLE的 TEXTIMAGE_ON 选项来指定大数据类型存储的位置。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 2008 及更高版本。Applies to: SQL Server 2008SQL Server 2008 and later.

为 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. 仅当使用sp_db_vardecimal_storage_format为 vardecimal 存储格式启用了数据库时,才能启用 vardecimal 存储格式。Vardecimal 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 =]"value"[ @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)

RemarksRemarks

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

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.

如果满足以下条件, textntextimage字符串将存储在数据行中: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.

如果textntextimage字符串大于指定的限制或行中的可用空间,则指针将存储在行中。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 选项的限制增加时,数据行中已有的textntextimage字符串将不会转换为符合新的限制,直到更新它们为止。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_row更改为1以获得最佳性能。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设置或将每个中小型大值类型列更新为自身之后重新填充表,以便将字符串的存储更改为有效的 table 选项。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.

PermissionsPermissions

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

示例Examples

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

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

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

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

下面的示例修改 Production.WorkOrderRouting 表,以 vardecimal 存储格式存储 decimal 数据类型。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 'AdventureWorks2012', 'ON';  
GO  
USE AdventureWorks2012;  
GO  
EXEC sp_tableoption 'Production.WorkOrderRouting',   
   'vardecimal storage format', 'ON';  

另请参阅See Also

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