CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sql
CSharpeANdJavaGuy123
CSharpeANdJavaGuy123

Reputation: 769

Storing a 30KB BLOB in SQL Server 2005

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

Upvotes: 1

Views: 168

Answers (2)

Andomar
Andomar

Reputation: 238086

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.

Upvotes: 0

James McNellis
James McNellis

Reputation: 355059

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.

Upvotes: 3

Related Questions