how to determine size of row in the database

sql-server varbinary
yamspog picture yamspog · Oct 4, 2010 · Viewed 11.3k times · Source

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

Remus Rusanu picture Remus Rusanu · Oct 4, 2010

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.

Related questions

SQL Server converting varbinary to string

I want to do conversion in T-SQL from a varbinary type to string type Here is an example : First I got this varbinary 0x21232F297A57A5A743894A0E4A801FC3 And then I want to convert it to 21232…

Read More
Script to save varbinary data to disk

I have some varbinary data stored in a table in MS Sql Server 2005. Does anyone have SQL code that takes a query as input (lets say the query guarantees that a single column of varbinary is returned) and outputs the …

Read More
What data can be stored in varbinary data type of SQL Server?

I have a table in which the userpassword field have varbinary datatype, So I'm confused that in which form should I save the data into userpassword field because when I save varchar data it gave me error.

Read More