departments.name has been VARCHAR(50) since Module 7's create-table lesson, without ever explaining why VARCHAR specifically, or what the alternatives even were. This lesson covers CHAR, VARCHAR, and the large-text type every database uses instead of them once a value could run long, including a direct answer to a real, common question: what database actually has a type called LONGVARCHAR? (none of them.)
Learning Objectives
- Distinguish CHAR (fixed-length) from VARCHAR (variable-length), and know when fixed-length genuinely helps.
- Name each database's large-text type, and know that LONGVARCHAR isn't a real keyword in any of them.
- Pick between CHAR, VARCHAR, and the large-text type for a real column.
Text Value
'Engineering', or a 50,000-word resume
Fixed or Variable Length?
CHAR vs VARCHAR
Short or Large?
VARCHAR vs the large-text type
Matching Type
No wasted space, no silent truncation
CHAR vs VARCHAR
| CHAR(n) | VARCHAR(n) | |
|---|---|---|
| Storage | Always reserves exactly n characters, padding shorter values | Stores only the actual characters, plus a small length prefix |
| Best for | Values that are genuinely always the same length, a 2-letter state code | Everything else: names, emails, titles, free-form text |
| SQLite | Accepted, but not padded and not length-limited at all | Accepted, but not length-limited at all |
SQLite is the one real exception on this whole table: its type system accepts CHAR(n) and VARCHAR(n) syntactically, but enforces neither the padding CHAR implies nor the maximum length VARCHAR implies. A string of any length can be stored in a SQLite VARCHAR(10) column with zero truncation or error, the (10) is accepted and then completely ignored.
Maximum Lengths by Database
| Database | CHAR / VARCHAR Maximum |
|---|---|
| MySQL / MariaDB | CHAR up to 255 characters; VARCHAR up to 65,535 bytes, shared across the whole row |
| PostgreSQL | Both effectively unlimited in practice (over 10 million characters); VARCHAR with no length behaves like TEXT |
| SQL Server | CHAR/VARCHAR up to 8,000 bytes, or VARCHAR(MAX) up to 2GB; NCHAR/NVARCHAR store Unicode at half those character counts |
| Oracle | CHAR up to 2,000 bytes; VARCHAR2 up to 4,000 bytes (32,767 with extended data types enabled) |
| SQLite | No enforced maximum at all, regardless of what n is declared as |
Oracle's own column is written as VARCHAR2 deliberately, not VARCHAR: Oracle's documentation explicitly recommends VARCHAR2 over VARCHAR, since VARCHAR, while currently a synonym for it, is reserved for a future redefinition with different comparison semantics.
Large Text: the "LONGVARCHAR" Myth
LONGVARCHAR isn't a real keyword in MySQL, MariaDB, PostgreSQL, SQLite, SQL Server, or Oracle, it's a generic JDBC/ODBC type code that a database driver maps onto whichever actual large-text type that database uses underneath. It shows up constantly in generic database tools and interview questions, but no CREATE TABLE statement on any of these six accepts it as a real type name.
| Database | Actual Large-Text Type |
|---|---|
| MySQL / MariaDB | TEXT (64KB) / MEDIUMTEXT (16MB) / LONGTEXT (4GB) |
| PostgreSQL | TEXT, unbounded, the same type VARCHAR with no length becomes |
| SQL Server | VARCHAR(MAX) / NVARCHAR(MAX) (legacy TEXT/NTEXT are deprecated) |
| Oracle | CLOB, up to (4GB - 1) * the database's block size |
| SQLite | TEXT, the same type as VARCHAR, no separate large-text keyword needed |
Try It Yourself
Beacon Robotics wants to store an employee's full resume, potentially tens of thousands of characters, in a new resume column on employees. Write the column definition, on MySQL.
ALTER TABLE employees ADD COLUMN resume LONGTEXT;
Frequently Asked Questions
Why does CHAR still exist if VARCHAR can do everything CHAR can?
CHAR's fixed width makes every row the same byte size for that column, which some engines can scan slightly faster, and a genuinely fixed-format value, a 2-letter country code, a fixed product SKU prefix, is a legitimate real use case where every value really is the same length. For anything naturally variable in length, VARCHAR is the right default.
Does growing a VARCHAR(50) column to VARCHAR(200) later require rebuilding the whole table?
It varies, but growing (not shrinking) a VARCHAR is usually cheap: MySQL/MariaDB and PostgreSQL can often do it as a fast, mostly metadata-only change, and SQL Server/Oracle generally can too, ALTER TABLE employees MODIFY name VARCHAR2(200); on Oracle, for example. Shrinking a VARCHAR, or changing its underlying type, is far more likely to require a full table rewrite everywhere.
Common Beginner Mistakes
Writing LONGVARCHAR directly in a CREATE TABLE statement, expecting it to work
It's rejected on every database in this course, LONGVARCHAR is a generic JDBC type constant, not real SQL syntax anywhere. The actual keyword to reach for is LONGTEXT (MySQL/MariaDB), TEXT (PostgreSQL/SQLite), VARCHAR(MAX) (SQL Server), or CLOB (Oracle).
Using CHAR(n) for naturally variable-length text, like names or emails
This wastes storage padding every short value out to n characters for little benefit, and most databases' CHAR comparison/retrieval behavior around that padding is subtle enough to cause confusing bugs. VARCHAR is the correct default for anything that isn't genuinely fixed-width.
Best Practices
- Default to VARCHAR for variable-length text, reserve CHAR for genuinely fixed-width codes.
- Reach for the engine's dedicated large-text type once a column could realistically exceed a few hundred characters.
- Never assume a generic database tool's display name (like LONGVARCHAR) is something you can type directly into CREATE TABLE.
Interview Questions
Is LONGVARCHAR valid syntax in a MySQL CREATE TABLE statement?
No, LONGVARCHAR is a generic JDBC/ODBC type constant (java.sql.Types.LONGVARCHAR) used by driver layers and database tools, it isn't real MySQL syntax. The actual MySQL type for large text is LONGTEXT: resume LONGTEXT; is valid, resume LONGVARCHAR; is rejected outright.
What's the real difference between VARCHAR(50) and VARCHAR(255) in terms of storage?
On disk, a VARCHAR column in MySQL, PostgreSQL, SQL Server, and Oracle only uses as many bytes as the actual stored value needs, plus a small length prefix, the declared maximum doesn't waste space for unused capacity. The one real exception: MySQL's in-memory temporary tables (used internally for sorting and grouping) can allocate space based on the declared maximum rather than the actual value length, which is a genuine reason to avoid an unnecessarily large VARCHAR(255) when VARCHAR(50) would do.
Summary
CHAR pads every value to a fixed length, VARCHAR stores only what's actually there, and every database has its own separate large-text type (TEXT, LONGTEXT, VARCHAR(MAX), CLOB) for anything that could run long. LONGVARCHAR, despite showing up everywhere in generic tooling, is not a real keyword on any database in this course.
What's Next?
Date and Time Data Types is next: DATE, TIME, DATETIME, and TIMESTAMP are not the same thing everywhere, and SQL Server has a type literally named TIMESTAMP that has nothing to do with dates at all.
Ready to Practice SQL?
Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.
Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.