通过

  • 项目

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 2008 不支持。

    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 存储格式

    为 TRUE、ON 或 1 时，将为 vardecimal 存储格式启用指定的表。为 FALSE、OFF 或 0 时，将不为 vardecimal 存储格式启用此表。只有使用 sp_db_vardecimal_storage_format 为 vardecimal 存储格式启用数据库后，才能启用 vardecimal 存储格式。有关 vardecimal 存储格式的信息，请参阅将 Decimal 数据存储为可变长度类型。此选项需要 SQL Server 2005 Service Pack 2。只有 SQL Server Enterprise Edition、Developer Edition 和 Evaluation Edition 支持 Vardecimal 存储格式。在 SQL Server 2008 和更高版本中，对于所有的用户数据库都将启用 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，但是必须记住，各个行内文本指针之间具有不同的持续时间和数量限制。有关详细信息，请参阅管理 ntext、text 和 image 数据。

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

权限

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

示例

A. 将 xml 数据存储在行外

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

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

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

以下示例修改了 Production.WorkOrderRouting 表从而以 vardecimalstorage 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 'AdventureWorks', 'ON';
GO
USE AdventureWorks;
GO
EXEC sp_tableoption 'Production.WorkOrderRouting', 
   'vardecimal storage format', 'ON';