Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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 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 | max) ]

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.

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

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.

like image 408
Diego Ramos Avatar asked Aug 22 '12 23:08

Diego Ramos


People also ask

What is the maximum size of SQL Server 2008 R2 (Enterprise and standard)?

(2) What is the number of CPUs for SQL Server 2008 R2 (Enterprise and Standard Editions). (3) What is the maximum memory size for SQL Server 2008 R2 (Enterprise and Standard Editions). See below. Maximum database size can be 524,272 terabytes.

What is the size of varbinary in SQL Server?

the sizes of the column data entries vary considerably. varbinary(max) the column data entries exceed 8,000 bytes. Converting binary and varbinary data When converting data from a string data type to a binaryor varbinarydata type of unequal length, SQL Server pads or truncates the data on the right.

What is max storage size in SQL Server?

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.

What is varbinary argument in SQL?

Arguments. varbinary [ ( n | max) ] 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.


2 Answers

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.

like image 122
Aaron Bertrand Avatar answered Nov 26 '22 05:11

Aaron Bertrand


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.

like image 30
ShaunOReilly Avatar answered Nov 26 '22 06:11

ShaunOReilly