Maximum real space in varbinary (max) in SQL Server

Question

I save files (of any type) in an SQL table using varbinary(max) , and find out that the maximum use of this data type is 8000, but what does 8000 mean?

The online documentation says it's 8000 bytes. Does this mean that the maximum size of the saved file is 8000/1024 = 7.8125 KB?

I am starting testing, and the maximum file size that I can save is 29.9 MB. If I select a larger file, I will get a SQLException.

String or binary data will be ignored. The application has been discontinued.

Answer 1

Deploy SQL Server 2012 (codename Denali) when it is released - it has a FileTable function :)

• varbinary(8000) limited to 8000 bytes - that's for sure!
• varbinary(max) limited to 2 gigabytes
• varbinary(max) FILESTREAM limited by your file system (FAT32 - 2 Gb, NTFS - 16 exabytes)

Answer 2

Taken from here:

http://msdn.microsoft.com/en-us/library/ms188362.aspx :

max indicates that the maximum storage size is 2³¹-1 bytes

which is 2,147,483,647 bytes. I'm not sure why it stops at 29.9 MB.

Answer 3

What version of SQL Server are you using?

Varbinary on MSDN for SQL Server 2008 explicitly states that VarBinary (MAX) is intended to be used when "column data records exceed 8000 bytes."

In addition, I would also take a look at the file stream capabilities in SQL Server 2008 if you are using this server.

Answer 4

I got a "String or binary data truncated" error when trying to save 5MB using varbinary (max) on SQL Server 2005. Increasing the auto-difference size for the database solved the problem. Spent me sorting it out, so I just thought I'd be sharing :)