SQL Binary Data Types

Introduction

SQL binary data types store raw bytes rather than ordinary characters. Those bytes may represent an image, PDF document, encrypted value, cryptographic hash, digital signature, compressed payload, certificate, serialized message, or another format whose meaning is interpreted by an application. A database preserves the byte sequence; it does not assume that the sequence contains readable language.

The main binary families are fixed-length BINARY, variable-length VARBINARY, and large-object types such as BLOB. Product-specific equivalents include PostgreSQL BYTEA, Oracle RAW and BLOB, SQL Server VARBINARY(MAX), and SQLite BLOB. Choosing among them requires more than estimating a maximum length. The schema designer must understand the value's representation, size distribution, access pattern, security requirements, transaction boundaries, backup impact, and portability needs.

What Is Binary Data?

Binary data is a sequence of bytes. A byte contains eight bits and can hold a value from 0 through 255. Tools often display bytes in hexadecimal because one byte maps neatly to two hexadecimal digits. For example, the displayed sequence 4F A2 00 FF contains four bytes, even though it occupies eight hexadecimal digits plus formatting spaces.

Those bytes do not carry character meaning by themselves. They might be the opening bytes of a JPEG, part of an encrypted token, or four values in a proprietary protocol. The format specification and application logic determine the interpretation. This separation is why raw bytes should normally use a binary column rather than a character column.

Binary Data Versus Character Data

CHAR, VARCHAR, and text types store character data under a character set and may apply collation rules. A binary value is compared byte by byte and has no natural concepts of uppercase, accents, words, or locale. Passing arbitrary bytes through a text encoding can reject them, replace invalid sequences, or change their representation.

QuestionCharacter typeBinary type
Logical contentHuman-readable charactersRaw byte sequence
EncodingUses a character encodingNo character encoding semantics
ComparisonMay use linguistic collationUsually compares byte values
ExamplesName, email, JSON textHash, image, ciphertext
Typical typesCHAR, VARCHAR, TEXTBINARY, VARBINARY, BLOB

A hexadecimal or Base64 representation is still text. The text FF10 contains four characters; after hexadecimal decoding it represents two bytes. Similarly, SGVsbG8= is Base64 text that decodes to the bytes for Hello. A schema must record which representation it stores because raw, hexadecimal, and Base64 values are not interchangeable without conversion.

Main Binary Type Families

Binary types can be organized by whether their length is fixed, variable, or large. Exact limits and physical behavior vary by database, so product documentation and realistic tests remain important.

FamilyLength behaviorTypical use
BINARY(n)Exactly n bytesFixed-size hashes or identifiers
VARBINARY(n)Up to n bytesTokens, signatures, short payloads
BLOB or large binaryLarge variable contentDocuments, images, archives
Product-specific typeDepends on DBMSBYTEA, RAW, VARBINARY(MAX)

BINARY for Fixed-Length Values

BINARY(n) represents a fixed number of bytes. It is a strong choice when every valid value has the same known length, such as a decoded SHA-256 digest at 32 bytes or a decoded UUID at 16 bytes. Fixed length communicates the domain and prevents accidentally inserting a different-sized representation.

CREATE TABLE file_fingerprints (
    file_id BIGINT PRIMARY KEY,
    sha256_hash BINARY(32) NOT NULL,
    size_bytes BIGINT NOT NULL,
    UNIQUE (sha256_hash, size_bytes)
);

Some systems pad shorter fixed binary values. Padding, comparison, and truncation rules differ, so applications should supply exactly the declared length and tests should verify round trips. Do not choose fixed length merely because today's samples happen to be similar.

VARBINARY for Variable-Length Values

VARBINARY(n) stores a byte sequence whose actual length can vary up to a declared maximum. It suits encrypted outputs, signatures, protocol messages, thumbnails, and security tokens whose lengths are bounded but not identical.

CREATE TABLE signed_requests (
    request_id BIGINT PRIMARY KEY,
    payload_hash BINARY(32) NOT NULL,
    signature_bytes VARBINARY(512) NOT NULL
);

Unlike a fixed type, variable binary generally stores the actual bytes plus length metadata. A realistic maximum protects the schema and communicates expectations. An enormous limit on every column may push values into different storage paths or make memory and index behavior less predictable.

BLOB and Large Binary Objects

A BLOB, or Binary Large Object, stores large variable-length content such as a PDF, image, ZIP archive, or audio fragment. A BLOB is the content itself, not a path to a file. If a column contains /documents/report.pdf, that value is a character path and belongs in a string column.

CREATE TABLE documents (
    document_id BIGINT PRIMARY KEY,
    file_name VARCHAR(255) NOT NULL,
    mime_type VARCHAR(100) NOT NULL,
    size_bytes BIGINT NOT NULL,
    sha256_hash BINARY(32) NOT NULL,
    document_data BLOB NOT NULL,
    created_at TIMESTAMP NOT NULL
);

Large-object declarations are only one part of the design. A database may store small values inline and larger values out of row, on separate pages, or through a special large-object subsystem. That behavior affects reads, updates, caching, transaction logs, replication, backups, and restore times.

Binary Literals and Hexadecimal Display

Database products use different literal and conversion syntax. Common forms include hexadecimal notation such as 0x4FA200FF, standard-style binary strings, or decoding functions. Hexadecimal is convenient for diagnostics because it is lossless and compact enough for short values, but application code should normally bind bytes through a parameter rather than concatenate a literal.

-- Conceptual parameterized operation
INSERT INTO file_fingerprints (file_id, sha256_hash, size_bytes)
VALUES (?, ?, ?);

The driver receives the hash as bytes and maps it to the binary parameter. This avoids quoting errors, SQL injection, accidental text conversion, and unnecessary encoding overhead.

Binary Length and Empty Values

Binary length is measured in bytes, not characters. Functions vary: standard SQL environments may offer OCTET_LENGTH, SQL Server commonly uses DATALENGTH, and other products provide their own length functions. Confirm whether a function returns bytes, characters, or storage units.

NULL and a zero-length byte sequence are different. NULL can mean that no file is known or stored, while an empty BLOB can mean that a file exists but contains zero bytes. The distinction should come from the domain. Avoid defaulting every binary column to empty data because doing so erases the difference between absence and an intentionally empty value.

Hashes and Checksums

A cryptographic digest has a predictable raw length: MD5 produces 16 bytes, SHA-1 produces 20, SHA-256 produces 32, and SHA-512 produces 64. Legacy algorithms such as MD5 and SHA-1 are not appropriate where collision resistance is a security requirement. When an application stores a SHA-256 digest as raw output, BINARY(32) is clearer and smaller than a 64-character hexadecimal string.

Hex text can still be appropriate when systems exchange, inspect, or manually enter hashes in that representation. The important rule is to identify the representation explicitly. A 32-byte digest, a 64-character hex string, and a Base64 string can represent the same mathematical result but require different column types and comparison inputs.

Hashes can support integrity checks and content deduplication. A service can calculate a digest, look up the digest and size, and reuse an existing object when policy allows. Cryptographic collisions are theoretically possible, so highly sensitive workflows may verify both size and content before treating two payloads as identical.

Hashes Are Not Encryption

Hashing is intended to be one-way, while encryption is designed to be reversible with a key. Neither operation is provided merely by declaring a binary column. The type only preserves output bytes. Passwords should use a dedicated password-hashing algorithm such as Argon2, bcrypt, scrypt, or PBKDF2 with suitable parameters and salts, never reversible encryption or a fast general-purpose hash alone.

Many password libraries return a self-describing encoded string containing the algorithm, cost, salt, and digest. That result is legitimately stored as text because the library's output format is textual. Do not decode it into a binary column without understanding and preserving all components.

Encrypted Values and Key Management

Encryption commonly produces binary ciphertext, an initialization vector or nonce, and an authentication tag. The schema may use separate constrained columns or a versioned envelope whose format is documented. Store the algorithm or key version needed for rotation; never assume that one undocumented byte layout will remain permanent.

CREATE TABLE protected_secrets (
    secret_id BIGINT PRIMARY KEY,
    cipher_version SMALLINT NOT NULL,
    key_version INTEGER NOT NULL,
    nonce_bytes VARBINARY(32) NOT NULL,
    ciphertext VARBINARY(4096) NOT NULL,
    auth_tag VARBINARY(32) NOT NULL
);

A binary type does not make a value confidential. Database privileges, transport encryption, key custody, log redaction, backup protection, and application authorization remain necessary. Keys should normally be managed through an appropriate key-management service rather than stored casually beside the ciphertext they protect.

Security Tokens, Signatures, and Certificates

Random API tokens may originate as raw bytes and then be encoded for transmission. A secure system often stores only a keyed or cryptographic hash of the presented token, reducing the damage if the database is exposed. Token lookup design, rotation, revocation, expiration, and constant-time verification should be considered together.

Digital signatures are variable binary results whose permitted size depends on the algorithm and encoding. Certificates can use binary DER or textual PEM. If the application stores PEM, a text type is appropriate; if it stores DER, use binary. The same principle applies to public and private keys, although private-key storage demands stricter custody and should not be treated as an ordinary BLOB requirement.

UUID Storage

A UUID contains 128 bits, or 16 bytes. Some teams store it in BINARY(16) to reduce storage compared with a 36-character formatted string. Conversion at application boundaries must preserve byte order consistently, particularly because vendor functions and libraries may reorder components for index locality.

When the database offers a native UUID type, that type is often preferable. It communicates semantics, validates input, offers appropriate operators, and avoids custom conversion code. Binary UUIDs can be useful for portability or specific physical designs, but compactness alone should not override clarity.

Files in the Database Versus Object Storage

Storing files in the database provides transactional consistency with related rows, centralized authorization, and one backup boundary. It can be a sound choice for small attachments, certificates, signed snapshots, and documents that must commit atomically with business data.

Large or numerous files can make databases, transaction logs, replicas, and backups grow rapidly. Object storage is designed for large payloads, streaming delivery, lifecycle policies, and scalable capacity. A common architecture keeps an object key, metadata, hash, ownership, and status in SQL while storing the bytes externally.

CREATE TABLE stored_files (
    file_id BIGINT PRIMARY KEY,
    object_key VARCHAR(500) NOT NULL UNIQUE,
    original_name VARCHAR(255) NOT NULL,
    mime_type VARCHAR(100) NOT NULL,
    size_bytes BIGINT NOT NULL,
    sha256_hash BINARY(32) NOT NULL,
    storage_status VARCHAR(30) NOT NULL
);

This split introduces consistency work: object upload and database commit are not one native transaction. Production designs use staged states, idempotent operations, cleanup jobs, and reconciliation to handle partial failure. The right architecture depends on file size, count, access frequency, compliance, disaster recovery, and operational expertise.

Store Metadata Separately

Whether bytes live in SQL or object storage, queryable metadata should use ordinary typed columns. File name, MIME type, byte length, hash, owner, dimensions, creation time, and scan status should not require parsing the payload for every request. Metadata supports filtering, authorization, validation, and reporting without loading the large value.

Searchable content also belongs in an appropriate index. A PDF BLOB preserves the original document, but LIKE cannot provide useful full-text search over its encoded structure. Extract text into a text-search system or indexed column. For images, store OCR text, labels, dimensions, or embeddings separately when those capabilities are required.

Database Product Differences

Binary type names are not portable in the same way across products. Even similar declarations can differ in maximum length, padding, literal syntax, conversion functions, index limits, streaming APIs, and storage behavior.

DatabaseCommon binary typesPractical note
MySQLBINARY, VARBINARY, TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOBChoose the BLOB family and limits deliberately
PostgreSQLBYTEA, large objectsBYTEA handles ordinary variable binary values
SQL ServerBINARY, VARBINARY, VARBINARY(MAX)IMAGE is deprecated for new designs
OracleRAW, BLOBRAW suits bounded values; BLOB suits large content
SQLiteBLOB storage classDynamic typing makes application validation important

MySQL Binary Types

MySQL distinguishes fixed BINARY(n), variable VARBINARY(n), and several BLOB sizes. Binary strings are byte sequences rather than nonbinary strings governed by a character set and collation. Select the smallest meaningful family without setting limits so narrowly that valid business data fails unexpectedly.

BLOB variants affect maximum capacity and length metadata. Capacity is not a recommendation to store enormous objects in ordinary transactional rows; packet, client, memory, replication, and backup settings still matter.

PostgreSQL BYTEA

PostgreSQL commonly uses BYTEA for variable-length binary content. It does not provide ordinary BINARY(n) and VARBINARY(n) declarations in the same style as some other databases. Length requirements can be enforced with a check constraint when the domain demands them.

CREATE TABLE digests (
    digest_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sha256_value BYTEA NOT NULL,
    CHECK (octet_length(sha256_value) = 32)
);

PostgreSQL also has a large-object facility with different APIs and transaction behavior. Choose between BYTEA, large objects, and external storage based on size and access requirements rather than type-name similarity.

SQL Server Binary Storage

SQL Server provides BINARY(n), VARBINARY(n), and VARBINARY(MAX). The older IMAGE type should not be selected for new work. DATALENGTH can report bytes, and application drivers can stream large values instead of materializing the entire payload.

Large-value and FILESTREAM-related options can change physical storage and API behavior. Evaluate them as architectural features, not interchangeable syntax. Index key size, random identifier behavior, and transaction-log growth should be measured with the real workload.

Oracle RAW and BLOB

Oracle RAW stores bounded byte sequences and is useful for values such as hashes or identifiers. BLOB handles large binary objects through large-object APIs. Length limits depend on context and Oracle release, so migrations should not translate VARBINARY mechanically to a guessed RAW size.

SQLite BLOB Values

SQLite has a BLOB storage class and flexible typing. A declared column does not provide the same strict length domain as a fixed binary type in other systems. The application, constraints, and tests must enforce representation and size where those properties matter.

Indexing Binary Columns

Short hashes and identifiers can be indexed for equality lookup, uniqueness, or deduplication. Fixed raw hashes make compact keys compared with hexadecimal text. Large BLOBs should not be used directly as ordinary index keys; indexing them is expensive and usually does not support the business query.

Store a digest and useful metadata beside a large object, then index those smaller columns. An index on a random UUID can cause scattered writes and page splits in some physical designs. Sequential or time-ordered identifiers may improve locality, but they have different privacy and concurrency properties and should be selected deliberately.

Binary comparisons are normally exact byte comparisons. They do not apply case-insensitive or accent-insensitive linguistic rules. A database's term binary collation for character data is not the same thing as declaring a binary byte column.

Compression and Serialized Objects

Compressed data is binary, but compression is not automatically beneficial. JPEG, PNG, ZIP, and many media formats are already compressed, so another compression layer may consume CPU without reducing size. Database page compression, application compression, and object-storage compression can overlap; measure the complete system before adding a custom layer.

Serialized application objects can also be stored as bytes, but this often creates a long-term maintenance problem. Language version changes, class renaming, library upgrades, and untrusted deserialization can make old rows unreadable or unsafe. Structured business attributes that need queries, constraints, joins, or independent updates should remain modeled as columns and relations. A BLOB is not a replacement for database design.

Application Mapping and Streaming

Drivers typically map binary columns to byte arrays or streams: Java uses byte[], InputStream, or BLOB APIs; Python uses bytes; JavaScript runtimes commonly use buffers or typed arrays; and .NET uses byte[] or streams. Small values can be read as arrays, while large values should usually be streamed.

Loading a 500 MB document into memory for one insert can exhaust an application process under concurrent traffic. Stream from the upload source through validation and storage with bounded buffers. Do not hold a database transaction open while a client uploads slowly or while malware scanning waits on an external service. Stage the payload first, validate it, and keep the final transaction focused.

Connection pools and ORM layers may silently materialize lazy BLOB fields or include them in broad SELECT * queries. Keep metadata and payload access separate so list pages do not transfer every document. Test the generated SQL and memory profile, not just the entity mapping.

Binary Data in APIs

HTTP can carry raw binary with a suitable media type such as application/octet-stream or the file's specific content type. Multipart requests are useful when metadata and files travel together. JSON has no native byte type, so APIs commonly encode bytes as Base64 text.

Base64 is appropriate at a text-only boundary but adds roughly one-third size overhead before JSON and transport overhead. Large files should usually use streaming upload and download endpoints rather than huge Base64 properties. API contracts must specify encoding, maximum decoded size, hash algorithm, content type, and error behavior.

Upload Validation and Safe File Handling

A file extension and client-provided MIME type are not trustworthy proof of content. A secure pipeline checks decoded size, allowed format, internal structure or magic bytes, and malware scan results. It rejects malformed polyglot or decompression-bomb inputs according to risk policy and records a safe server-generated storage name.

Serving uploaded files also requires authorization, correct Content-Type, a deliberate Content-Disposition, cache controls, and protection against active content. User uploads should not be rendered inline under a trusted application origin without analysis. The fact that bytes were stored successfully says nothing about their safety.

Logging, Replication, and Backups

Binary-heavy workloads affect the entire data platform. Large inserts and updates can expand transaction logs, replication queues, change-data-capture streams, snapshots, backup windows, and restore time. A table holding one million 5 MB files contains about 5 TB of raw payload before logs, replicas, indexes, backups, and overhead.

Application logs should record identifiers, sizes, hashes where appropriate, and operation outcomes rather than dumping binary content, Base64 secrets, tokens, or private keys. Change feeds may need to exclude the payload or publish an object reference. Disaster-recovery testing must prove that both metadata and external objects can be restored to a consistent point.

Transactions and Concurrency

Database BLOBs can commit atomically with related rows, but large operations hold locks and resources longer. Updating an entire payload for a small metadata change creates unnecessary write amplification. Keep immutable content separate from mutable metadata where practical, and use version columns or content hashes for optimistic concurrency.

External object storage requires a state machine. A row can move through pending upload, scanned, available, quarantined, and deletion-pending states. Idempotent workers and reconciliation jobs handle retries when object and database operations succeed independently. Never assume a single network request makes the two systems transactional.

Constraints and Data Integrity

Use NOT NULL when absence is invalid, length checks for fixed representations, and uniqueness where the domain requires it. Metadata constraints can reject negative sizes and unsupported status values. Some systems cannot express every validation efficiently in DDL, so application validation and background reconciliation supplement rather than replace database constraints.

CREATE TABLE binary_assets (
    asset_id BIGINT PRIMARY KEY,
    content_hash BINARY(32) NOT NULL,
    size_bytes BIGINT NOT NULL CHECK (size_bytes >= 0),
    mime_type VARCHAR(100) NOT NULL,
    asset_data BLOB NOT NULL,
    UNIQUE (content_hash, size_bytes)
);

A stored hash can detect accidental corruption only when the system recalculates and compares it. Hash metadata that is never verified is not an integrity process.

Migration and Portability

Binary migrations must preserve exact bytes. Exporting through text tools can accidentally convert values to hex, Base64, escaped notation, or corrupted characters. Document the source and target representations, stream large rows, calculate checksums before and after, and reconcile counts and byte lengths.

A cross-database migration also maps type limits and large-object APIs. SQL Server VARBINARY(MAX) does not become PostgreSQL BYTEA solely through a name change if the application relies on specific streaming, FILESTREAM, or transaction behavior. Test drivers, prepared statements, NULL and empty values, maximum sizes, and rollback paths.

Moving BLOBs to object storage is usually an online workflow: create metadata and state columns, copy content in batches, verify hashes, switch readers with fallback, switch writers, monitor reconciliation, and remove old payloads only after retention and recovery requirements are satisfied.

Performance Design

Separate hot relational columns from cold payloads so ordinary queries read fewer pages and use cache effectively. Select only required columns, paginate metadata results, stream content, and avoid repeatedly updating large values. Measure inline versus out-of-row thresholds and do not rely on assumptions about physical storage.

Performance tests should include realistic size distribution and concurrency. Ten tiny samples cannot predict behavior for mixed 10 KB thumbnails, 20 MB PDFs, and occasional 1 GB archives. Observe latency, memory, temporary storage, transaction-log growth, replication lag, backup throughput, and recovery time.

Common Binary Data Mistakes

  • Storing arbitrary bytes in VARCHAR and risking encoding conversion.
  • Encoding every value as Base64 even when the database accepts raw bytes.
  • Using a BLOB for a fixed 32-byte SHA-256 digest.
  • Confusing a file path with the file's binary content.
  • Assuming a binary column encrypts or secures its value.
  • Storing private keys without dedicated custody and access controls.
  • Using serialized objects instead of modeling queryable business data.
  • Loading every BLOB into memory or retrieving it with list queries.
  • Trusting file extensions and client MIME types during uploads.
  • Indexing entire large objects rather than compact hashes and metadata.
  • Ignoring replication, backup, restore, and retention costs.
  • Treating NULL and an empty byte sequence as the same state.
  • Copying bytes through text-based migration tools without verification.

Binary Type Selection Checklist

  1. Confirm that the value is raw bytes rather than meaningful characters.
  2. Document whether the stored representation is raw, hex, Base64, PEM, or another format.
  3. Use fixed binary for exact known lengths and variable binary for bounded variation.
  4. Evaluate BLOB, native large-object features, and object storage for large content.
  5. Store searchable and operational metadata in separate typed columns.
  6. Define NULL, empty, and missing-object semantics explicitly.
  7. Use prepared statements and stream large values through the driver.
  8. Validate size, format, content, authorization, and malware risk.
  9. Index compact lookup values rather than complete payloads.
  10. Plan encryption keys, token hashing, log redaction, and backup security.
  11. Measure transactions, memory, replication, backups, and recovery at realistic scale.
  12. Verify exact bytes and checksums during every migration.

Testing Binary Columns

Test minimum, typical, maximum, and over-limit payloads. Include all byte values, leading and trailing zero bytes, invalid encodings at API boundaries, NULL, and a zero-length sequence. Round-trip each case and compare bytes rather than a formatted display. Fixed-length columns need shorter and longer negative tests to reveal padding or truncation behavior.

For files, test a valid format, spoofed extension, incorrect MIME type, malformed header, malware response, timeout, interrupted upload, duplicate upload, unauthorized download, range request, and safe response headers. For hashes and encryption, test known vectors, wrong key versions, corrupted tags, rotation, and log redaction.

Load tests must monitor memory while multiple large values stream concurrently. Migration tests compare row counts, NULL counts, byte lengths, and cryptographic digests. Recovery exercises verify that database rows and external objects remain synchronized after restore.

Interview Questions and Answers

What is a SQL binary data type? It is a type that stores an exact sequence of bytes without character-encoding semantics.

What is the difference between BINARY and VARBINARY? BINARY has a fixed declared byte length, while VARBINARY stores varying lengths up to a limit.

When should BLOB be used? Use it for large binary content when keeping the payload in the database fits transaction, performance, backup, and recovery requirements.

Is Base64 a binary type or encryption? Neither. It is a textual encoding of bytes and provides no confidentiality.

Why can BINARY(32) suit SHA-256? The raw SHA-256 output is always 32 bytes; its common hexadecimal display is 64 characters.

Should large files always be stored in the database? No. Object storage is often better for large or numerous files, while databases can be useful for smaller transactional content.

What is the difference between NULL and an empty BLOB? NULL means no known value; an empty BLOB is a present byte sequence with length zero.

How should applications insert binary data? Bind bytes with a prepared statement and use streaming APIs for large values.

Key Takeaway

Binary types preserve raw bytes, but the schema must still define what those bytes represent and how they are validated, secured, queried, moved, and recovered. Use fixed binary for fixed-size values, variable binary for bounded byte sequences, and a large-object or external-storage design for substantial payloads.

BINARY DESIGN = KNOWN REPRESENTATION
              + APPROPRIATE LENGTH FAMILY
              + SAFE PARAMETER BINDING
              + QUERYABLE METADATA
              + SECURITY AND VALIDATION
              + MEASURED STORAGE OPERATIONS

Prefer raw storage over unnecessary text encoding, keep large payloads out of routine queries, and verify exact bytes across every application and migration boundary. The best binary design balances semantic clarity with database performance and operational reality.