BINARY and VARBINARY (Transact-SQL)

These data types are used to store fixed-length and variable-length binary data.

BINARY[(n)]

Fixed-length binary data of length n bytes. n can be a value from 1 to 8000. Storage size is n bytes.

Syntax

DECLARE @MyBinary BINARY(10);

Description

The BINARY data type stores data as a sequence of bytes. If you specify a length n, each entry will occupy exactly n bytes. If you provide less than n bytes of data, the data will be padded with hexadecimal zeros (0x00) to reach the specified length.

Example

-- Declaring a BINARY variable with 10 bytes
DECLARE @FixedData BINARY(10);

-- Assigning data (less than 10 bytes)
SET @FixedData = 0x123456;

-- The value stored will be 0x12345600000000000000
SELECT @FixedData;

-- Assigning data (exactly 10 bytes)
SET @FixedData = 0x0102030405060708090A;
SELECT @FixedData;

-- Assigning data (more than 10 bytes will cause an error)
-- SET @FixedData = 0x0102030405060708090A0B; -- This would error out

VARBINARY[(n)]

Variable-length binary data. n can be a value from 1 to 8000. Storage size is the actual length of the data entered in bytes, plus 2 bytes to store the length.

Syntax

DECLARE @MyVarBinary VARBINARY(50);

Description

The VARBINARY data type stores binary data of varying lengths. Unlike BINARY, it does not automatically pad data to a fixed length. The storage used is dynamic, accommodating the actual size of the data inserted. This makes it more efficient for storing binary data where the size can fluctuate.

Example

-- Declaring a VARBINARY variable with a maximum of 50 bytes
DECLARE @VariableData VARBINARY(50);

-- Assigning data of different lengths
SET @VariableData = 0xAA;
SELECT LEN(@VariableData) AS Length1, @VariableData AS Data1; -- Length: 1, Data: 0xAA

SET @VariableData = 0xBBCCDD;
SELECT LEN(@VariableData) AS Length2, @VariableData AS Data2; -- Length: 3, Data: 0xBBCCDD

SET @VariableData = 0x0102030405060708090A;
SELECT LEN(@VariableData) AS Length3, @VariableData AS Data3; -- Length: 10, Data: 0x0102030405060708090A

Key Differences and When to Use

• Length: BINARY is fixed length, VARBINARY is variable length.
• Padding: BINARY pads with 0x00 if less data is provided than specified. VARBINARY does not pad.
• Storage: BINARY(n) always uses n bytes. VARBINARY(n) uses the actual data length plus 2 bytes for length information.
• Use Case: Use BINARY when you know the exact size of the binary data and need consistent storage. Use VARBINARY when the size of the binary data varies and you want to optimize storage space. Common uses include storing images, files, or encrypted data.

Related Data Types

• Character Data Types
• Numeric Data Types