SQL-Char vs Varchar

In SQL, both CHAR and VARCHAR are used to store text or string data, but they differ in how they store the data and handle storage space.

CHAR (Fixed-length)

• Fixed length - When you define a column as CHAR(n), SQL reserves exactly n characters for that column. If the data stored is shorter than n, it will be padded with spaces.
• Storage - Since it always uses the same amount of space, CHAR(n) can be less efficient when storing strings of varying lengths.
• Use case - It is best used for columns that store data with a fixed length, such as phone numbers, country codes, or postal codes.

Example

CREATE TABLE example (
  zip_code CHAR(5) -- Always stores 5 characters, padding with spaces if necessary
);

VARCHAR (Variable-length)

• Variable length - When you define a column as VARCHAR(n), SQL will only use as much space as needed to store the string, up to the specified length n. No padding is added for shorter strings.
• Storage - VARCHAR is more space-efficient for columns where the length of the string varies.
• Use case - It is best used for storing strings where the length can vary, such as names, addresses, or descriptions.

Example

CREATE TABLE example (
  description VARCHAR(255) -- Variable length, stores data without padding
);

Key Differences

1. Storage efficiency
   • CHAR uses fixed space, even if the actual string is shorter.
   • VARCHAR only uses the space required for the string length, making it more efficient for variable-length strings.
2. Performance
   • CHAR might have a slight performance advantage for fixed-length strings because it avoids the need to calculate the actual string length.
   • VARCHAR is more flexible, but might have a small overhead for length tracking.
3. Padding
   • CHAR pads shorter strings with spaces.
   • VARCHAR does not add padding, storing exactly the characters entered.

Example

-- CHAR(5) will store 'ABC' as 'ABC  ' (with 2 trailing spaces)
-- VARCHAR(5) will store 'ABC' as 'ABC' without padding.