SQL VARCHAR vs CHAR
Introduction
CHAR and VARCHAR both store character data, but they express different assumptions about length. CHAR(n) represents a fixed-length value and usually pads shorter input to the declared width. VARCHAR(n) represents variable-length content whose maximum is n. That distinction affects storage, trailing spaces, comparison, indexes, migration, and how clearly a schema communicates its domain.
The choice should not be reduced to the slogan that CHAR is faster or VARCHAR saves space. Modern database engines have product-specific row formats, encodings, and optimization strategies. The correct decision begins with meaning: use fixed length when every valid value has a stable width, and variable length when valid values naturally differ in length.
What Is CHAR?
CHAR(n), also written as CHARACTER(n), declares fixed-length character data. If a value contains fewer than n characters, the database normally fills the remainder with spaces according to its implementation. The declaration communicates that width is part of the value's domain.
CREATE TABLE currencies (
currency_code CHAR(3) PRIMARY KEY,
currency_name VARCHAR(80) NOT NULL
);
Three-letter ISO currency codes are a strong example because valid codes share one defined width. Human names, notes, and addresses do not.
What Is VARCHAR?
VARCHAR(n), or CHARACTER VARYING(n), stores variable-length character content up to a declared maximum. A value with 12 characters remains a 12-character value even when the maximum is 200; the engine does not normally fill it to 200 characters.
CREATE TABLE customers (
customer_id BIGINT PRIMARY KEY,
display_name VARCHAR(200) NOT NULL,
email_address VARCHAR(320)
);
VARCHAR is the normal starting point for bounded human-entered text because real values vary and padding has no business meaning.
The Core Difference
| Concern | CHAR(n) | VARCHAR(n) |
|---|---|---|
| Length model | Fixed width | Variable up to a maximum |
| Short values | Usually space padded | Remain at actual length |
| Typical use | Standardized fixed-width codes | Names, email, labels, identifiers |
| Storage concept | Based on declared width | Actual content plus length metadata |
| Primary concern | Padding and wasted width | Choosing an appropriate maximum |
The number n means a fixed shape for CHAR and a ceiling for VARCHAR. Knowing a maximum length does not make a value fixed length.
Fixed Length Versus Maximum Length
A country code may be exactly two characters by specification. A product name may permit up to 200 characters but usually contains far fewer. Both declarations include a number, yet they express different rules.
country_code CHAR(2)
product_name VARCHAR(200)
Use CHAR only when every valid value is expected to occupy the same width. Do not use it merely because an application form happens to cap input at a particular maximum.
How CHAR Padding Works
When 'US' is assigned to CHAR(5), the logical fixed-width representation may contain three trailing spaces. Databases can trim, preserve, or ignore those spaces in different contexts. Drivers may also return values differently.
Padding matters to length functions, concatenation, binary comparisons, exports, hashes, and application equality. A value that appears as 'US' on screen can behave as a five-character field somewhere else. This is why fixed-length behavior should be intentional rather than incidental.
Trailing Spaces and Comparisons
SQL products often ignore trailing spaces in ordinary CHAR comparisons and sometimes in VARCHAR comparisons, but the exact behavior depends on product, data type, and collation. Binary comparisons may produce a different result. Applications should not encode business meaning in trailing spaces.
SELECT currency_code
FROM currencies
WHERE currency_code = 'USD';
Normalize controlled identifiers before storage, validate their exact visible length, and test through the production driver. Do not rely on an implicit padding rule to validate a code.
VARCHAR and Whitespace
VARCHAR does not add fixed padding, but it can store spaces supplied by the user. 'ABC', ' ABC', and 'ABC ' may remain distinct inputs even when a chosen comparison treats some of them as equal. Decide whether leading or trailing whitespace is meaningful for each domain.
Codes and email fields are often trimmed at a controlled boundary. Human names and free text need more careful treatment because spaces can be meaningful. One global trimming rule is rarely suitable for every character column.
Storage Behavior
Conceptually, CHAR reserves its declared width and VARCHAR stores actual content plus metadata describing its length. The physical details vary with database engine, row format, compression, encoding, NULL bitmap, and whether a value is stored off-row. It is therefore unsafe to calculate exact storage from the SQL declaration alone.
For a million rows where values range from five to one hundred characters, VARCHAR can avoid substantial unused width. For an always-three-character code, the difference may be negligible. Measure representative data when storage affects an important workload.
Characters Are Not Bytes
Unicode encodings can use multiple bytes for one character. A declared character limit does not necessarily equal an identical byte limit, and product semantics differ. Index limits and row-size limits may be measured in bytes even when the column limit is expressed in characters.
This distinction affects both CHAR and VARCHAR. A VARCHAR(100) column may hold one hundred characters whose storage exceeds one hundred bytes. Validate multilingual and emoji data rather than testing only ASCII.
When CHAR Is Appropriate
CHAR is strongest when fixed width belongs to an external or internal specification: ISO country codes, ISO currency codes, uniformly encoded status codes, or fixed-width machine identifiers. It can also align naturally with legacy fixed-record integrations, although file width and database character width must be verified separately.
Even then, length alone does not validate meaning. CHAR(2) accepts many invalid country codes. Add a foreign key to a reference table or an appropriate constraint.
When VARCHAR Is Appropriate
VARCHAR should usually be used for names, email addresses, phone numbers, postal codes, addresses, product names, URLs, and variable business identifiers. These values have maximum limits but not one stable width. Variable length reflects their actual domain and avoids padding concerns.
VARCHAR is not synonymous with unstructured or unlimited. Its declared maximum should be realistic, and business constraints still apply. A status column that permits twenty characters should also restrict valid statuses.
Names, Addresses, and Descriptions
Human names and addresses vary across languages and cultures, so CHAR is usually inappropriate. Padding a short name to the longest allowed length wastes capacity and can introduce confusing spaces. Descriptions are even more variable and may require VARCHAR or a large-text type depending on expected size.
Do not force cultural assumptions into small limits based on sample data. Select Unicode-capable storage, define defensible maxima, and preserve punctuation, accents, and meaningful internal spaces.
Phone Numbers and Postal Codes
Phone numbers are identifiers, not arithmetic quantities. They can contain country prefixes, leading zeros, extensions, and formatting. Their lengths vary, making VARCHAR more appropriate than CHAR or a numeric type in most systems.
Postal codes also vary by country and can contain letters, spaces, and leading zeros. Store them as variable text unless a table is explicitly limited to one stable national format. Country-specific validation belongs alongside the value.
Product and Business Codes
A product code should use CHAR only if its format guarantees one width for all current and future values. Many codes look fixed in early data but later gain prefixes, separators, or longer sequences. VARCHAR handles that evolution more safely.
For controlled codes, normalization, uniqueness, case rules, and character constraints often matter more than fixed versus variable storage. Document the protocol before selecting the SQL type.
CHAR(1) Flags and Boolean Data
A one-character flag such as 'Y' or 'N' can use CHAR(1), but a native boolean often expresses the meaning more directly. If a character flag is required for compatibility, constrain its values.
is_active CHAR(1) NOT NULL
CHECK (is_active IN ('Y', 'N'))
Without the constraint, CHAR(1) also accepts unrelated characters. Storage shape is not domain validation.
Length Limits and Oversized Values
Both types reject or truncate input that exceeds the declared width, depending on database and mode. Applications must never depend on silent truncation. A shortened email, code, or security value can become invalid or refer to a different entity.
Choose limits from standards and business requirements, validate before assignment, and test one character beyond each boundary. Include multibyte data because byte and character constraints can fail at different points.
Constraints Define Meaning
Neither type knows that a field is a country, currency, status, or product code. Add NOT NULL, CHECK, UNIQUE, or foreign keys to express those rules.
CREATE TABLE order_statuses (
status_code VARCHAR(20) PRIMARY KEY,
display_name VARCHAR(80) NOT NULL
);
ALTER TABLE orders
ADD CONSTRAINT fk_order_status
FOREIGN KEY (status_code) REFERENCES order_statuses(status_code);
A reference table is useful when controlled values need descriptions, ordering, lifecycle flags, or localization.
Performance Is Workload-Specific
CHAR can simplify fixed-width processing in some engines, while VARCHAR reduces row width when values vary. Smaller rows can improve cache density and I/O. Yet modern performance depends on indexes, query plans, data distribution, compression, and access patterns far more than a universal CHAR-versus-VARCHAR rule.
Do not select CHAR for variable data on the assumption that it is always faster. Model the domain correctly, then benchmark a representative workload if the choice materially affects performance.
Row Width and Memory
Wide fixed columns can reduce rows per page and increase scan, cache, sort, hash, and network costs. VARCHAR stores variable content more compactly but adds length metadata and can affect row movement when values grow. Product-specific storage options can alter both patterns.
These tradeoffs matter at scale, but they do not justify misleading schema semantics. A small fixed code can remain CHAR, while a mostly short but occasionally long description should remain variable.
Indexing CHAR and VARCHAR
Both types can be indexed for equality, ordering, joins, and uniqueness. Index size depends on encoded key width and actual values, with VARCHAR often producing smaller keys when content is short. Large maximum lengths may exceed product index-key limits even when current values are small.
Collation affects key order and equality. Prefix searches can often use an index, while expressions such as LOWER(column) may require a matching functional index or normalized column. Type choice does not replace an indexing strategy.
Primary and Foreign Keys
A fixed standard code can be a reasonable CHAR primary key, and a stable variable code can be a VARCHAR key. Long or mutable business text is usually a poor key because it enlarges related indexes and propagates changes. A surrogate key may be preferable.
Foreign-key columns must match the parent in type, length, character set, and collation closely enough for the product. Using CHAR in one table and VARCHAR in another for the same identifier can create padding, conversion, constraint, and performance problems.
Case Sensitivity and Collation
Case sensitivity is not determined by CHAR or VARCHAR. It is controlled primarily by collation and database behavior. The same VARCHAR values may compare equal under one collation and distinct under another. Accent and locale rules can also affect equality and ordering.
Choose comparison semantics from the domain. Usernames, product labels, machine tokens, and country codes can require different rules. A unique constraint follows those semantics, so test case variants before deployment.
NULL and Empty Strings
NULL represents a missing or unknown value. Padding does not turn NULL into spaces, and neither CHAR nor VARCHAR changes NULL into an empty string. An empty string is a known value with zero characters, although Oracle traditionally treats empty character strings as NULL.
Optional text commonly uses nullable VARCHAR. Use CHAR only when a present value is fixed width. Avoid defaulting every optional field to an empty string because that can hide missing information and complicate portability.
TRIM and Normalization
TRIM can remove surrounding spaces, but applying it to every query treats a symptom rather than defining input policy. Normalize fixed identifiers at entry, preserve meaningful human text, and store a separate canonical form when search needs differ from display.
CHAR does not mean uppercase, and VARCHAR does not preserve a business-specific case rule automatically. Add validation or canonicalization explicitly where the protocol requires it.
Unicode CHAR and VARCHAR Variants
SQL Server commonly distinguishes CHAR/VARCHAR from Unicode NCHAR/NVARCHAR. PostgreSQL uses database encoding for its regular character types. MySQL behavior depends on the selected character set and collation. Therefore, VARCHAR does not inherently mean ASCII, and NVARCHAR is not a universal cross-database name.
Choose an encoding that covers all expected scripts and emoji, configure connections consistently, and test round trips. The fixed-versus-variable decision remains separate from the Unicode decision.
Length and Storage Functions
Function semantics vary. SQL Server LEN commonly excludes trailing spaces, while DATALENGTH reports bytes. MySQL distinguishes CHAR_LENGTH from byte-oriented LENGTH. PostgreSQL length on text reports characters.
Padding makes these differences especially visible for CHAR. Use the function aligned with the rule being checked and test both trailing spaces and multibyte characters.
Fixed Values That Should Not Use CHAR
A date rendered as YYYY-MM-DD always appears to have ten characters, but it should use a native date type. A fixed-format price should use an exact numeric type. A UUID often has a fixed textual representation, but a native UUID type can validate it and store it more efficiently.
Fixed visual width is not enough. First select the type that represents semantics; only choose CHAR when the value is genuinely text and fixed width.
Hashes, Tokens, and Password Outputs
A fixed-length encoded hash can fit CHAR when the algorithm and encoding guarantee a stable width. Binary storage may be more compact for raw bytes. Variable security tokens usually need VARCHAR with exact case-sensitive comparison.
Password hashes are structured outputs whose lengths can change when algorithms are upgraded. VARCHAR is often safer than CHAR for encoded password-hash formats. Passwords themselves must never be stored in plaintext.
VARCHAR Versus TEXT
VARCHAR is bounded variable text. TEXT or CLOB types support large content and can have different indexing, storage, comparison, and driver behavior by database. A description may use VARCHAR when it has a meaningful modest limit or TEXT when it is genuinely document-like.
CHAR is not a practical alternative to large text. Declaring an enormous fixed width would misrepresent the domain and waste resources. Use each type family for its intended scale.
PostgreSQL Behavior
PostgreSQL supports character(n), character varying(n), and text. CHAR values are blank padded, while VARCHAR enforces a maximum. VARCHAR without a length and TEXT can store variable content without a declared small limit under implementation limits.
For ordinary variable text, PostgreSQL does not provide a general performance advantage to bounded VARCHAR over TEXT; the bound is primarily a validation decision. Fixed CHAR should still be reserved for fixed-width domains.
MySQL Behavior
MySQL stores CHAR as fixed length and VARCHAR as variable length, with behavior influenced by character set, SQL mode, row format, and version. Trailing-space handling has product-specific details, and multibyte character sets affect byte capacity and index limits.
Select full Unicode support when required and verify strict handling of oversized values. Do not assume behavior from another database carries over unchanged.
SQL Server Behavior
SQL Server provides CHAR, VARCHAR, NCHAR, and NVARCHAR, plus MAX forms for large values. Data type precedence and implicit conversions can affect index use when parameters or joined columns use incompatible Unicode types.
Match parameter types to columns, use explicit lengths in application mappings, and verify collation. A Unicode literal and a non-Unicode column can create different behavior than expected.
Oracle and SQLite Behavior
Oracle commonly uses CHAR and VARCHAR2, with CHAR fixed and blank padded. VARCHAR2 is preferred for variable text. Oracle's empty-string-as-NULL behavior is an important portability concern. Length semantics can be configured in bytes or characters in relevant contexts.
SQLite uses dynamic type affinity and does not enforce CHAR and VARCHAR length declarations like a traditional strongly typed engine. Applications needing strict limits should add explicit checks and tests.
Cross-Database Portability
The fixed-versus-variable concept is portable, but padding, trailing-space comparison, empty strings, length units, Unicode names, and maximum sizes are not fully uniform. Migration scripts should map intent rather than perform blind textual replacement.
A portable schema documents each column's semantic length, encoding, comparison, and validation rule. Integration tests then confirm the target database implements the required behavior.
Application and ORM Mappings
Most application languages map both CHAR and VARCHAR to their normal string type, which can hide the database distinction. A driver may return a padded CHAR value exactly as stored, trim it, or expose product-specific behavior. Application code that compares the returned string, calculates a hash, writes a fixed file, or serializes JSON must know whether trailing spaces remain.
ORM definitions should state column type and length explicitly when schema generation is used. A generic string property can otherwise become a framework-default VARCHAR whose length does not match the database, or a migration tool may repeatedly propose changing CHAR to VARCHAR. Validate the generated DDL rather than assuming annotations express the intended physical schema.
Domain models can protect the distinction. A currency-code value object can normalize case, validate exactly three permitted characters, and bind to CHAR(3). A customer-name property can retain variable Unicode content and bind to a bounded VARCHAR. This keeps padding details out of ordinary business logic while preserving meaningful rules.
Parameter Binding and Implicit Conversion
Bind parameters using a type compatible with the target column. A Unicode application parameter compared with a non-Unicode column, or a very wide VARCHAR parameter compared with a narrow indexed column, can cause implicit conversion and alter index use in some products. The same risk appears in joins between related columns that were declared differently.
SELECT order_id
FROM orders
WHERE currency_code = ?;
-- Bind as the database's compatible three-character text type.
Parameterization protects query structure, but it does not guarantee an ideal parameter definition. Inspect driver metadata and query plans for important lookups. Explicit conversion can clarify a boundary, but converting the indexed column on every row is usually less desirable than binding correctly.
Fixed-Width Files and External Protocols
A fixed-width file format is one of the clearest reasons to consider CHAR, but file positions and database character length are not automatically equivalent. A file specification may define byte offsets, while a Unicode database column counts characters. One multibyte character can break a byte-oriented record even though it fits the SQL declaration.
Separate the external record contract from the internal model when necessary. An import process can decode and validate file fields before storing them in the most appropriate database type. A fixed ten-byte field containing a variable business name does not make the internal name a fixed-length domain; it only describes one transport format.
Export tests should verify exact bytes, padding character, encoding, line endings, and truncation rules. Never assume the database's implicit CHAR padding produces a compliant external record.
When Fixed Codes Evolve
A code that is fixed today may evolve. A two-character internal region code might expand after an acquisition, or a product identifier may gain a prefix. CHAR makes the current fixed-width assumption visible, which is valuable, but it also means the schema must change when the contract changes.
Before choosing CHAR, identify who owns the standard and whether width is guaranteed. External ISO codes have stronger stability than an undocumented local convention. If the organization controls the code and anticipates new formats, VARCHAR with a constraint may preserve flexibility while still enforcing current allowed values through a lookup table.
Do not add spaces or placeholder characters merely to force variable codes into one width. That creates artificial values, complicates integrations, and transfers padding rules into every consumer.
Search, Reporting, and Data Warehouses
Operational schemas and analytical platforms can make different choices. A warehouse may ingest padded CHAR fields from several sources, but grouping those fields without normalization can split logically identical categories under some engines. A staging layer should preserve source values for audit while producing a canonical value for joins and reporting.
Report exports can expose trailing spaces that interactive SQL tools hide. They can also render blank fixed-width values as if they were empty. Include visible delimiters or length diagnostics during validation, and compare canonical keys rather than presentation labels.
Columnar storage and compression can reduce the physical penalty of repeated fixed values, but that does not change semantics. Select CHAR or VARCHAR in the model based on the contract, then let the analytical engine optimize storage.
Auditing and Operational Observability
Production controls should track more than SQL errors. Monitor rejected oversized values, values at the maximum length, unexpected surrounding whitespace, decoding replacement characters, case variants of controlled codes, and joins that fail because related identifiers differ. These signals reveal contract drift before it becomes widespread corruption.
For CHAR columns, compare trimmed semantic length with declared width. A surge of shorter inputs may indicate missing content that padding is hiding. For VARCHAR columns, a steady increase in upper-percentile length can indicate that the chosen maximum will soon be inadequate. Keep metrics aggregated where raw text contains personal or secret data.
When normalization changes a value, retain enough provenance to diagnose the source without indefinitely duplicating sensitive input. Document whether trimming and case conversion occur in the client, service, import job, or database so each layer does not apply a different transformation.
Design Review Scenarios
Consider an employee badge defined by the issuing system as exactly eight uppercase characters. CHAR(8), an uppercase-format check, and a unique constraint form a coherent design. By contrast, an eight-character limit in the current user interface does not prove that an employee display name should be CHAR(8); the name remains variable text.
A shipping carrier may document service codes as two or three characters. Even though the set is small and controlled, VARCHAR(3) is more accurate than CHAR(3) because valid widths differ. A foreign key to a carrier-service table enforces meaning without inventing a trailing blank.
A hexadecimal digest from one fixed algorithm may use CHAR at its guaranteed encoded width. If the system must support algorithm migration, a variable encoded value plus an algorithm column may be safer. These examples show that lifecycle and ownership are part of type selection.
Real-World Schema Example
CREATE TABLE orders (
order_id BIGINT PRIMARY KEY,
country_code CHAR(2) NOT NULL,
currency_code CHAR(3) NOT NULL,
customer_name VARCHAR(200) NOT NULL,
postal_code VARCHAR(20),
status_code VARCHAR(20) NOT NULL,
CHECK (status_code IN ('NEW', 'PAID', 'SHIPPED', 'CANCELLED'))
);
The country and currency codes follow fixed standards. Customer names, postal codes, and statuses vary in length. Status meaning is enforced separately rather than assumed from its maximum width.
Migration Between CHAR and VARCHAR
Before changing CHAR to VARCHAR, inspect padding, application trimming, exports, joins, keys, and uniqueness. Decide whether trailing spaces should be removed. Before changing VARCHAR to CHAR, prove every valid value has one stable width and profile rows that are shorter or longer.
Update related foreign keys, ORM metadata, stored procedures, API contracts, and file layouts together. Test values at boundaries and compare query plans before and after the migration.
Data Quality and Monitoring
Monitor rejected and truncated values, actual length distributions, leading and trailing whitespace, invalid code formats, NULL and empty-string rates, and encoding failures. A growing maximum can signal an upstream contract change before the declared limit causes an outage.
For CHAR columns, monitor values that are shorter than the semantic width before padding or normalization. For VARCHAR, watch values consistently near the maximum. These measures turn type decisions into observable contracts.
Common Misconceptions
- VARCHAR does not reserve its full maximum for every value.
- CHAR is not always faster than VARCHAR.
- CHAR does not make content uppercase or valid.
- VARCHAR does not automatically mean Unicode or unlimited text.
- A known maximum does not make a value fixed length.
VARCHAR(100)does not universally mean one hundred bytes.- CHAR padding should not be used as business validation.
- Case sensitivity comes mainly from collation, not these type names.
- Fixed display format does not justify storing dates or numbers as CHAR.
- Current sample width is not proof of the future domain.
Decision Process
- Confirm that the value is genuinely textual.
- Determine whether every valid value has exactly one stable width.
- Use CHAR only when fixed width belongs to the domain or protocol.
- Otherwise use VARCHAR with a defensible maximum.
- Select encoding and Unicode support independently.
- Define collation, case, accent, and whitespace rules.
- Add constraints that validate content and relationships.
- Align primary keys, foreign keys, and bound parameters.
- Test storage, comparison, indexes, imports, and APIs.
- Measure performance only with representative data and queries.
Testing Checklist
Test minimum, typical, and maximum lengths; one character beyond the maximum; NULL; empty strings; leading and trailing spaces; mixed case; accents; multilingual scripts; emoji; and values requiring multiple bytes. Verify insert, update, comparison, ordering, grouping, unique constraints, and joins.
For CHAR, inspect actual returned values and length functions through the production driver. For VARCHAR, verify that application and database limits agree. Include imports, exports, ORM schema generation, and API round trips.
Interview Questions and Answers
What is the main difference between CHAR and VARCHAR? CHAR represents fixed-length text and is commonly padded, while VARCHAR stores variable-length text up to a maximum.
Which uses more storage? It depends on declared width, actual values, encoding, row format, and compression; CHAR often wastes width when values vary.
Is CHAR always faster? No. Performance is database- and workload-specific, and reduced VARCHAR row width can be beneficial.
Which type should store phone numbers? Usually VARCHAR because phone identifiers vary in length and can contain nonnumeric characters.
Which type suits a three-letter currency code? CHAR(3), supported by a reference or validation constraint.
Does VARCHAR(100) allocate one hundred characters for every row? Normally no; it declares a maximum while storage mainly follows actual length.
Does CHAR validate exact content? No. It controls width; constraints validate allowed values.
Key Takeaway
CHAR and VARCHAR differ primarily in the promise they make about length. CHAR says width is fixed and normally pads short input. VARCHAR says content length varies up to a documented maximum. That semantic distinction should drive the choice before performance folklore.
CHAR OR VARCHAR = TEXTUAL SEMANTICS
+ FIXED WIDTH OR MAXIMUM WIDTH
+ PADDING AND COMPARISON RULES
+ ENCODING, COLLATION, AND CONSTRAINTS
Use CHAR for genuinely fixed-width textual standards and VARCHAR for most ordinary text. Then define validation, Unicode support, collation, whitespace, indexes, and cross-layer mappings explicitly so the chosen type behaves consistently throughout the system.