sp_tableoption (Transact-SQL)
适用于： 
SQL Server Azure SQL 数据库 Azure SQL 托管实例
设置用户定义表的选项值。 sp_tableoption可用于控制具有 varchar（max）、nvarchar（max）、varbinary（max）、xml、text、ntext、image 或大型用户定义类型列的表的行内行为。
重要
将在 SQL Server 的未来版本中删除行中的文本功能。 若要存储大型值数据，建议使用 varchar（max）、 nvarchar（max） 和 varbinary（max） 数据类型。
语法
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。如果指定和@OptionValue ON（已启用）或整数值，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';