Compare

CHAR vs VARCHAR

Q: Why does CHAR pad with spaces?

CHAR is fixed-width: CHAR(10) is always exactly 10 characters in storage. Values shorter than 10 chars are right-padded with spaces. The padding is the source of unexpected behavior: comparisons sometimes ignore trailing spaces, sometimes don't, depending on engine and ANSI_PADDING settings.

Q: What about CHAR vs VARCHAR2 in Oracle?

Oracle has both CHAR (blank-padded) and VARCHAR2 (variable-length, no padding). Use VARCHAR2; it's Oracle's preferred string type. The plain VARCHAR keyword in Oracle is reserved for future use; treat it as deprecated.

Short answer: CHAR(n) is a fixed-length string: every value is padded with spaces to exactly n characters. VARCHAR(n) is variable-length, with values stored as-is, up to n characters. Use VARCHAR for almost everything. CHAR is occasionally useful for genuinely fixed-width data (country codes, hash digests, fixed-format identifiers) and for marginal performance gains on very-frequently-accessed fixed-width columns. The rest of the time, CHAR just wastes space and creates trailing-space comparison bugs.

Quick comparison #

	`CHAR(n)`	`VARCHAR(n)`
Storage	always exactly `n` characters (space-padded)	actual length + 1-2 byte length prefix
Length-cap behavior	rejects too-long input	rejects too-long input
Trailing spaces	added automatically	preserved as you wrote them
Comparison rules	spaces ignored at end (engine-dependent)	spaces matter
Use for	hash digests, country codes, fixed-format IDs	almost everything else

See the padding behavior #

Loading SQL editor...

When to use which #

VARCHAR: the default for any text column. Names, emails, addresses, descriptions, slugs, anything where the length isn't actually fixed.

CHAR(n) is a niche tool, but legitimately the right choice when:

The data is physically fixed-width: ISO-3166 country codes (CHAR(2)), ISO-4217 currency codes (CHAR(3)), MD5 hashes (CHAR(32)), SHA256 digests (CHAR(64)), license-plate IDs in a known format.
You want the type to document the constraint (analysts inspecting the schema see "CHAR(2)" and immediately know it's a fixed-width code).
You're in a write-heavy OLTP context where the storage savings of skipping the per-row length prefix matter (rare on modern engines, usually negligible).

Don't use CHAR for: usernames, names, emails, anything user-typed. The trailing spaces become a debugging tax forever.

Common mistakes #

Comparing CHAR and VARCHAR and getting unexpected results: depending on engine, 'ana'::CHAR(10) = 'ana'::VARCHAR(10) may or may not match. The "padding-space-aware comparison" is in the SQL standard but not consistently implemented.
Using CHAR(255) for "short text" because the docs said CHAR is faster: the storage and performance cost of padding 250 spaces per row is real and outweighs any micro-optimization.
Storing nullable CHAR: works, but a NULL and a CHAR(10) of all-spaces are easy to confuse downstream. Pick one convention.
Indexing CHAR differently from VARCHAR: most engines treat the index identically; on MySQL, indexing semantics around trailing spaces have caused subtle bugs ('ana' matches 'ana ' in lookups but not in equality joins). Test.

Dialect notes #

Postgres: CHAR(n) (alias CHARACTER(n)) is space-padded on storage but trimmed on retrieval/cast. Postgres officially recommends against CHAR for almost everything — "no performance advantages."
MySQL: CHAR(n) is space-padded on storage; trailing spaces are removed on retrieval. Comparisons ignore trailing spaces. BINARY(n) if you want the spaces preserved.
SQL Server: CHAR(n) is genuinely space-padded both ways. Comparison rules depend on SET ANSI_PADDING; default behavior treats 'ana' and 'ana ' as equal.
Oracle: CHAR(n) uses "blank-padded comparison": string comparisons are done after right-padding both operands to the same length. Different from VARCHAR2 (Oracle's preferred type) which uses non-padded comparison.
Snowflake / BigQuery / DuckDB / Redshift: CHAR and VARCHAR are both supported but typically aliased to a single underlying string type with no real difference. The (n) is a constraint, not a storage choice.

FAQ #

When should I use CHAR instead of VARCHAR?

Almost never. The legitimate cases: physically fixed-width data (country codes CHAR(2), currency codes CHAR(3), MD5 hashes CHAR(32), SHA256 digests CHAR(64)) where the type itself documents the constraint. Anywhere else, VARCHAR avoids the trailing-space ambiguity that costs more debugging than CHAR ever saves.

Why does CHAR pad with spaces?

CHAR is fixed-width: CHAR(10) is always exactly 10 characters in storage. Values shorter than 10 chars are right-padded with spaces. The padding is the source of unexpected behavior: comparisons sometimes ignore trailing spaces, sometimes don't, depending on engine and ANSI_PADDING settings.

Does VARCHAR(10) waste space if I store 'ana'?

No. VARCHAR stores actual length plus a 1- or 2-byte length prefix. VARCHAR(10) holding 'ana' takes 4 bytes; CHAR(10) holding the same value takes 10 bytes plus the trailing-space padding. VARCHAR is more space-efficient for variable-length strings.

Will CHAR be faster than VARCHAR?

Modern engines: rarely meaningful. CHAR avoids the per-row length prefix and may pack more tightly. The micro-optimization is real but rarely worth the trailing-space ambiguity. For high-frequency, fixed-width hot-path columns where storage micro-optimization matters, CHAR has a small edge.

Why do my CHAR comparisons sometimes match values with trailing spaces?

ANSI SQL says comparisons should ignore trailing spaces (the strings are blank-padded for comparison). Most engines do this for CHAR columns. SQL Server's behavior depends on ANSI_PADDING setting. The result: 'ana' and 'ana ' may compare equal — usually convenient, occasionally a bug.

What about CHAR vs VARCHAR2 in Oracle?

Oracle has both CHAR (blank-padded) and VARCHAR2 (variable-length, no padding). Use VARCHAR2; it's Oracle's preferred string type. The plain VARCHAR keyword in Oracle is reserved for future use; treat it as deprecated.

Tip

The default rule: use VARCHAR(n) (or unbounded VARCHAR / TEXT) and forget CHAR exists. Reach for CHAR deliberately when you have physically fixed-width data — country codes, hashes, license plates — and want the type to advertise that fact. Anywhere else, CHAR introduces trailing-space ambiguity that costs more debugging time than it ever saves in storage. Reference: VARCHAR vs TEXT for the related "should I have a length cap?" question.