sp_tableoption (Transact-SQL)

  • 项目

适用于：SQL ServerAzure SQL 数据库Azure SQL 托管实例

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

重要

将在 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 存储格式启用指定表时TRUE或ON1启用指定表。 当、或未为 vardecimal 存储格式启用表时FALSE。0OFF 仅当使用 sp_db_vardecimal_storage_format 为 vardecimal 存储格式启用数据库时，才能启用 vardecimal 存储格式。 在 SQL Server 2008（10.0.x）及更高版本中， 已弃用 vardecimal 存储格式。 请改用 ROW 压缩。 有关详细信息，请参阅 数据压缩。 默认值为 0。

[ @OptionValue = ] 'OptionValue'

指定是否启用@OptionName（TRUE或ON）或1禁用（或OFF0） 。FALSE @OptionValue 为 varchar（12），没有默认值。 @OptionValue 不区分大小写。

对于行选项中的文本，有效选项值为 0，ON或OFF从中获取700024的整数。 当@OptionValue为ON时，限制默认为 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_tableoption ， ALTER 需要对表具有权限。

示例

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

相关内容