SQL Server - How can varbinary(max) store > 8000 bytes?

Question

In SQL Server 2005 and 2008 R2, I'm calling into a stored procedure that has an out parameter defined as varbinary(max). The out parameter returns 10020 bytes according to DATALENGTH.

However, SQL Server errors if I try to define a varbinary with> 8000 bytes such as varbinary(10000). E.g.

The size (10000) given to the type 'varbinary' exceeds the maximum allowed for any data type (8000).

What is happening here? How can SQL Server return more bytes than allowed in the data type? Is SQL Server using some other data type behind the scenes to hold> 8000 bytes?

• Justin Cave: I'm not sure that I understand the question. You can define a varbinary(max) or a varbinary(8000) but you can't define a varbinary with a size between 8000 and max (2^31 - 1). A varbinary(max) can have a 10,000 byte value, a varbinary(8000) cannot.
• user4321: I was thinking that if SQL Server supported up to 2^31 - 1 that you would be able to specify the length to be any positive integer value up to that max. But from your comment, I see that SQL Server just prevents you from defining a varbinary(n) where 8000<n<2^31 -1. Thanks.
• user4321: I think this would be good information for developers of SQL Server JDBC drivers:) The drivers truncate data > 8000 bytes.

Answer 1

The maximum size you can specify is 8000. Anything larger needs to be specified as (MAX)

See https://msdn.microsoft.com/en-us/library/ms188362.aspx for details.

• Concrete Gannet: Thanks Max. varbinary(max) means you want the possibility of data extending across more than one page. Any size number you set is a size within one page.