sp_tableoption 及使用 使用大值数据类型

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_pattern 的数据类型为 nvarchar(776)，无默认值。

[ @OptionName = ] 'option_name'

表选项名称。option_name 的数据类型为 varchar(35)，无默认值 NULL。option_name 可以是下列值之一。

值	说明
table lock on bulk load	禁用时（默认值），使用户定义表的大容量处理获得行锁。启用时，使用户定义表的大容量处理获得大容量更新锁。
insert row lock	SQL Server 2005 不支持。 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 列都存储在行外，用一个 16 个字节的指针指向根目录。 0 = varchar(max)、nvarchar(max)、varbinary(max) 和 xml 值直接存储在数据行中（最大限制为 8000 字节，并且只要记录中可以容纳该值）。如果记录中容纳不下该值，则指针存储在行内，其余内容存储在 LOB 存储空间内的行外。

[ @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 字节。

备注

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 字符串数。在转换过程中，表将被锁定。

table 变量（包括返回 table 变量的函数）将自动启用 text in row 选项，并将 inline limit 值默认为 256 字节。此选项不可更改。

text in row 支持 TEXTPTR、WRITETEXT、UPDATETEXT 和 READTEXT 函数。用户可以使用 SUBSTRING() 函数读取部分 BLOB，但是必须记住，各个行内文本指针之间具有不同的持续时间和数量限制。有关详细信息，请参阅 管理 ntext、text 和 image 数据。

返回代码值

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

权限

执行 sp_tableoption 要求具有表的 ALTER 权限。

示例

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

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

使用大值数据类型 

Microsoft SQL Server 2005 中引入了 max 说明符。此说明符增强了 varchar、nvarchar 和 varbinary 数据类型的存储能力。varchar(max)、nvarchar(max) 和 varbinary(max) 统称为大值数据类型。您可以使用大值数据类型来存储最大为 2^31-1 个字节的数据。

注意：

当 sp_tableoption 存储过程的 'large value types out of row' 选项设置为 OFF 时，大值类型的行内存储限制为 8000 个字节。当此选项设置为 ON 时，只在行内存储 16 字节的根。有关详细信息，请参阅 sp_tableoption (Transact-SQL)。

大值数据类型在行为上和与之对应的较小的数据类型 varchar、nvarchar 和 varbinary 相似。这种相似使 SQL Server 能够更高效地存储和检索大型字符、Unicode 和二进制数据。

有了大值数据类型，使用 SQL Server 的方式是使用早期版本的 SQL Server 中的 text、ntext 和 image 数据类型所不可能具有的。例如，在 SQL Server 2005 中，您可以定义能存储大量数据（最多可达 2^31 字节的字符、整数和 Unicode 数据）的变量。有关详细信息，请参阅 Transact-SQL 变量。

下表说明了大值数据类型和 SQL Server 早期版本中与之对应的数据类型之间的关系。

大值数据类型	早期版本中的 LOB
varchar(max)	text*
nvarchar(max)	ntext*
varbinary(max)	image

* SQL Server  6.5 版本的客户端不支持 ntext 数据类型，因此无法识别 nvarchar(max)。

重要事项：

请使用 varchar(max)、nvarchar(max) 和 varbinary(max) 数据类型，而不要使用 text、ntext 和 image 数据类型。

大值数据类型在行为上和与之对应的较小的数据类型 varchar(n)、nvarchar(n) 和 varbinary(n) 相同。下面介绍大值数据类型在某些特定情形下的使用：

·             游标
由于可以定义大值数据类型变量，便可以将 FETCH 返回的大值数据类型列中的数据赋给本地变量。有关详细信息，请参阅 FETCH (Transact-SQL)。
使用大值数据类型不影响游标的强制实施游标类型转换用法。

·             成块更新
UPDATE 语句现在支持 .WRITE( ) 子句对基础大值数据列进行部分更新。这类似于 SQL Server 早期版本中所支持的对 text、ntext、image 数据类型的文本指针操作、WRITETEXT 和 UPDATETEX。有关详细信息，请参阅 UPDATE (Transact-SQL)。

·             触发器
支持对插入的和删除的表中的大值数据类型列引用上使用 AFTER 触发器。有关详细信息，请参阅 CREATE TRIGGER (Transact-SQL)。

·             字符串函数
内置的可操作字符和二进制数据的字符串函数有所增强，可支持大值数据类型的参数。这些函数包括：

o                                    COL_LENGTH

o                                    CHARINDEX

o                                    PATINDEX

o                                    LEN

o                                    DATALENGTH

o                                    SUBSTRING

有关字符串函数的信息，请参阅字符串函数 (Transact-SQL)。