MSDN Documentation

Binary Data Types

Binary data types are used to store raw binary data, such as images, files, or any other sequence of bytes. SQL Server provides several binary data types, each with specific storage capacities and characteristics.

VARBINARY(n)

The VARBINARY(n) data type stores variable-length binary data. n specifies the maximum number of bytes that can be stored, from 1 to 8000 bytes.

Example:

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100),
    ProductImage VARBINARY(MAX) -- Using MAX for potentially large images
);

BINARY(n)

The BINARY(n) data type stores fixed-length binary data. n specifies the exact number of bytes to be stored, from 1 to 8000 bytes. If the provided data is less than n bytes, it will be padded with binary zeros.

Example:

CREATE TABLE Fingerprints (
    UserID INT PRIMARY KEY,
    LeftThumb BINARY(1024), -- Fixed size for a fingerprint template
    RightThumb BINARY(1024)
);

VARBINARY(MAX)

The VARBINARY(MAX) data type (formerly known as image) stores variable-length binary data with a maximum capacity of 2^31-1 bytes (approximately 2 GB).

This is the recommended type for storing large binary objects.

BINARY(MAX)

The BINARY(MAX) data type stores fixed-length binary data with a maximum capacity of 2^31-1 bytes. Similar to BINARY(n), it pads data with binary zeros if it's shorter than the specified (implicit) maximum.

Choosing the Right Binary Type

Data Type Storage Variable Length Use Case
BINARY(n) Fixed (n bytes) No When all data items for a column are exactly the same size.
VARBINARY(n) Variable (up to n bytes) Yes When data sizes vary but have a known maximum limit.
BINARY(MAX) Variable (up to ~2 GB) No (conceptually, but effectively variable due to MAX) Rarely used, generally prefer VARBINARY(MAX).
VARBINARY(MAX) Variable (up to ~2 GB) Yes Storing large binary objects like files, images, videos.

Considerations