作者： 戴羽 出处： IT专家网 

　　本文解释了SQL Server用于访问和存储数据的BLOB数据类型，解析了SQL Server BLOB数据存储，访问的方法以及如果降低你的SQL Server BLOB存储的每个字节的总成本。

　　BLOB数据是一个每条记录都拥有大量数据的字段。这个数据可以是文本格式的或二进制格式的。当在SQL Server中使用BLOB数据时，每条记录的数据量一般都远远超过了一个单独记录的8K的限制。

　　有多种数据类型用于存储BLOB数据，并且它们随着时间已有所改变。可用于BLOB存储的数据类型很多年都没有变，但是在SQL Server 2005中，微软推出了许多新的数据类型。在SQL Server 2008中，微软甚至计划推出更多的数据类型，不过到目前为止，他们还没有在CTP版本中推出这些数据类型。

　　在SQL Server 2000和更早的版本中，我们被限制于TEXT、NTEXT和IMAGE数据类型。TEXT数据类型是用于存储非Unicode的BLOB数据，而NTEXT数据类型是用于存储Unicode 的BLOB数据。IMAGE数据类型是用于存储二进制BLOB数据的。你可以使用IMAGE数据类型来存储任何二进制数据，例如图片、office文档、压缩数据等等。因为数据是二进制格式的，所以它可以是Unicode的也可以是非Unicode的。

　　SQL Server 2005数据类型是基于VARCHAR、NVARCHAR、VARBINARY和XML的。在使用这些数据类型时，一般你会指定要存储的数据的最大规模。为了在这些字段里存储BLOB数据，你要指定字段的最大规模。这些数据类型允许你在每条记录里最大存储2GB的数据(对于字符和二进制数据是2^31位，对于Unicode数据是2^30)。微软计划在未来的微软SQL Server版本中删除TEXT、NTEXT和IMAGE数据类型，它们的使用将会被转换到新的VARCHAR(MAX)、NVARCHAR(MAX)、VARBINARY(MAX)和XML数据类型上。


　　CREATE TABLE BlobData 
　　(ID int PRIMARY KEY, 
　　FirstName nvarchar(30), 
　　LastName nvarchar(30), 
　　EmailAddress varchar(255), 
　　[Resume] nvarchar(MAX)) 


　　在SQL Server 2008中，传说我们会得到一个叫做FILESTREAM的新的数据类型，它可以将二进制数据例如office文档存储到和普通的数据库文件不同的地方。通过将这个数据存储到和普通的MDF和NDF数据文件不同的地方，我们可以将它存储到单独的硬盘上来将访问BLOB数据对数据库中其余内容造成的性能影响降到最低。

　　SQL Server BLOB数据怎么存储和访问?

　　在使用TEXT、NTEXT和IMAGE数据类型时，数据是不和普通数据存储在一起的。一个被称作指针的很小的二进制值，和普通数据存储在一起。这个二进制值指向数据文件里的数据实际存储的位置。

　　当存储VARCHAR(MAX)、NVARCHAR(MAX)、VARBINARY(MAX)和XML数据类型的数据时，如果数据小于一行数据8K的最大限制，那么这个数据被存储到这个页面内。但是如果这个数据比一行数据8K的最大限制大的话，它会以和存储遗留数据类型一样的存储方式来存储，并且它会通过一个二进制指针的值被访问。

　　注意：这个二进制值和位置都是在后台处理的，对于用户是不可见的。

　　为了以TEXT、NTEXT和IMAGE数据类型读取数据，需要使用READTEXT命令。这个命令接受一个列名还有一个16位的二进制指针，要读取的第一个字符和要读取的字符数目。它使用TEXTPTR函数来获得正确的二进制指针，这个二进制指针是用于定位物理文件中的数据的：


　　DECLARE @ptr varbinary(16); 
　　SELECT @ptr = TEXTPTR(LongText) 
　　FROM TextDataExample 
　　where TextID = 1 
　　READTEXT TextDataExample.LongText @ptr 1 50; 


　　与READTEXT命令类似的，还有一个WRITETEXT命令用于将数据插入到记录中去。和READTEXT命令一样，WRITETEXT命令使用TEXTPTR函数来定位物理文件中的数据：


　　DECLARE @ptr varbinary(16); 
　　SELECT @ptr = TEXTPTR(LongText) 
　　FROM TextDataExample 
　　where TextID = 1 
　　READTEXT TextDataExample.LongText @ptr 'Sample data'; 


　　这些新的数据类型，不像他们之前的那些数据类型，它们可以不使用READTEXT 和WRITETEXT命令就可以很容易的被访问到。这些新的数据类型能够这么快就这么流行的一部分原因是因为要使用他们不需要额外的命令。你可以用我们平常操纵数据所使用的普通的SELECT、INSERT和UPDATE DML来使用这些数据类型。不幸的是，因为这个FILESTREAM数据类型还没有发布，没有关于怎样使用它的示例代码可以公布。

　　随着分层存储而出现的成本效率

　　使用一个叫做分层存储的存储设计是一个降低你的SQL Server BLOB存储的每个字节的总成本的简单方法。有了分层存储技术，高访问的数据被放置在更快，更贵的存储器里。不是经常访问的数据放在慢一些、较便宜的存储器上。通常情况下，BLOB数据被频繁的访问，而由于存储在BLOB数据列中的信息数量，使用分层存储通常是成本最有效的存储方法。当创建一个较便宜的存储层时，更大的、较慢的硬盘被用来创建第二个RAID数组，而这个RAID数组是用来存放第二个文件组的，该文件组存储BLOB数据。

　　分层存储是通过告诉SQL Server在一个独立于行数据的文件组里存储BLOB数据来使用的。这个指派发生在CREATE TABLE命令使用TEXTIMAGE_ON参数创建了这个表的时候。一个SQL Server表里的所有BLOB数据存储在一个单独的文件组里。如果你想在两个文件间分离BLOB数据，你必须同时将你的表分割为两个表，并通过TEXTIMAGE_ON参数为每一个表指定一个不同的组：


　　CREATE TABLE TextDataExample 
　　(TextID int IDENTITY(1,1), 
　　TextDescription varchar(25), 
　　LongText TEXT) 
　　ON [PRIMARY] TEXTIMAGE_ON [TextDataFileGroup] 

　　不幸的是，一旦表被创建了就没有简单的方法来改变包含BLOB数据的文件组。移动BLOB数据到另一个文件组的唯一方法是创建一个新的表，将数据复制到新的表里，删除旧的表，然后将新的表重命名为正确的名称。

　　假设你有一个用这个定义创建的表：


　　CREATE TABLE TextDataExample 
　　(TextID int IDENTITY(1,1) PRIMARY KEY, 
　　TextDescription varchar(25), 
　　LongText TEXT) 
　　ON [PRIMARY] 


　　并且你想转移数据到用这个定义创建的表上：


　　CREATE TABLE TextDataExample 
　　(TextID int IDENTITY(1,1) PRIMARY KEY, 
　　TextDescription varchar(25), 
　　LongText TEXT) 
　　ON [PRIMARY] TEXTIMAGE_ON [TextDataFileGroup] 


　　那你要用一个与这个类似的脚本：


　　CREATE TABLE TextDataExample_Temp 
　　(TextID int IDENTITY(1,1) PRIMARY KEY, 
　　TextDescription varchar(25), 
　　LongText TEXT) 
　　ON [PRIMARY] TEXTIMAGE_ON [TextDataFileGroup] 
　　SET IDENTITY_INSERT TextDataExample_Temp ON 
　　INSERT INTO TextDataExample_Temp 
　　(TextID, TextDescription, LongText) 
　　SELECT TextID, TextDescription, LongText 
　　FROM TextDataExample WITH (TABLOCK) 
　　SET IDENTITY_INSERT TextDataExample_Temp OFF 
　　DROP TABLE TextDataExample 
　　exec sp_rename 'TextDataExample_Temp', 
　　'TextDataExample', 'object' 


　　在这个过程中要确保在删除旧表以后在新的表上重新创建所有需要的索引。这些索引应该在旧表被删除之后创建，以便万一任何脚本或存储过程要求唯一的索引名称才是有效的，那这样相同的索引名称就可以使用了。

　　既然SQL Server支持BLOB数据，你在存储BLOB数据到SQL Server中时，一定要谨慎。存储大量BLOB数据到你的数据库中会影响你的数据库性能。虽然有一些方法可以在性能方面给你些帮助，但是它通常最终导致将BLOB数据移出SQL Server数据库存入文件系统。虽然这时把BLOB数据存放在数据库里看起来是一个简单的解决方案，但它会导致后来随着系统发展而来的性能问题。这个文件系统对于BLOB数据来说通常是最好的存放地址，但是如果你选择存放到数据库中，一定要确保小心使用。