What is the difference between CHAR(10) and VARCHAR(10), and when is CHAR the better choice?
answer
- One of the two always occupies its full width
- Think about what happens to a short value
- The extra characters are spaces, and they persist
- Comparison and concatenation see the padding
- Fixed-width codes are the honest use case
basics
~10 sCHAR(10) is fixed length: shorter values are blank-padded to ten characters. VARCHAR(10) is variable length with ten as a maximum. Choose CHAR only for genuinely fixed-width codes; VARCHAR for everything else.
solid answer
~50 s`CHAR(n)` declares a *fixed-length* string: a value shorter than `n` is padded with spaces to exactly `n` characters, and that padding is part of the stored value. `VARCHAR(n)` declares a *variable-length* string whose length may be anything up to `n`, stored as given. In both cases `n` is a declared constraint on the data, not a hint — inserting a longer string raises a truncation error under standard semantics. The practical consequence of padding is comparison and concatenation surprises: `'US '` coming back from a `CHAR(5)` column may or may not compare equal to `'US'` depending on the collation's PAD SPACE or NO PAD attribute, and it will concatenate with the spaces attached. So reach for `CHAR` only when every value truly has the same width — ISO 4217 currency codes as `CHAR(3)`, ISO 3166 country codes as `CHAR(2)` — and use `VARCHAR` for names, emails, descriptions and anything user-entered.
code
sql · 9 linesCREATE TABLE price_list (
sku VARCHAR(64) NOT NULL, -- variable, supplier-issued
currency_code CHAR(3) NOT NULL, -- always exactly 3, ISO 4217
amount DECIMAL(12,2) NOT NULL
);
-- The padding is part of a CHAR value:
-- CHAR_LENGTH(currency_code) is 3 even for a 2-character input,
-- and currency_code || '/' yields e.g. 'US /' if 'US' were stored.go deeper
Be able to state it in one line — CHAR is fixed length and blank-padded, VARCHAR is variable up to a maximum — and give one honest CHAR use case such as a two-letter country code.
Explain what the padding does to comparison, CHAR_LENGTH and concatenation, and that the declared length is a constraint that raises a truncation error rather than a hint the engine may ignore.
Show the judgment: size VARCHAR from the domain, keep CHAR for standardised fixed-width codes, and be wary of collation-dependent trailing-space comparison on anything used as a join key or lookup value.
Own the convention across a schema — how widths are chosen and reviewed, where unbounded text types are allowed, and the collation and character-set decisions that make string comparison behave the same in every service touching the data.
## The declared difference Standard SQL gives you two character string types with a length limit. `CHARACTER(n)`, spelled `CHAR(n)`, is fixed length: the column always holds exactly `n` characters, and a shorter value is blank-padded on the right until it reaches `n`. `CHARACTER VARYING(n)`, spelled `VARCHAR(n)`, is variable length: it holds between zero and `n` characters, exactly as supplied. So `INSERT ... VALUES ('US')` into a `CHAR(5)` column stores `'US '` — the value itself now has five characters. The same insert into `VARCHAR(5)` stores `'US'`. ## What `n` actually constrains `n` is a data rule, the same kind of declaration as `NOT NULL`. It is not a tuning knob and not a hint. Under standard assignment rules, storing a string longer than `n` raises an error (`string data, right truncation`), with one carve-out: if the excess characters are all spaces, they may be trimmed silently. Engines have varied here historically — MySQL outside strict mode truncated with a warning rather than raising — so never rely on the column length as your only validation for user input. The unit of `n` is also worth knowing. The standard counts characters, and most engines count characters for Unicode character sets, but some let you declare the limit in bytes instead. If your data can contain multi-byte characters, confirm which your engine means before sizing a column so that a legitimate value cannot be rejected. ## Why the padding matters Padding is not cosmetic — it is in the value: - **Concatenation** carries it: `country_code || '-' || region` on a `CHAR(5)` column produces `'US -NE'`. - **Length functions** report the padded length for `CHAR`, so `CHAR_LENGTH(country_code)` returns 5, not 2. - **Comparison depends on collation.** SQL collations carry a PAD SPACE or NO PAD attribute. Under PAD SPACE the shorter operand is conceptually blank-extended before comparing, so `'US'` and `'US '` compare equal; under NO PAD they do not. Different engines, and different collations within one engine, land on different sides of this. That is exactly the kind of behaviour you do not want a join key or a lookup predicate to depend on. - **Round-tripping** through an application then back into the database can persist the padding into other columns, or trip an equality check the second time around. ## When to choose CHAR Choose `CHAR(n)` when every value is genuinely `n` characters wide and always will be. Standardised codes are the clean examples: ISO 4217 currency codes (`CHAR(3)`), ISO 3166 alpha-2 country codes (`CHAR(2)`), a fixed-length check digit or status code. Here `CHAR` doubles as documentation and as a light constraint — a two-character country column cannot accidentally hold a full country name. Everything else is `VARCHAR`: names, emails, addresses, titles, identifiers issued by other systems, anything a human types. The width there is inherently variable and the padding buys you nothing but surprises. A common false reason to pick `CHAR` is performance. Claims about fixed-width rows being faster are storage-engine specific, change between engines and versions, and are dominated by other factors in almost every real workload. Pick the type that describes the data; leave physical layout to the engine. ## Sizing VARCHAR, and unbounded text Size `VARCHAR` from the domain: what is the longest legitimate value, plus headroom for the ones you have not seen? Too tight and you reject valid data; absurdly wide and the declaration stops carrying information. For genuinely unbounded text — a description, a comment body — the standard type is `CHARACTER LARGE OBJECT` (`CLOB`), and engines offer their own unbounded text types; picking one is a portability decision, so make it deliberately rather than by writing `VARCHAR(100000)`. ## The short answer to give `CHAR` fixed and padded, `VARCHAR` variable and exact, `n` is a constraint in both, padding leaks into comparisons and concatenation, so `CHAR` earns its place only for fixed-width codes.
- What happens if you insert an eleven-character string into a VARCHAR(10) column?Under standard assignment rules it raises a truncation error rather than silently cutting the value, because the declared length is a constraint on the data. The one exception is when the excess characters are all spaces, which may be trimmed. Engines have differed historically — MySQL outside strict mode truncated with a warning — so validate input rather than relying on the column width to reject it.
- Is CHAR faster than VARCHAR because rows are fixed width?Not in any way you should design around. Whether fixed-width rows help depends on the storage engine, the row format and the version, and the effect is dwarfed by indexing, row counts and access patterns in real workloads. Choose the type that describes the data; padding semantics are a permanent correctness concern, while the layout benefit is speculative and engine-specific.
saying these in an interview costs you the question
- Says CHAR and VARCHAR differ only in how bytes are stored
- Believes VARCHAR(255) is special or optimal
- Claims trailing spaces from a CHAR column always compare away
- Picks CHAR for names because it is supposedly faster
- Treats the declared length as a hint rather than a constraint