sp_tableoption (Transact-SQL)

适用于: SQL Server Azure SQL 数据库 Azure SQL 托管实例

设置用户定义表的选项值。 sp_tableoption可用于控制具有 varchar(max)、nvarchar(max)varbinary(maxxml、textntextimage 或大型用户定义类型列的表的行内行为。

重要

将在 SQL Server 的未来版本中删除行中的文本功能。 若要存储大型值数据,建议使用 varchar(max)nvarchar(max)varbinary(max) 数据类型。

Transact-SQL 语法约定

语法

sp_tableoption
    [ @TableNamePattern = ] N'TableNamePattern'
    , [ @OptionName = ] 'OptionName'
    , [ @OptionValue = ] 'OptionValue'
[ ; ]

参数

[ @TableNamePattern = ] N'TableNamePattern'

用户定义的数据库表的限定或非限定名称。 @TableNamePattern为 nvarchar(776),没有默认值。 如果提供了包含数据库名称的完全限定表名,则数据库名称必须为当前数据库的名称。 不能同时设置多个表的表选项。

[ @OptionName = ] 'OptionName'

表选项名称。 @OptionName为 varchar(35),可以是以下值之一。

说明
table lock on bulk load 禁用时(默认值),使用户定义表的大容量处理获得行锁。 启用时,使用户定义表的大容量处理获得大容量更新锁。
insert row lock 不再支持。

此选项对 SQL Server 的锁定行为没有影响,并且仅用于现有脚本和过程的兼容性。
text in row OFF 或(已禁用)时( 0 默认值),它不会更改当前行为,并且行中没有 BLOB。

如果指定和@OptionValueON(已启用)或整数值,700024则新文本ntext图像字符串将直接存储在数据行中。 更新 BLOB 值时,所有现有的 BLOB(二进制大型对象: 文本ntext图像)数据都更改为行格式的文本。 有关详细信息,请参阅“备注”。
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 值直接存储在数据行中,最多限制为 8,000 字节,只要该值可以容纳在记录中。 如果该值不适合记录,指针将存储在行中,其余值存储在 LOB 存储空间中。 默认值为 0。

大型用户定义类型(UDT)适用于:SQL Server 2008 (10.0.x) 及更高版本。

TEXTIMAGE_ON使用 CREATE TABLE 选项指定用于存储大型数据类型的位置。
vardecimal 存储格式 适用于:SQL Server 2008 (10.0.x) 及更高版本。

为 vardecimal 存储格式启用指定表时TRUEON1启用指定表。 当、或未为 vardecimal 存储格式启用表时FALSE0OFF 仅当使用 sp_db_vardecimal_storage_formatvardecimal 存储格式启用数据库时,才能启用 vardecimal 存储格式。 在 SQL Server 2008(10.0.x)及更高版本中, 已弃用 vardecimal 存储格式。 请改用 ROW 压缩。 有关详细信息,请参阅数据压缩。 默认值为 0。

[ @OptionValue = ] 'OptionValue'

指定是否启用@OptionNameTRUEON)或1禁用(或OFF0) 。FALSE @OptionValuevarchar(12),没有默认值。 @OptionValue 不区分大小写。

对于行选项中的文本,有效选项值为 0ONOFF从中获取700024的整数。 当@OptionValueON,限制默认为 256 字节。

返回代码值

0 (成功)或错误号(失败)。

注解

sp_tableoption 只能用于设置用户定义的表的选项值。 若要显示表属性,请使用 OBJECTPROPERTY 或查询 sys.tables

只能在包含文本列的表中启用或禁用行选项中的 sp_tableoption 文本。 如果表没有文本列,SQL Server 将引发错误。

启用行选项中的文本时, @OptionValue 参数允许用户指定要存储在 BLOB 行中的最大大小。 默认值为 256 字节,但是值可以介于 24 到 7000 个字节之间。

如果满足以下条件,文本ntext图像 字符串将存储在数据行中:

  • 已启用行中的文本。
  • 字符串的长度比@OptionValue中指定的限制短。
  • 数据行中有足够的空间。

当 BLOB 字符串存储在数据行中时,读取和写入文本ntext 或图像字符串的速度可以像读取或写入字符和二进制字符串一样快。 SQL Server 不必访问单独的页面来读取或写入 BLOB 字符串。

如果文本ntext 或图像字符串大于指定限制或行中的可用空间,则指针将改为存储在行中。 不过在行中存储 BLOB 字符串的条件依然适用:数据行中必须有足够的空间来存放指针。

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

首次启用行中的文本时,不会立即转换现有 BLOB 字符串。 仅当字符串被更新时才将其转换。 同样,当行选项限制中的文本增加时, 数据行中已有的文本ntext图像 字符串不会转换为遵守新限制,直到更新这些字符串为止。

注意

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

表变量(包括返回表变量的函数)的 text in row 选项会自动启用,并将内联限制值默认为 256 个字节。 无法更改此选项。

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

若要将表从 vardecimal 存储格式更改回普通十进制存储格式,数据库必须采用 SIMPLE 恢复模式。 更改恢复模式会中断日志链以进行备份,因此,在从表中删除 vardecimal 存储格式后,应创建完整数据库备份。

如果要将现有的 LOB 数据类型列(文本、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_tableoptionALTER 需要对表具有权限。

示例

A. 将 XML 数据存储出行

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

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

B. 对表启用 vardecimal 存储格式

以下示例将表修改Production.WorkOrderRouting为以 vardecimal 存储格式存储十进制数据类型

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