SQL Server 2008 R2 Varbinary 最大大小 [英] SQL Server 2008 R2 Varbinary Max Size

问题描述

我可以在 SQL Server 2008 R2 中使用 varbinary(max) 插入的最大文件大小是多少?我试图将列中的最大值更改为超过 8,000 字节，但它不会让我，所以我猜最大值是 8,000 字节，但来自 MSDN 上的这篇文章，它说最大存储大小为 2^31-1 字节:

What is the max size of a file that I can insert using varbinary(max) in SQL Server 2008 R2? I tried to change the max value in the column to more than 8,000 bytes but it won't let me, so I'm guessing the max is 8,000 bytes, but from this article on MSDN, it says that the max storage size is 2^31-1 bytes:

varbinary [ ( n | ma​​x) ]

varbinary [ ( n | max) ]

可变长度的二进制数据.n 可以是 1 到 8,000 之间的值.ma​​x 表示最大存储大小为 2^31-1 字节.存储大小为输入数据的实际长度+2 个字节.输入的数据长度可以为 0 个字节.varbinary 的 ANSI SQL 同义词是二进制变化.

Variable-length binary data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of the data entered + 2 bytes. The data that is entered can be 0 bytes in length. The ANSI SQL synonym for varbinary is binary varying.

那么如何在 varbinary 字段中存储更大的文件?我不考虑使用 FILESTREAM，因为我想保存的文件最大从 200kb 到 1mb，我使用的代码:

So how can i store larger files in a varbinary field? I'm not considering using a FILESTREAM since the files I want to save are from 200kb to 1mb max, The code I'm using:

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

我已经能够成功地对小于或等于 8000 字节的文件执行该代码.如果我尝试使用 8001 字节大小的文件，它将失败.我在表上的文件字段有一个名为文件"的字段.输入 varbinary(8000) ，正如我所说，我无法更改为更大的值.

I have been able to execute that code successfully to files less or equal than 8000 bytes. If i try with a file 8001 bytes size it will fail. My file field on the table has a field called "file" type varbinary(8000) which as I said, I can't change to a bigger value.

推荐答案

我无法重现此场景.我尝试了以下方法:

I cannot reproduce this scenario. I tried the following:

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，那么我猜以下任一情况是正确的:

If this is getting capped at 8K then I would guess that either one of the following is true:

1. 列实际上是VARBINARY(8000).

您正在 Management Studio 中选择数据，并分析其中显示的数据的长度.这被限制为最多 8192 个字符的文本结果，如果是这种情况，那么直接对列使用 DATALENGTH() 是一种更好的方法.

You are selecting the data in Management Studio, and analyzing the length of the data that is displayed there. This is limited to a max of 8192 characters in results to text, if this is the case, so using DATALENGTH() directly against the column is a much better approach.

这篇关于SQL Server 2008 R2 Varbinary 最大大小的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！