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?
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 forvarbinary
isbinary 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.