CHAR vs VARCHAR in SQL: Key Differences
💡 Concept Name
CHAR vs VARCHAR – These are SQL string data types. CHAR stores fixed-length strings, while VARCHAR stores variable-length strings.
📘 Quick Intro
CHAR(n) always uses n bytes, padding with spaces if needed. VARCHAR(n) uses only the necessary bytes to store characters, up to the defined maximum. Use CHAR for consistent-length data like country codes, and VARCHAR for variable-length input like names or emails.
🧠 Analogy / Short Story
Imagine CHAR as a row of fixed-size mailboxes—all are the same size, even if one contains a small letter. VARCHAR is like flexible mail slots—each expands to fit the letter size, saving space. CHAR ensures order, VARCHAR saves room.
🔧 Technical Explanation
- 📏 CHAR(n): Reserves exactly n bytes; pads with spaces if value is shorter.
- 📐 VARCHAR(n): Uses only the actual length of characters stored, up to n.
- 💾 CHAR may be slightly faster for fixed-size columns due to predictable size.
- 📉 VARCHAR is more space-efficient for variable or long strings.
- ⚠️ Be careful with trailing spaces in CHAR comparisons—they may affect query results.
🎯 Purpose & Use Case
- ✅ Use CHAR for fixed-size data like state abbreviations or country codes (e.g., 'US').
- ✅ Use VARCHAR for names, emails, comments, or any variable-length text.
- ✅ CHAR is slightly faster when all values are of similar length and used in indexes.
- ✅ VARCHAR saves storage space and improves efficiency for diverse text inputs.
💻 Real Code Example
-- CHAR stores 2 characters fixed
CREATE TABLE CountryCodes (
Code CHAR(2)
);
-- VARCHAR stores up to 50 characters, uses only what's needed
CREATE TABLE Users (
Name VARCHAR(50),
Email VARCHAR(100)
);
-- CHAR pads with spaces
INSERT INTO CountryCodes VALUES ('US'); -- Stored as 'US '
❓ Interview Q&A
Q1: What is the key difference between CHAR and VARCHAR?
A: CHAR stores fixed-length strings; VARCHAR stores variable-length strings up to the defined limit.
Q2: When should you prefer CHAR over VARCHAR?
A: When all values are of consistent, short length (like country codes or status flags).
Q3: Why does CHAR use more space than VARCHAR?
A: CHAR pads values with trailing spaces to match the defined length; VARCHAR does not.
Q4: Is there a performance difference?
A: CHAR may perform slightly better for fixed-size values, but VARCHAR is usually more efficient for general use.
Q5: What issue can trailing spaces in CHAR cause?
A: They can affect string comparison results and lead to unexpected filtering behavior.
📝 MCQs
Q1. Which data type always stores a fixed number of characters?
- CHAR
- VARCHAR
- TEXT
- STRING
Q2. Which is more space-efficient for variable-length strings?
- CHAR
- VARCHAR
- NCHAR
- TEXT
Q3. CHAR(4) stores 'AB'. How is it saved internally?
- 'AB'
- 'AB '
- ' AB'
- 'A B '
Q4. Which is better for storing country codes?
- VARCHAR
- CHAR
- TEXT
- NVARCHAR
Q5. What happens when you insert shorter data into a CHAR column?
- It fails
- It stores as-is
- It truncates
- It is padded with spaces
💡 Bonus Insight
Mixing CHAR and VARCHAR in queries may require using TRIM() or RTRIM() functions to avoid mismatches due to trailing spaces. Always consider expected string length before choosing between them.
📄 PDF Download
Need a handy summary for your notes? Download this topic as a PDF!