What are the differences between the CHAR, VARCHAR and VARCHAR2 data types in the database? What are their uses in the database?

In the Relational Database Management System (RDBMS) we have so many data types in SQL Server and ORACLE database. There are two very important data types i.e. CHAR and VARCHAR which we use for most of the time in the database. Let discuss more about these data types in detail to understand the importance and use in the database. We are having the CHAR, VARCHAR and VARCHAR2 data types for using in the database all the time. The behavior is different for the CHAR and VARCHAR2 data types, but both are used to store the character string values. On the other hand VARCHAR is not used to do the same. Every single data type is used for the different purposes.

Let’s discuss something about these data types CHAR, VARCHAR and VARCHAR2 one by one:-

1) CHAR: – We can use the CHAR data types for storing the fix length character strings. These string values may be Space/Blank before going to store in the memory. We cannot use this data type to store the variable length strings, because it will waste space on the disc memory.

2) VARCHAR: – These data types like VARCHAR & VARCHAR2 are similar to each other in the behavior. This type of the data type is used to reserve the space for future usage.

3) VARCHAR2:- VARCHAR2 is also used to store the variable length character strings. The length of the variable string values are stored within the values itself on the disc.

Here are some differences between VARCHAR and VARCHAR2:-

1) ORACLE suggested the new data type VARCHAR2, which is going to replace the VARCHAR.
2) VARCHAR can stores up to 2000 bytes of characters and on the other hand VARCHAR2 can stores the 4000 bytes of characters.
3) VARCHAR occupy space for the NULL values and on the other hand VARCHAR2 not occupy the space.
4) VARCHAR is ANSI standard, where VARCHAR2 is Oracle standard

Here is the difference between CHAR and VARCHAR:-

1) VARCHAR is used to store variable length character string values up to 2000 bytes of characters. So that’s why it quite slow. On the other hand CHAR is faster than the VARCHAR. Sometime twice faster than the VARCHAR.
2) CHAR is fixed length, right padded with spaces and VARCHAR is fixed length, right padded NULL and more over the VARCHAR2 is variable length.
3) Searching with the CHAR is easy because every single string is stored at the specified location from each other. So, that’s why system does not need to find the end of the string. But on the other hand in VARCHAR system has to look for the end of the each string.

We also have some other data types like nCHAR and nVARCHAR data types. These are classified as UNICODE characters, whereas CHAR and VARCHAR are called the Non-UNICODE characters.

We can use VARCHAR when strings do not have a fixed length (e.g. Names, States, etc.)

We can use CHAR when strings are always going to be the same length (e.g. SSN, License Number, etc.)

The addition of the “n” in front of either VARCHAR or CHAR designates that the column can hold Non-Unicode characters. On the other hand, without the “n” means that the strings can only hold Unicode characters. If we are storing the foreign language character in our database then we should go with the CHAR and VARCHAR data types but if we are going to store the U.S. characters then it does not matter which of the data type we are going to use.

Thanks,
Sandeep Rajmail
World of Mechatronics