Firebird database BLOB type with 20,000 bytes, which SQL Server data type to store blob?

Question

Migrating data from a Firebird database to SQL Server. There is a BLOB Binary field which has values around 20,000 bytes which was measured using OCTET_LENGTH function.

Which SQL Server data type will house more than 8000 bytes?

Answer 1

The closest equivalent to a Firebird BLOB SUB_TYPE BINARY (or BLOB SUB_TYPE 0 or BLOB without explicit subtype), is a VARBINARY(MAX). See binary and varbinary (Transact-SQL):

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. The ANSI SQL synonym for varbinary is binary varying.

There are some differences in how values of these types are accessed (depending on the API used). In Firebird BLOB values are stored off-row and are accessed separately (though some APIs will hide this from you), while - IIRC - in SQL Server, VARBINARY values are stored on-row and APIs will allow you to access the value directly.