如何只选择巨大的二进制文件(文件)的一部分? [英] How do I select just a portion of huge binary (file)?

问题描述

我的问题是:我有可能在SQL Server 2008(> 1GB)的二进制(图像)字段中存储大文件.

My problem is this: I have the potential for huge files being stored in a binary (image) field on SQL Server 2008 (> 1GB).

如果我使用常规的select语句返回整个二进制文件，则查询将花费一分钟多的时间才能将结果返回到.NET程序，并且我的客户端应用程序超时.我正在寻找的是TSQL代码，它将限制返回数据的大小(可能为300mb)，从而允许我遍历其余块并防止超时.

If I return the entire binary using a regular select statement, the query takes more than a minute to return results to my .NET program and my client apps time out. What I'm looking for is TSQL code that will limit the size of the data returned (maybe 300mb), allowing me to iterate through the remaining chunks and prevent timeouts.

这必须在SQL查询中发生，而不是在返回数据后的处理中发生.

This has to happen in the SQL query, not in the processing after the data is returned.

我已经尝试了SubString，MS说它可以处理二进制数据，但是我得到的最大是8000个字节.我尝试的最后一件事看起来像这样:

I've tried SubString, which MS says works with binary data, but all I get back is 8000 bytes maximum. The last thing I tried looked like this:

select substring(Package,0,300000000) 'package', ID from rplPackage where ID=0
--where package is the huge binary stored in a image field

由于客户端应用程序的缘故，数据流也不是真正的选择.

Data Streaming isn't really an option either, because of the client apps.

有什么想法吗?

推荐答案

好的，我知道了.实现此目的的方法是使用substring函数，MS准确地说该函数适用于二进制文件.他们没有说的是，子字符串将仅返回8,000个字节，这就是我的想法.

OK, I figured it out. The way to do this is with the substring function, which MS accurately says works with binaries. What they don't say is that substring will return only 8,000 bytes, which is what threw me.

换句话说，如果Blob数据类型为image，并且您使用此代码:

In other words, if the blob data type is image and you use this:

 select substring(BlobField,0,100000000) 
 from TableWithHugeBlobField
 where ID = SomeIDValue

 --all you'll get is the first 8K bytes (use DataLength function to get the size)

但是，如果您声明变量varbinary(max)且blob字段数据类型为varbinary(max)-或对您有用的某些大小，则可以使用substring函数将部分二进制返回到变量中宣布.这样很好.就是这样:

However, if you declare a variable of varbinary(max) and the blob field data type is varbinary(max) - or some size that's useful to you - then use the substring function to bring back the partial binary into the variable you declared. This works just fine. Just like this:

 Declare @PartialImage varbinary(max) 
 select @PartialImage = substring(BlobField, 0, 100000000) --1GB
 from TableWithHugeBlobField
 where ID = SomeIDValue

 select DataLength(@PartialImage) -- should = 1GB

这个问题是在前面提出的，为什么要使用SQL来存储文件数据?这是一个有效的问题；想象一下，您必须将数据作为文件复制到数百个不同的客户端设备(例如iPhone)，每个包彼此之间是唯一的，因为不同的客户端有不同的需求，然后将这些文件包作为blob存储在数据库中很容易编程相比之下，以编程方式浏览文件夹以找到正确的程序包以流式传输到客户端是不可行的.

The question was posed earlier, why use SQL to store file data? It's a valid question; imagine you're having to replicate data as files to hundreds of different client devices (like iPhones), each package unique from the other because different clients have different needs, then storing the file packages as blobs on a database is a lot easier to program against than it would be to programmatically dig through folders to find the right package to stream out to the client.

这篇关于如何只选择巨大的二进制文件(文件)的一部分?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！