Detailed explanation
CHAR, VARCHAR, and TEXT store character data, but they differ in length rules, padding, and database-specific storage and indexing behaviour. The practical choice depends on whether values have a constant size, whether a meaningful maximum length exists, and how you will search the column. Interviewers usually expect these distinctions and suitable examples, rather than the claim that one type is always faster.
CHAR(n) represents a fixed-length string and generally pads shorter values with spaces to the declared length. For example, state CHAR(2) suits two-character codes such as 'TN'. However, CHAR(2) does not by itself guarantee that users supply exactly two meaningful characters: shorter inputs may be padded. Use a CHECK constraint or a reference table when validation matters. Trailing-space behaviour during retrieval and comparison varies by database, so avoid assuming spaces are always preserved or always ignored. Fixed length also does not automatically make CHAR faster.
VARCHAR(n) stores variable-length strings up to a declared limit, without padding them to that limit. An email VARCHAR(120), for example, can hold addresses of different lengths while enforcing a chosen size boundary. Storage generally includes the actual encoded data plus length or row-management overhead, so saying it uses exactly one byte per character is incorrect. Unicode encoding affects byte usage, and the meaning of n is database-dependent: PostgreSQL counts characters, whereas SQL Server VARCHAR(n) specifies a byte limit. Choose limits from application requirements, not arbitrary guesses.
TEXT commonly represents variable-length free-form content, such as bio TEXT, without a small application-defined limit. It is not universally stored off-page or universally unindexable: storage location can depend on value size, row size, compression, and database rules. PostgreSQL treats TEXT and VARCHAR similarly for storage and performance; MySQL has several TEXT sizes and requires prefixes for ordinary indexes on TEXT columns; SQL Server's legacy TEXT type is deprecated in favour of VARCHAR(MAX) or NVARCHAR(MAX). For interviews, explain the general distinction first, then qualify engine-specific details and mention that full-text search differs from ordinary indexing.
Key points to remember
- CHAR(n) is fixed-length and generally space-padded; use it for genuinely constant-size values.
- VARCHAR(n) supports variable-length values with a declared maximum and storage overhead.
- TEXT suits long free-form content, but its limits and behaviour depend on the database.
- Padding, Unicode byte usage, indexing, and off-page storage are engine-specific details.
- Choose types using data requirements and query patterns; enforce additional rules with constraints.
Common mistakes candidates make
- Claiming CHAR is always faster or that VARCHAR consumes exactly one byte per stored character.
- Saying TEXT is always stored off-page and cannot be indexed.
- Assuming CHAR(n) enforces exactly n meaningful characters or that n always means characters across databases.
Likely follow-up questions
Does CHAR(2) guarantee a valid two-letter state code?
No; shorter inputs may be padded, and arbitrary two-character values can still be accepted. Use a foreign key to a state-code table or an appropriate CHECK constraint to enforce validity.
Can a TEXT column be indexed?
It depends on the database and index type: PostgreSQL supports ordinary indexes on TEXT subject to index-entry limits, while MySQL requires a prefix for ordinary TEXT indexes. For word-based document searches, consider the database's full-text indexing features.
Should I use TEXT instead of VARCHAR everywhere?
Not automatically: VARCHAR(n) expresses and enforces a useful maximum when your requirements define one. TEXT is appropriate when that limit is unnecessary, but check your database's indexing, storage, and compatibility rules.
Interview tip: Start with the three length behaviours, give the state/email/bio examples, and then add one database-specific caveat.