Size of varbinary field in SQL server 2005

Question

Hi!

I am trying to determine the size in bytes of the contents in a varbinary(max) field in sql server 2005, using sql. As I doubt there is native support for this, could it be done using CLR integration? Any ideas would be greatly appreciated.

Answer 1

Actually, you can do this in T-SQL!

DATALENGTH() will work on varbinary(max) fields.

Answer 2

Hi,

The VARBINARY(MAX) field allocates variable length data up to just under 2GB in size.

You can use DATALENGTH() function to determine the length of the column content.

For example:

SELECT DATALENGTH(CompanyName), CompanyName
FROM Customers

Hope this helps.

Cheers,John