SQL Server 2008 R2 Varbinary Max Size

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

Solution 1:

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;

Results:

--------
39578

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

1. The column is actually VARBINARY(8000).

2. 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.

Solution 2:

I would dare to say, use file stream for files bigger than 1 MB based on the following from: MS TechNet | FILESTREAM Overview.

In SQL Server, BLOBs can be standard varbinary(max) data that stores the data in tables, or FILESTREAM varbinary(max) objects that store the data in the file system. The size and use of the data determines whether you should use database storage or file system storage. If the following conditions are true, you should consider using FILESTREAM:

• Objects that are being stored are, on average, larger than 1 MB.
• Fast read access is important.
• You are developing applications that use a middle tier for application logic.

For smaller objects, storing varbinary(max) BLOBs in the database often provides better streaming performance.

Solution 3:

"SET TEXTSIZE" Specifies the size of varchar(max), nvarchar(max), varbinary(max), text, ntext, and image data returned by a SELECT statement.

select @@TEXTSIZE

The SQL Server Native Client ODBC driver and SQL Server Native Client OLE DB Provider for SQL Server automatically set TEXTSIZE to 2147483647 when connecting. The maximum setting for SET TEXTSIZE is 2 gigabytes (GB), specified in bytes. A setting of 0 resets the size to the default (4 KB).

As mentioned, for big files you should prefer file stream.