SQL Server 2008 R2 Varbinary最大大小

Question

在SQL Server 2008 R2中，使用varbinary(max)插入的文件最大大小是多少？我试图将列中的max值更改为超过8000个字节，但不允许我这样做，所以我猜最大值是8000个字节，但根据MSDN上的this article，最大存储大小为2 ^ 31-1个字节：

varbinary [ ( n | max) ]：可变长度二进制数据。n的值可以从1到8000。max表示最大存储大小为2 ^ 31-1个字节。存储大小是输入数据的实际长度+2个字节。输入的数据长度可以为0字节。varbinary的ANSI SQL同义词是binary varying。

那么如何在varbinary字段中存储更大的文件？我不考虑使用FILESTREAM，因为我想保存的文件最大为200kb至1mb。我正在使用的代码：

UPDATE [table]
SET file = ( SELECT * FROM OPENROWSET ( BULK 'C:\A directory\A file.ext', SINGLE BLOB)    alias) 
WHERE idRow = 1

我已经成功地执行了该代码，对于小于或等于8000字节的文件。如果我尝试使用大小为8001字节的文件，则会失败。我的表中的文件字段有一个名为“file”的字段，类型为varbinary(8000)，正如我所说，我无法将其更改为更大的值。

- Diego Ramos

---

你是怎么尝试的？你能展示一下你用来做这个的代码和创建表格结构的代码吗？另外，nvarbinary并不存在。 - Aaron Bertrand

抱歉，我是指varbinary(max)，我还添加了我使用的代码，非常感谢。 - Diego Ramos

你检查了兼容模式设置吗？ - Pablo Romeo

@PabloRomeo，即使您使用80兼容性（SQL Server 2000），也可以创建并正确填充varbinary(max)。兼容性级别设置会影响某些语法和解析，但不会影响数据类型或实际的DML操作。 - Aaron Bertrand

正如文档清楚地说明的那样：你要么指定 varbinary(n) 并给出一个具体的数字，最多可达 8000 - 超过这个范围是不可能的；要么你定义你的列或变量为 varbinary(max)，在这种情况下它可以容纳高达 2 GB 的数据。 - marc_s

Answer 1

我无法重现这种情况。我尝试了以下操作：

USE tempdb;
GO

CREATE TABLE dbo.blob(col VARBINARY(MAX));

INSERT dbo.blob(col) SELECT NULL;

UPDATE dbo.blob 
  SET col = (SELECT BulkColumn 
    FROM OPENROWSET( BULK 'C:\Folder\File.docx', SINGLE_BLOB) alias
  );

SELECT DATALENGTH(col) FROM dbo.blob;

结果：

--------
39578

如果这被限制在8K，那么我猜测以下情况之一可能是真的：

1. 该列实际上是VARBINARY（8000）。

2. 你在Management Studio中选择数据，并分析显示的数据长度。如果是这种情况，则结果文本最大为8192个字符，因此对该列直接使用DATALENGTH()是更好的方法。

- Aaron Bertrand

Answer 2

"SET TEXTSIZE" 指定由 SELECT 语句返回的 varchar(max)、nvarchar(max)、varbinary(max)、text、ntext 和图像数据的大小。

select @@TEXTSIZE

SQL Server原生客户端ODBC驱动程序和适用于SQL Server的SQL Server原生客户端OLE DB提供程序在连接时自动将TEXTSIZE设置为2147483647。 SET TEXTSIZE的最大设置为2 GB，以字节为单位指定。 将大小设置为0会将其重置为默认值（4 KB）。

正如提到的那样，对于大文件，应优先使用文件流。

- Ronen Ariely

Answer 3

我敢说，根据MS TechNet | FILESTREAM Overview中的以下内容，对于大于1 MB的文件，请使用文件流。

在SQL Server中，BLOB可以是标准的varbinary(max)数据，它将数据存储在表中，或者是FILESTREAM varbinary(max)对象，它将数据存储在文件系统中。数据的大小和使用情况决定了您应该使用数据库存储还是文件系统存储。如果满足以下条件，则应考虑使用FILESTREAM：

• 存储的对象平均大于1 MB。
• 快速读取访问很重要。
• 您正在开发使用中间层进行应用程序逻辑的应用程序。

对于较小的对象，在数据库中存储varbinary(max) BLOB通常提供更好的流式传输性能。

- ShaunOReilly