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?

Improve this question
3
  • 2
    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
    Commented Oct 13, 2016 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
    Commented Oct 13, 2016 at 21:51
  • I think this would be good information for developers of SQL Server JDBC drivers:) The drivers truncate data > 8000 bytes.
    – user4321
    Commented Oct 13, 2016 at 21:54

1 Answer 1

Reset to default
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.

Improve this answer
1
  • 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
    Commented Oct 14, 2016 at 5:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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