VARCHAR vs TEXT vs NVARCHAR: Choosing the Right String Type

Posted by Kyle Hankinson July 8, 2024

---

Every database has multiple string types, and choosing the wrong one can waste storage, break character support, or limit query performance.

PostgreSQL

PostgreSQL makes it simple — use TEXT for almost everything:

Type	Max Length	Notes
TEXT	Unlimited	Preferred general-purpose type
VARCHAR(n)	n characters	Same performance as TEXT
CHAR(n)	Fixed n characters	Pads with spaces, rarely useful

There is no performance difference between TEXT and VARCHAR(n) in PostgreSQL. VARCHAR(n) only adds a length check constraint. The PostgreSQL documentation recommends TEXT or VARCHAR without a length limit.

CREATE TABLE users (
    name TEXT NOT NULL,
    email TEXT NOT NULL
);

MySQL

MySQL's string types differ in storage and maximum length:

Type	Max Length	Storage
TINYTEXT	255 bytes	Length + 1 byte
TEXT	65,535 bytes	Length + 2 bytes
MEDIUMTEXT	16 MB	Length + 3 bytes
LONGTEXT	4 GB	Length + 4 bytes
VARCHAR(n)	65,535 bytes (row limit)	Length + 1-2 bytes
CHAR(n)	255 characters	Fixed n bytes

Key difference: VARCHAR columns can be fully indexed, but TEXT columns can only be indexed with a prefix length:

-- Works
CREATE INDEX idx_email ON users (email(255));

-- Does not work (no prefix)
-- CREATE INDEX idx_email ON users (email);  -- Error if email is TEXT

For columns you will index or use in WHERE clauses, prefer VARCHAR(n). For large content (blog posts, descriptions), use TEXT or MEDIUMTEXT.

SQL Server

SQL Server's big distinction is Unicode support:

Type	Max Length	Character Set
VARCHAR(n)	8,000 bytes	Non-Unicode (1 byte/char)
VARCHAR(MAX)	2 GB	Non-Unicode
NVARCHAR(n)	4,000 characters	Unicode (2 bytes/char)
NVARCHAR(MAX)	2 GB	Unicode
CHAR(n)	8,000 bytes	Fixed, non-Unicode
NCHAR(n)	4,000 characters	Fixed, Unicode
TEXT	2 GB	Deprecated — use VARCHAR(MAX)

Use NVARCHAR if your data may contain non-English characters (names, addresses, user-generated content). Use VARCHAR only when you are certain the data is ASCII-only and storage is a concern.

-- Good default
CREATE TABLE users (
    name NVARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL  -- emails are ASCII
);

Oracle

Type	Max Length	Notes
VARCHAR2(n)	4,000 bytes (or 32,767 with MAX_STRING_SIZE)	Standard string type
NVARCHAR2(n)	4,000 bytes	Unicode variant
CHAR(n)	2,000 bytes	Fixed length
CLOB	4 GB	For large text

Always use VARCHAR2, not VARCHAR. Oracle's documentation states that VARCHAR may behave differently in future versions. VARCHAR2 is the guaranteed type.

CREATE TABLE users (
    name VARCHAR2(100) NOT NULL,
    bio CLOB
);

Decision Guide

Scenario	MySQL	PostgreSQL	SQL Server	Oracle
Short strings (names, emails)	VARCHAR(n)	TEXT	NVARCHAR(n)	VARCHAR2(n)
Long content (articles, bios)	TEXT or MEDIUMTEXT	TEXT	NVARCHAR(MAX)	CLOB
ASCII-only data	VARCHAR(n)	TEXT	VARCHAR(n)	VARCHAR2(n)
Unicode data	VARCHAR(n) with utf8mb4	TEXT (always UTF-8)	NVARCHAR(n)	NVARCHAR2(n)

Common Mistakes

Using CHAR for variable-length data: CHAR(50) pads shorter values with spaces, wasting storage and causing comparison surprises.

VARCHAR(255) by default: Many ORMs default to 255, but there is no magic about this number (it was the max for MySQL's old TINYTEXT). Choose a length that reflects your actual data constraints.

Ignoring character sets in MySQL: VARCHAR(100) with latin1 holds 100 bytes. With utf8mb4, a character can be up to 4 bytes, so the row size limit is reached sooner.

---