The Definition of Binary Data Types in a SQL Server

Microsoft SQL Server supports seven distinct categories of data. Of these, binary strings allow for encoded data represented as binary objects.

Data types in the binary-strings category include:

• Bit variables store a single bit with a value of 0, 1 or NULL.
• Binary(n) variables store n bytes of fixed-size binary data. These fields may store a maximum of 8,000 bytes.
• Varbinary(n) variables store variable-length binary data of approximately n bytes. They may store a maximum of 8,000 bytes.
• Varbinary(max) variables store variable-length binary data of approximately n bytes. They may store a maximum of 2 GB and actually store the length of the data plus an additional two bytes.
• Image variables store up to 2 GB of data and are commonly used to store any type of data file (not just images).

The image type is scheduled for deprecation in a future release of SQL Server. Microsoft engineers recommend using varbinary (max) instead of image types for future development.

Appropriate Uses

Use bit columns when you need to store yes-or-no kinds of data as represented by zeroes and ones. Use binary columns when the size of the columns are relatively uniform. Use varbinary columns when the column size is expected to exceed 8K or may be subject to significant variability in size per record.

Conversions

T-SQL—the variant of SQL used in Microsoft SQL Server—right-pads data when you convert from any string type to a binary or varbinary type. Any other type conversion to a binary type yields a left-pad. This padding is effected through the use of hexadecimal zeroes.

Because of this conversion and the risk of truncation, if the post-conversion field isn't large enough, it's possible that converted fields could result in arithmetic errors without throwing an error message.