sp_tableoption (Transact-SQL)

设置用户定义表的选项值。 sp_tableoption 可用于控制包含 varchar(max)、nvarchar(max)、varbinary(max)、xml、text、ntext、image 或大型用户定义类型列的表的行内行为。

重要提示
在 SQL Server 的未来版本中，将删除 text in row 功能。 若要存储大值数据，建议您使用 varchar(max)、nvarchar(max) 和 varbinary(max) 数据类型。

Transact-SQL 语法约定

语法

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

参数

• [ @TableNamePattern =] 'table'
  用户定义数据库表的限定名称或非限定名称。 如果提供了包含数据库名称的完全限定表名，则数据库名称必须为当前数据库的名称。 不能同时设置多个表的表选项。 table 的数据类型为 nvarchar(776)，无默认值。

• [ @OptionName = ] 'option_name'
  表选项名称。 option_name 的数据类型为 varchar(35)，默认值为 NULL。 option_name 可以是下列值之一。

  值	说明
  table lock on bulk load	禁用时（默认值），使用户定义表的大容量处理获得行锁。 启用时，使用户定义表的大容量处理获得大容量更新锁。
  insert row lock	不再支持。 SQL Server 的锁定策略为行锁定，并可能提升为页锁定或表锁定。 此选项对 SQL Server 的锁定行为没有影响，保留此选项只是为了与现有脚本和过程兼容。
  text in row	如果为 OFF 或 0（禁用，默认值），则不更改当前行为，且在行中不存在 BLOB。 如果指定此值，并且 @OptionValue 设置为 ON（启用）或者是 24 到 7000 之间的一个整数值，则新的 text、ntext 或 image 字符串会直接存储到数据行中。 当 BLOB 值已更新，所有现有 BLOB（二进制大型对象：text、ntext 或 image 数据）都将更改为 text in row 格式。 有关详细信息，请参阅“备注”。
  large value types out of row	1 = varchar(max)、nvarchar(max)、varbinary(max)、xml 和大型用户定义类型 (UDT) 列（表中）都存储在行外，带有一个指向根的 16 字节指针。 0 = varchar(max)、nvarchar(max)、varbinary(max)、xml 和大型 UDT 值直接存储在数据行中，上限为 8000 个字节并且只要记录中可以容纳该值。 如果记录中容纳不下该值，则指针存储在行内，其余内容存储在 LOB 存储空间内的行外。 0 是默认值。
  vardecimal storage format	为 TRUE、ON 或 1 时，将为 vardecimal 存储格式启用指定的表。 为 FALSE、OFF 或 0 时，将不为 vardecimal 存储格式启用此表。 只有使用 sp_db_vardecimal_storage_format 为 vardecimal 存储格式启用数据库后，才能启用 vardecimal 存储格式。 在 SQL Server 2008 和更高版本中，已不推荐使用 vardecimal 存储格式， 请改用 ROW 压缩。 有关详细信息，请参阅数据压缩。 0 是默认值。

• [ @OptionValue =] 'value'
  指示 option_name 是启用（TRUE、ON 或 1）还是禁用（FALSE、OFF 或 0）。 value 的数据类型为 varchar(12)，无默认值。 value 不区分大小写。

  对于 text in row 选项，有效选项值是 0、ON、OFF，或从 24 到 7000 的整数。 在 value 为 ON 时，默认限制值为 256 个字节。

返回代码值

0（成功）或错误号（失败）

注释

sp_tableoption 仅可用于设置用户定义表的选项值。 若要显示表属性，请使用 OBJECTPROPERTY。

sp_tableoption 中的 text in row 选项只能对包含文本列的表启用或禁用。 如果表不含文本列，SQL Server 将引发错误。

如果启用了 text in row 选项，则用户可使用 @OptionValue 参数为 BLOB 指定可在行中存储的最大大小。 默认值为 256 字节，但是值可以介于 24 到 7000 个字节之间。

在满足以下条件时，数据行中可以存储 text、ntext 或 image 字符串：

• 启用了 text in row。

• 字符串的长度比 @OptionValue 中指定的限制值短

• 数据行中有足够的可用空间。

数据行中存储 BLOB 字符串时，读取和写入 text、ntext 或 image 字符串可以与读取或写入字符和二进制字符串一样快。 SQL Server 不必访问不同的页就可读取或写入 BLOB 字符串。

如果 text、ntext 或 image 字符串比所指定的限制或行中的可用空间大，则行中将存储指针。 尽管如此，在行中存储 BLOB 字符串的条件依然适用：数据行中必须有足够的空间来存放指针。

存储在表行中的 BLOB 字符串和指针被视为类似于可变长度字符串。 SQL Server 仅使用存储字符串或指针所需的字节数。

如果先启用了 text in row，则不会立即转换现有的 BLOB 字符串。 仅当字符串被更新时才将其转换。 同样，如果增加了 text in row 选项限制值，则在更新数据行中已有的 text、ntext 或 image 字符串之前，不会对这些字符串进行转换以遵从新限制。

注意
禁用 text in row 选项或减少该选项的限制值需要转换所有的 BLOB；因此，此过程可能需要较长的时间，具体时间则取决于必须转换的 BLOB 字符串数。 在转换过程中，表将被锁定。

表变量（包括返回表变量的函数）的 text in row 选项会自动启用，并将内联限制值默认为 256 个字节。 此选项不可更改。

text in row 选项支持 TEXTPTR、WRITETEXT、UPDATETEXT 和 READTEXT 函数。 用户可以使用 SUBSTRING() 函数读取部分 BLOB，但是必须记住，各个行内文本指针之间具有不同的持续时间和数量限制。

若要将表从 vardecimal 存储格式改回为正常的十进制存储格式，数据库必须处于 SIMPLE 恢复模式。 更改恢复模式将断开用于备份目的的日志链，因此在从表中删除 vardecimal 存储格式后，应创建完整数据库备份。

如果将现有 LOB 数据类型列（text、ntext 或 image）转换为小、中、大值类型（varchar(max)、nvarchar(max) 或 varbinary(max)），并且大多数语句不引用环境中的大值类型列，则考虑将 large_value_types_out_of_row 更改为 1 以获得最优性能。 当更改 large_value_types_out_of_row 选项值时，现有的 varchar(max)、nvarchar(max)、varbinary(max) 和 xml 值不会立即转换。 字符串的存储会随着字符串的更新而变化。 任何插入表中的新值都将根据有效的表选项存储。 如需直接结果，可以复制数据，然后在更改 large_value_types_out_of_row 设置后重新填充表，或更新小、中、大值类型列自身，以便在表选项生效时更改字符串的存储。 考虑在更新或重新填充后在表中重新生成索引以缩减该表。

权限

执行 sp_tableoption 要求对表拥有 ALTER 权限。

示例

A.将 xml 数据存储在行外

以下示例指定了将 HumanResources.JobCandidate 表中的 xml 数据存储在行外。

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

B.在表中启用 vardecimal 存储格式

以下示例修改了 Production.WorkOrderRouting 表从而以 vardecimal storage format 存储 decimal 数据类型。

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';

请参阅

参考

sys.tables (Transact-SQL)

OBJECTPROPERTY (Transact-SQL)

系统存储过程 (Transact-SQL)

数据库引擎存储过程 (Transact-SQL)