读取 text、ntext 或 image 列中的 text、ntext 或 image 值

1、先说明相关的类型、设置项：

 

·         ntext、text 和 image类型

用于存储大型非 Unicode 字符、Unicode 字符及二进制数据的固定长度和可变长度数据类型。Unicode 数据使用 UNICODE UCS-2 字符集。

ntext

可变长度 Unicode 数据的最大长度为 2³⁰ - 1 (1,073,741,823) 个字符。存储大小是所输入字符个数的两倍（以字节为单位）。ntext 在 SQL-92 中的同义词是 national text。

text

服务器代码页中的可变长度非 Unicode 数据的最大长度为 2³¹-1 (2,147,483,647) 个字符。当服务器代码页使用双字节字符时，存储量仍是 2,147,483,647 字节。存储大小可能小于 2,147,483,647 字节（取决于字符串）。

image

可变长度二进制数据介于 0 与 2³¹-1 (2,147,483,647) 字节之间。

·         max text repl size 选项

使用 max text repl size 选项可以设定在执行单个INSERT、UPDATE、WRITEEXT或者UPDATEEXT命令时可以增添到复制字段的 text 和 image 数据的大小（单位为字节）。

该设置立即生效（无需停止并重新启动服务器）。 

·         Text in Row

Microsoft® SQL Server™ 2000 支持在数据行中存储小型到中等的 text、ntext 和 image 值。该功能最适于这样的表：text、ntext 和 image 列中的数据通常读取或写入一个单元，并且大多数引用该表的语句都使用 text、ntext 和 image 数据。

除非指定 text in row 选项，否则 text、ntext 或 image 字符串为存储在数据行外的大型（最大 2GB）字符串或二进制字符串。数据行只包括一个 16 字节的文本指针，该指针指向一个树的根节点，该树由映射存储串片段的页的内部指针构成。

如果指定的是 on 而不是一个特定的限制，则此限制的默认值为 256 字节。该默认值使您得以从 text in row 选项中获得最多的性能收益。尽管在通常情况下，不应将此值设为低于 72，但也不应把此值设得过高，特别是对那些大多数语句都不引用 text、ntext 和 image 列的表或者具有多个 text、ntext 和 image 列的表。如果设置了较大的 text in row 限制，且行本身存储了许多字符串，则可以显著减少存储在每页上的数据行数。如果大多数引用表的语句不访问 text、ntext 或 image 列，则减少页中的行数会增加处理查询时必须读取的页数。减少每页上的行数会增加索引及页的大小，优化器找不到可用的索引时可能需要扫描这些索引和页。text in row 限制的默认值 256 足以确保小型字符串和根文本指针存储在行中，同时不会使每页上的行减少得太多以至影响性能。

也可以使用 sp_tableoption 关闭此选项，这可以通过指定 off 或 0 选项值来实现：

sp_tableoption N'MyTable', 'text in row', 'OFF'

2、text in row 设置为 OFF 时的 text、ntext 和 image 数据

如果 sp_tableoption 的 text in row 选项设置为 OFF，数据少于 32 KB 与多于 32 KB 时用于存储 text、ntext 或 image 数据的 B 树结构略有不同。

如果数据量少于 32 KB，数据行的 16 字节文本指针就指向 84 字节的文本根结构。这形成 B 树结构的根节点。根节点指向 text、ntext 或 image 数据块。

虽然 text、ntext 和 image 列的数据在 B 树内按逻辑排列，但根节点和各数据块却分布在整个表的 text、ntext 和 image 页链内。根节点和各个数据块放置在任何可用的空间内。每个数据块的大小由应用程序写定的大小决定。小数据块组合在一起填充一页。如果数据量少于 64 字节，则全都存储在根结构中。

例如，如果应用程序先写入一个 1 KB 的 image 数据，则该数据作为行的第一个 1 KB 的 image 数据块存储。如果应用程序随后写入一个 12 KB 的 image 数据，则将 7 KB 和刚才的 1 KB 的块组合在一起，这样第一个块就是 8 KB。剩下的 5 KB 形成第二个 image 数据块。（每个 text、ntext 或 image 页的实际容量是 8080 字节的数据。）

因为 text、ntext 或 image 数据块以及根结构都可以在相同的 text、ntext 或 image 页上共享空间，所以在 SQL Server 7.0 中，text、ntext 或 image 数据使用的空间比以前的 SQL Server 版本少。例如，如果在 text 列中插入 20 行，每行有 200 字节的数据，那么这些数据和所有根结构都可以放在同一个 8 KB 页中。

如果 text、ntext 或 image 列的一个事件的数据量超过 32 KB，则 SQL Server 开始在数据块和根节点之间生成中间节点。

在整个 text、ntext 或 image 页内，用前面介绍的相同方式交叉存取根结构和数据块。然而，中间节点存储于不在 text、ntext 或 image 列事件之间共享的页内。存储中间节点的页仅包含一个数据行内的一个 ntext、text 或 image 数据值的中间节点。

3、说说大家关心的数据存储与读取的基本函数

 

a、UPDATETEXT

更新现有 text、ntext 或 image 字段。使用 UPDATETEXT 在适当的位置更改 text、ntext 或 image 列的一部分。使用 WRITETEXT 来更新和替换整个 text、ntext 或 image 字段。

语法

UPDATETEXT { table_name . dest_column_name dest_text_ptr }
    { NULL | insert_offset }
    { NULL | delete_length }
     [ WITH LOG ]
     [ inserted_data
         | { table_name . src_column_name src_text_ptr } ]

b、WRITETEXT

允许对现有的 text、ntext 或 image 列进行无日志记录的交互式更新。该语句将彻底重写受其影响的列中的任何现有数据。WRITETEXT 语句不能用在视图中的 text、ntext 和 image 列上。

语法

WRITETEXT { table . column text_ptr }
    [ WITH LOG ] { data }

c、READTEXT

读取 text、ntext 或 image 列中的 text、ntext 或 image 值，从指定的偏移量开始读取指定的字节数。

语法

READTEXT { table.column text_ptr offset size } [ HOLDLOCK ]

d、TEXTPTR

以 varbinary 格式返回对应于 text、ntext 或 image 列的文本指针值。检索到的文本指针值可用于 READTEXT、WRITETEXT 和 UPDATETEXT 语句。

语法

TEXTPTR ( column )

e、 WRITETEXT与UPDATETEXT的差别

可以简单理解为updtetext是对数据更新及追加、删除部分，而writetext是无日志的交互更新，多用于初始操作。

4、对值的操作

要操作值那得读取，一般情况下，如果text in row是on时，可以用convert,cast转成Nvarchar类型进行相应的操作，比如：substring,left,right,replace.....

如果是text in row = OFF,显然我们得用readtext

示例：

 

USE pubs
GO
DECLARE @val varbinary(16)
SELECT @val = TEXTPTR(pr_info) 
FROM pub_info
WHERE pub_id = '0736'
READTEXT pub_info.pr_info @val 4 10
GO

下面是结果集：

(1 row(s) affected)

pr_info     

------------------------------------------------------------------------
 is sample

 

下面我们如何把上面的结果集放到text变量中呢，而且如果字节大于8000怎么办呢？

好，我查了相关帮助和文档，未发现好的办法，想到了用过程来实现，

请看下面的代码：

create proc pr_test 
@id int, --记录ID
@s int, --起位置
@len int --结束
as  
begin 
DECLARE @ptrval varbinary(16) 

SELECT @ptrval = TEXTPTR(col)  
   FROM ta  
where id = @id 

READTEXT ta.col  @ptrval @s @len 

end 
go 

 

调用示例：

 

create table ss(s text) 
go 
insert ss exec pr_test 1,1,22 
select * from ss

 

对这个ss.s列行相应的字符函数操作，也就是化解为多个小text进行操作。以上功能算是对READTEXT 功能的一个补充。