The binary datatypes store raw binary data, such as pictures, in a hexadecimal-like notation. Binary data begins with the characters “0x” and includes any combination of digits and the uppercase and lowercase letters A – F. The two digits following “0x” in binary and varbinary data indicate the type of number: “00” represents a positive number and “01” represents a negative number.
If the input value does not include “0x,” Adaptive Server assumes that the value is an ASCII value and converts it.
Adaptive Server manipulates the binary types in a platform-specific manner.
For true hexadecimal data, use the hextoint and inttohex functions.
See Chapter 16, “Using Transact-SQL Functions in Queries.”
Use the binary(n) and varbinary(n) datatypes to store data up to 255 bytes in length. Each byte of storage holds 2 binary digits. Specify the column length with n, or use the default length of 1 byte. If you enter a value longer than n, Adaptive Server truncates the entry to the specified length without warning or error.
Use the fixed-length binary type, binary(n), for data in which all entries are expected to have a similar length. Because entries in binary columns are zero-padded to the column length, they may require more storage space than those in varbinary columns, but they are accessed somewhat faster.
Use the variable-length binary type, varbinary(n), for data that is expected to vary greatly in length. Storage size is the actual size of the data values entered, not the column length. Trailing zeros are truncated.
When you create a binary column that allows nulls, Adaptive Server converts it to a varbinary column and uses the storage rules for that datatype.
You can search binary strings with the like keyword and operate on them with the string functions.
Because the exact form in which you enter a particular
value depends upon the hardware you are using, calculations involving
binary data may produce different results on different platforms.
