Difference Between CHAR and VARCHAR and VARCHAR2

CHAR and VARCHAR are the two widely used data types in the SQL language and database storage.

Both of the types are used to store the character string in the database table. And it makes more complex for the newbie to find the difference between CHAR and VARCHAR. Which one is good to use in SQL?

I will cover all the topics mentioned in the above content. At the end of this post, you will get answers to your every question.

Let’s begin…

Difference Between CHAR and VARCHAR

The main difference between the two types is the way they are used to store the data.

When you assign any data type to the column while creating a SQL table, each value in the column belongs to the same data type. In the case of CHAR and VARCHAR types, data are stored in character string format.

While creating a table in MySQL, CHAR and VARCHAR are declared with the maximum length (says 4 bytes) of the data type.

CHAR data type:

Each value stored in a CHAR type column will be having fix storage size of 4 bytes. If the string length is lower than the 4 bytes, extra spaces are padded at the end of the string (Right Padding).

VARCHAR data type:

Unlike CHAR, the storage size of the data stored in VARCHAR is not fixed. If the string length is lower than 4 bytes, it does not add any extra spaces and the size of the data is exactly the same as string length.

In simple word,

• CHAR is to store the fixed-length string.
• VARCHAR is to store the variable-length string.

Let’s make it simple to understand with an example given below.

The table below is to describe how VARCHAR is different from CHAR to store the same values (Alphabets, here).

The first row in the table: Even to store the empty string, CHAR takes 4 bytes of storage. Whereas, VARCHAR needs only 1-byte storage to save an empty string.

The fourth row in the table: If the length of the string is greater than the allotted storage space for both CHAR and VARCHAR, the string gets trimmed to a maximum length of storage space.

The string ‘abcdefgh’ has length 8 bytes but the data size to store is 4 bytes. So, ‘abcdefgh’ is trimmed to ‘abcd’ and saved in the table.

I am sure you got it clear after this example.

MySQL Example to Create CHAR and VARCHAR column data type:

Here is the simple command in MySQL to create the table having CHAR and VARCHAR data types.

mysql> CREATE TABLE CountryInfo (Country CHAR(5), Capital VARCHAR(5));
Query OK, 0 rows affected (0.02 sec)

It will create a table CountryInfo with two column-

Country: It has datatype CHAR and can save a maximum string length of 5 characters.

Capital: It has datatype VARCHAR and can save a maximum string length of 5 characters.

Related Read:

• If you are new to the MySQL, start learning MySQL commands.
• Select SQL Commands (Aggregate Functions with Example)

How VARCHAR2 is different from VARCHAR?

MySQL supports only two string data types – CHAR and VARCHAR. Whereas, Oracle supports additional VARCHAR2 variable-length string data type.

If you are using MySQL, you don’t need to worry about VARCHAR2.

But, you have to find the difference between VARCHAR and VARCHAR2 if you are using the Oracle database management system. Here, both are different terms.

In Oracle, VARCHAR is reserved to redefine in the future to distinguish empty and NULL string. On another end, in VARCHAR2, NULL and an empty string are considered as same. Apart from this, both string data types behave the same.

As VARCHAR is preserved for the future and can be redefined, it is not advisable to use VARCHAR. If your projects rely on an empty string and NULL string being the same, VARCHAR2 is most suitable.

CHAR vs VARCHAR | Which data type to use in SQL database table?

As we find the difference between the two, it is easy to decide which data types to use while creating a SQL database table.

The only difference between the two is the storage space.

When to use CHAR?

• If you know, your data in the table column is going to be fixed length, such as roll number, it is preferable to use a CHAR variable.
• Moreover, it is not advisable using CHAR variable to store long string values. If you initialize the value to NULL (or empty string), storage spaces allocated to the CHAR value will go unused (Ref above figure: first row in the table).

When to use VARCHAR?

• If you are not sure about the length of the data to be stored in a table, use VARCHAR variable data.
• VARCHAR is most suitable for long string data storage such as article content, address, where you are not sure about the length of the string and it can be of any length.

Which does give better performance?

There is also a difference based on the performance of the SQL server engine accessing CHAR and VARCHAR types. CHAR data type values are faster to access as it has fixed column size and SQL engine can jump to the next page by calculating the fixed size.

It is good to take advantage of CHAR performance up to a certain extent. We cannot ignore, CHAR may waste some storage space by saving small string length data into the big chunk of fixed size storage. It is suggested that, up to the length of 20 characters, CHAR is useful over VARCHAR.

Also, read:

•  Difference Between Database and DBMS
• Mind-blowing Fact about Big Data Growth

This is all about the Difference Between CHAR and VARCHAR. I hope, I am able to clear your doubt for various string data types and which one to use in your database. If you have any question on this topic, discuss in the comment section.

Aniruddha Chaudhari

I am a Python enthusiast who loves Linux and Vim. I hold a Master of Computer Science degree from NIT Trichy and have 10 years of experience in the IT industry, focusing on the Software Development Lifecycle from Requirements Gathering, Design, Development to Deployment. I have worked at IBM, Ericsson, and NetApp, and I share my knowledge on CSEstack.org.