Char vs Varchar in SQL

Char vs Varchar in SQL | In SQL both char and varchar/varchar2 data type is used to store alpha-numeric values. But both store these values differently from each other.

Char is used for storing the fixed lengths of alpha-numeric data in bytes. Whenever we are trying to store less number of bytes than the maximum size specified in the char data type, then the server internally automatically adds blank spaces in place of the remaining bytes until the end of the string. This is called a blank padded mechanism. 

Example:- Name CHAR(10);

Now, in each row of the “Name” column 10 bytes of space will be allocated. If we store “PROGRAM” then only 7 bytes among 10 bytes will be used, the remaining 3 bytes will be blank and wasted.

PROGRAM

Varchar and Varchar2 data type is used to store the variable or dynamic length of alpha-numeric data in bytes. Whenever we are using the varchar/varchar2 data type and try to store less number of bytes than the maximum size specified in the varchar/varchar2 data type then the server doesn’t add blank space in place of the remaining bytes. Those bytes will be released and will not be wasted.

Example:- VARCHAR CHAR(10);

Now, in each row of the “Name” column 10 bytes of space will be allocated. If we store “PROGRAM” then only 7 bytes among 10 bytes will be used but the remaining 3 bytes will be released. Hence these 3 bytes will not be wasted

PROGRAM

Also see:- Difference between VARCHAR vs VARCHAR2

When should we use CHAR vs VARCHAR/VARCHAR2?

We should use CHAR data type when we expect the data values in a column are of the same length. For example:- to store country_code, state_code, phone number, and e.t.c char data type is the best choice because each column will contain the same length. VARCHAR and VARCHAR2 data types should be used when the data values in a column are of dynamic length. For example:- to store name, mail id, address, and e.t.c. 

Comparison table between CHAR vs VARCHAR in SQL

CHARVARCHAR/VARCHAR2
It is used to store a fixed number of alpha-numeric values.It is used to store a dynamic number of alpha-numeric values.
CHAR understands “character”.VARCHAR understands “variable character”.
Whenever we are trying to store less number of bytes than the maximum size specified in the char data type, then the server internally automatically adds blank spaces in place of the remaining bytes until the end of the string. Whenever we are using the varchar/varchar2 data type and try to store less number of bytes than the maximum size specified in the varchar/varchar2 data type then the server doesn’t add blank space in place of the remaining bytes. 
We should use char data type when we expect the data values in a column are of the same length.VARCHAR and VARCHAR2 data types should be used when the data values in a column are of dynamic length. 

VARCHAR saves space when there is variation in the length of values, but CHAR might be performance-wise better.

CREATE TABLE TEST(NAME VARCHAR(10), LANG CHAR(10));
INSERT INTO TEST VALUES('SOPHIA', 'ENGLISH');
INSERT INTO TEST VALUES('JOHN', 'FRENCH');
SELECT LENGTH(NAME), LENGTH(LANG) FROM TEST;

Output:-

LENGTH(NAME)    LENGTH(LANG)
-----------     ------------
      6              10
      4              10

Both “Name” and “Lang” columns can store a maximum of 10 bytes of data. Here “Name” column is using the VARCHAR data type whereas the “Lang” column is using the CHAR data type. Hence the “Lang” column always takes 10 bytes of storage whereas “Name” will take the variable length between 1 to 10 based on stored data.

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!

Leave a Comment

Your email address will not be published. Required fields are marked *