2

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?

  • 1
    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. – Justin Cave Oct 13 '16 at 21:42
  • 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 Oct 13 '16 at 21:51
  • I think this would be good information for developers of SQL Server JDBC drivers:) The drivers truncate data > 8000 bytes. – user4321 Oct 13 '16 at 21:54
7

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.

  • 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. – Concrete Gannet Oct 14 '16 at 5:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.