Storing a 30KB BLOB in SQL Server 2005

Question

My data is 30KB on disk (Serialized object) was size should the binary field in t-sql be?

Is the brackets bit bytes ?

... so is binary(30000) .... 30KB?

Thanks

Answer 1

You need to use the varbinary(max) data type; the maximum allowed size for binary is 8,000 bytes. Per the MSDN page on binary and varbinary:

varbinary [ ( n | max) ]

Variable-length binary data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of the data entered + 2 bytes. The data that is entered can be 0 bytes in length.

Answer 2

The number after binary() is the number of bytes, see MSDN:

binary [ ( n ) ]

Fixed-length binary data of n bytes. n must be a value from 1 through 8,000. Storage size is n+4 bytes.

Whether 30kb is 30000 or 30720 bytes depends on which binary prefix system your file system is using.