SQL Server 2005中对BLOB的支持(ntext,text and image)

前几天和一个朋友聊天的时候他说当存储非常超过8K数据的时候使用Text,ntext OR Image时，就像把这些东西扔进一个黑箱子里面，感觉这个比喻比较形象,当我们把数据存储在这几种数据类型中后，会有非常多的限制。

 

我们都知道当使用这三个数据类型时，如果我们需要对其进行操作时，就会发现非常麻烦，比如替换其中的某些字符，进行插入，更新时也比较麻烦。时隔5年，让我们来看一下在SQL Server 2005中是怎么解决这个问题的。

 

在SQL Server 2005中，我们扩展了varchar,nvarchar AND binary的存储容量至2G，所以我们可以使用Varchar(max),nvarchar(max) AND varbinary(max)来取代text,ntext AND image;由于是从varchar,nvarchar AND binary进行的扩展，所以我们可以使用在SQL Server 2000中对这几种数据类型可以使用的所有函数，比如replace,upper,lower,substring.......这样我们进行许多操作时就非常有用。如下：

 

 

--附件中的po.xml是下面例子中要用到的数据。

 

USE AdventureWorks;

GO

--确认一下我所使用文件的长度超过8K

DECLARE @x varchar(max)

SELECT @x=s FROM OPENROWSET(BULK N'd:/po.xml',single_blob) AS a(s)---PO.xml will posted in the followed post

SELECT datalength(@x)

--the result IS

12328

 

下面我们试着对这个数据类型进行一些函数操作：

DECLARE @x varchar(max)

SELECT @x=s FROM OPENROWSET(BULK N'd:/po.xml',single_blob) AS a(s)

SELECT substring(@x,1,20)

--the result IS

<?xml version="1.0"

 

下面我们看一下怎么对这一拥有这一数据类型的列进行update,INSERT 操作。

--INSERT

 

CREATE TABLE test

(PID int IDENTITY(1,1) PRIMARY KEY,

doc varchar(max)

)

 

INSERT test(doc)

SELECT 'test'

GO

INSERT test(doc)

SELECT * FROM OPENROWSET(BULK N'd:/po.xml',single_blob) AS a

GO

SELECT * FROM test

GO

--UPDATE

UPDATE test

SET doc=(SELECT * FROM OPENROWSET(BULK N'd:/po.xml',single_blob) AS a)

WHERE PID=1

GO

SELECT * FROM test

GO

 

--UPDATE

UPDATE test

SET doc=upper(substring(doc,1,100))

WHERE PID=1

GO

SELECT * FROM test WHERE PID=1

GO

--the result IS

<?XML VERSION="1.0" ENCODING="UTF-8"?>

<IXQ:POLIST XMLNS:IXQ="HTTP://WWW.IPEDO.COM/XQUERYEXAMPLE"

 

 

 

SELECT @x=s FROM OPENROWSET(BULK N'd:/po.xml',single_blob) AS a(s)

SELECT upper(@x)