how to determine size of row in the database

Question

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?

Answer 1

There are two functions:

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

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

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.