Binary and VarBinary Data Types in SQL Server

Binary, Varbinary & Varbinary(max) are the binary string data types in SQL Server. These data types are used to store raw binary data up to a length of (32K – 1) bytes. The contents of image files (BMP, TIFF, GIF, or JPEG format files), word files, text files, etc. are examples of binary data.

Binary

Binary is a fixed width data type

The syntax for declaring Binary variable is binary(n), where n defines the size in bytes. Note that size is in bytes and not number of characters.

The Binary data type always uses the n bytes of storage irrespective of the size of the data

For Example, when we declare as binary(10), The column will occupy 10 bytes of storage. The value of n can be from 1 to 8000 bytes.

Varbinary

VarBinary is a variable width data type.

The syntax for declaring Binary variable is varbinary(n), where n defines the maximum size in bytes.

The varbinary data type uses actual length of the data entered + 2 bytes as the storage.

Varbinary(max)

Similar to varbinary(n), the max argument is used when the length of the binary data is expected to exceed the 8000 bytes.

IMPORTANT! ntexttext, and image data types will be removed in a future version of SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max)varchar(max), and varbinary(max) instead.

Binary Data Type Examples

Creating Tables with Binary Columns

Binary VarBinary and VarBinary max in SQL Server

Inserting Values into Binary Columns

Using Binary Local Variables

Converting to and from binary

Conversions between any data type and the binary data types are not guaranteed to be the same between different versions of SQL Server.

Binary to String

Conversion from binary to string is implicit. In the following example, we assign binary value @b to string without using any conversion functions.

String to Binary

But SQL Server does not do a implicit conversion from string to binary. The following code results in error Implicit conversion from data type varchar to varbinary(max) is not allowed. Use the CONVERT function to run this query.

We need to use the cast or convert function and explicitly convert the string to the number

When we convert string data to binary of larger length, SQL Server pads the data with zero on the right.

When we convert string data to binary of smaller length, SQL Server truncates the data on the right..

Binary to numeric

When you convert the number to binary,  the leading digits are silently truncated

For Example, in the following code @bnum variable can only store 2 bytes, which is insufficient to store the integer 123456. No errors are throw here, but the leading digits of the number is truncated. When we reconvert the results back, we will get an entirely different result.

Increase the size of the @bnum to binary(3) to get the correct result

Binary to Binary

Converting from One Binary data type to another binary data type, will also result in truncation if the target type have insufficient space

Ensure that the target type have sufficient bytes

Which one to choose

You need to choose the right binary data type. This is because of the silent padding & truncation that happens, when we convert data from another type to binary and vice versa.

Data typeUse when …
binarythe sizes of the column data entries are consistent.
varbinarythe sizes of the column data entries vary considerably.
varbinary(max)the column data entries exceed 8,000 bytes.

Reference

binary and varbinary (Transact-SQL)

Leave a Comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Scroll to Top