What is the difference between CHAR and VARCHAR?

In SQL databases, both CHAR and VARCHAR are used to store character strings. However, they differ significantly in how they handle storage, length, and performance, which impacts their appropriate use cases.

Key Differences

CHAR (Character) is a fixed-length string data type. When you declare a CHAR column with a specific length (e.g., CHAR(10)), the database will always allocate exactly that much space, regardless of the actual length of the string stored. If a string shorter than the declared length is inserted, it will be right-padded with spaces to fill the allocated space. These trailing spaces are usually removed when retrieving data, but they consume storage.

VARCHAR (Variable Character) is a variable-length string data type. When you declare a VARCHAR column with a maximum length (e.g., VARCHAR(255)), the database allocates only the space required for the actual string stored, plus a small overhead (typically 1 or 2 bytes) to store the string's length. No space padding occurs, making it more efficient for storing strings of varying lengths.

Storage Implications

For CHAR, storage is always constant. A CHAR(10) column will always consume 10 bytes (or characters, depending on character set) per row, even if you store 'A'. For VARCHAR, storage varies. A VARCHAR(255) column storing 'A' would consume 1 byte for 'A' plus 1-2 bytes for length metadata, typically resulting in 2-3 bytes total. This makes VARCHAR more memory-efficient when the actual data length is often less than the maximum possible length.

Performance Implications

CHAR columns can sometimes offer slight performance advantages for operations involving fixed-length strings (e.g., comparisons, indexing) because the database knows the exact size of each entry upfront, simplifying memory management and access patterns. However, this advantage is often negligible in modern database systems. VARCHAR, while having a small overhead for length storage, is generally efficient and preferred for most variable-length text.

When to Use CHAR

• When the string data has a strictly fixed and known length (e.g., country codes like 'US', 'GB', 'FR').
• When consistency in storage size is paramount, even if it means some wasted space.
• For very short strings where the overhead of VARCHAR's length byte might be proportionally significant (though this is rare).

When to Use VARCHAR

• For almost all variable-length string data (e.g., names, addresses, descriptions, comments).
• When you want to conserve storage space, as it only uses space for the actual data stored.
• When the length of the data can vary significantly and unpredictably.

Summary Table