关于text in row

 

默认情况下，text、ntext、image 字符串存储在数据行外的大型（最大 2GB）字符串或二进制字符串，而数据行存储的是 16 字节的文本指针，该指针指向一个树的根结点，而这个树存储的是实际字符串或二进制字符串所在页的指针。

我们知道 SQL Server 中页的大小为 8KB，除去相关占用，一页的数据大小最大还不足 8KB，由于一条记录（一行数据）只能存储在一页中，所以一条记录的最大大小不可能超过 8KB，而 8KB 对于很多记录来说是不够的，比如新闻系统，于是就产生了 text、ntext、image 字段，将实际的字符串存储在行外部，而行内部只存储相关的指针，这就是为什么 text、ntext、image 类型的字段，一般不能用字符串函数的原因了。

说了这么多，我们也该引入 text in row 了，一个新闻系统，有的文章很长，有的文章很短，只有几十个字，完全可以存储在行中，而不用存储在行外部，text in row 就是在这种情况下提出的。

text in row 可以指定多少字节以下的字符串存在在行中，如果 text、ntext 或 image字符串长度比 text in row 指定的长度短，且数据行中有足够的可用空间容纳字符串，那么字符串存在在行内部，而不是存在在行外部。

存储在数据行中的字符串，读写速度快，且可像 varchar 一样使用某些字符串函数。

如果 text、ntext 或 image字符串长度比 text in row 指定的长度长，或数据行中没有足够的空间容纳字符串，那么数据行存储指针集。这与 text in row 关闭时存储的指针有区别，text in row 关闭时存储的是某个树的根结点指针，而这里存储的是某个树根结点下面（树内部）的指针，这样由于不需要先访问根结点，处理速度也要稍显快些。

不过行中最多可容纳 5 个内部指针，需要 72 个字节的空间，如果连 72 个字节的空间都没有，并且又需要 5 个指针，SQL Server 就会另外分配一个 8KB 的页来容纳它们，所以 text in row 的值不应小于 72 字节，但也不应过高，如果此值过高，那么一个数据页可能容纳不了几条记录，就造成过多的页访问，速度反而变慢。

语法，前面说了 text in row 的相关知识，现在来讲讲语法。

在查询分析器中运行：

sp_tableoption N'表名', 'text in row', 'ON'

打开 text in row 选项，ON 也可以换成 [27, 7000] 之间的整数，如：sp_tableoption N'表名', 'text in row', '24'。

但根据上一节的分析，最小值最好是 72，那么最优值是多少呢？对于大多数情况下来说，最优值是 256，如果我们不是用的数字，用的是 ON，也就等价于指定的 256。

sp_tableoption N'表名', 'text in row', 'OFF'

关闭 text in row 选项。

---

当第一次启用 text in row 选项时，现有的字符串不会立即转换成行内字符串，仅当随后有更新字符串时，这些字符串才转换成行内字符串。

而关闭 text in row 可能比较耗时，所有行内 text、ntext 和 image 字符串都转换成常规的 text、ntext 和 image 字符串，且转换期间表被锁定。

 

 

========================以下为 Transact-SQL 参考  文档=====================================

Transact-SQL 参考

sp_tableoption

为用户定义表设置选项值。sp_tableoption 可以用来在具有 text、ntext 或 image 列的表上启用 text in row 功能。

语法

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 可以有下列值。

 

值	描述
pintable	当禁用时（默认值），它将表标记为不再驻留内存。启用时，将表标记为驻留内存。
table lock on bulk load	禁用时（默认值），用户定义表的大容量处理获得行锁。启用时，用户定义表的大容量处理获得大容量更新锁。
insert row lock	Microsoft® SQL Server™ 2000 中不支持该值。 对于 SQL Server 6.5 版而言，在指定表上启用或禁用插入行锁定 (IRL) 操作。在 SQL Server 7.0 中，行级锁定默认为启用。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 格式。有关更多信息，请参见注释部分。

 

[ , [ @OptionValue = ] 'value' ]

表示是启用 option_name（true、on 或 1）还是禁用 option_name（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（二进制大对象：text、ntext 或 image 数据）的最大值。默认设置是 256 字节，但是行中的值可以在 24 到 7000 字节范围内变化。

如果应用下列条件，则将 text、ntext 或 image 字符串存储在数据行中：

1. 启用 text in row。
   
   
2. 字符串的长度比 @OptionValue 所指定的限制短
   
   
3. 数据行中有足够的可用空间。

当 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 选项，并具备 256 字节的 inline limit 默认值。这一选项不可更改。

text in row 支持 TEXTPTR、WRITETEXT、UPDATETEXT 和 READTEXT 函数。用户可以使用 SUBSTRING() 函数读取 BLOB 的部分，但是必须记住，行内文本指针与其它文本指针相比有不同的有效期和个数限制。有关更多信息，请参见管理 ntext、text 和 image 数据。

权限

只有 sysadmin 固定服务器角色的成员可以修改表选项 pintable。

sysadmin 固定服务器角色成员，db_owner 和 db_ddladmin 固定数据库角色成员以及表所有者，都可以修改任何用户定义表的 table lock on bulk load 和 text in row 选项。其他用户只能修改自己拥有的表的选项。

示例

A. 启用 Northwind 数据库中表 'orders' 的 'text in row' 选项。

EXEC sp_tableoption 'orders', 'text in row', 'ON'

B. 启用 Northwind 数据库中表 'orders' 的 'text in row' 选项，并将 inline limit 设置为 1000。

EXEC sp_tableoption 'orders', 'text in row', '1000'

C. 启用 Northwind 数据库中表 'orders' 的 'text in row' 选项，并将 inline limit 设置为 23，这已超出允许范围。

sp_tableoption 'orders', 'text in row', '23'

会产生错误信息，提示参数超出范围。

D. 禁用 Northwind 数据库中表 'orders' 的 'text in row' 选项。

EXEC sp_tableoption 'orders', 'text in row', 'off'

-或-

EXEC sp_tableoption 'orders', 'text in row', '0'

请参见

 

DBCC PINTABLE

DBCC UNPINTABLE

OBJECTPROPERTY

系统存储过程