2

Possible Duplicate:
Size of varbinary field in SQL server 2005

In my SQL2005 database, I have a table that has a column varbinary(MAX).

How do I tell the size of the rows? Or as an alternative, tell the size of the varbinary field in each row?

Community
  • 1
  • 1
yamspog
  • 18,173
  • 17
  • 63
  • 95
  • 4
    See the similar question http://stackoverflow.com/questions/507785/size-of-varbinary-field-in-sql-server-2005 – Mike Schenk Oct 04 '10 at 18:15
  • Example with link in my answer here: http://stackoverflow.com/questions/3793022/how-to-come-to-limits-of-8060-bytes-per-row-and-8000-per-varchar-nvarchar-valu/3793265#3793265 – gbn Oct 04 '10 at 18:45
  • thanks for the links. i was obviously searching for the wrong keywords. just need one more vote to close the question. – yamspog Oct 04 '10 at 19:15

1 Answers1

4

There are two functions:

  • DATALENGTH returns the size of the data (bytes)
  • LEN returns the size of data (characters).

For a varbinary(max) column, you should use DATALENGTH:

SELECT DATALENGTH(column) FROM Table;

Both functions return the logical value of the data, that is after all compression algorithms have been applied and the column is completely uncompressed. The actual number of bytes occupied on-disk cannot be determined, because is influenced by the various storage options, most importantly by the compression options (row compression, page compression, unicode compression) an by the large value types out of row setting for the table.

Nick Craver
  • 623,446
  • 136
  • 1,297
  • 1,155
Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • 3
    So...is it possible to determine the size of the entire row, not just the one column (this was also kind of asked in the original question :) – rogerdpack Jul 17 '14 at 15:51