Open In App

CHAR vs VARCHAR in SQL

Improve
Improve
Like Article
Like
Save
Share
Report

1. CHAR Datatype: 
It is a datatype in SQL which is used to store character string of fixed length specified. If the length of the string is less than set or fixed-length then it is padded with extra blank spaces so that its length became equal to the set length when PAD_CHAR_TO_FULL_LENGTH SQL mode is enabled. The storage size of the CHAR datatype is n bytes(set length). We should use this datatype when we expect the data values in a column are of the same length. 

Example: 
Consider the Query:  

CREATE TABLE Student(Name VARCHAR(30), Gender CHAR(6));
INSERT into Student VALUES('Herry', 'Male');
INSERT into Student VALUES('Mahi', 'Female');
SELECT LENGTH(Gender) FROM Student;

OUTPUT:  

LENGTH(Gender)
6
6

2. VARCHAR Datatype: 
It is a datatype in SQL which is used to store character string of variable length but a maximum of the set length specified. If the length of the string is less than set or fixed-length then it will store as it is without padded with extra blank spaces. The storage size of the VARCHAR datatype is equal to the actual length of the entered string in bytes. We should use this datatype when we expect the data values in a column are of variable length. 

Example: 
Consider the Query:  

CREATE TABLE Student(Name VARCHAR(20), Gender CHAR(6));
INSERT into Student VALUES('Herry', 'Male');
INSERT into Student VALUES('Mahi', 'Female');
SELECT LENGTH(Name) FROM Student;

OUTPUT:  

LENGTH(Name)
5
4

Difference between CHAR and VARCHAR datatypes:

SR.NO. CHAR VARCHAR
1. CHAR datatype is used to store character strings of fixed length VARCHAR datatype is used to store character strings of variable length
2. In CHAR, If the length of the string is less than set or fixed-length then it is padded with extra memory space. In VARCHAR, If the length of the string is less than the set or fixed-length then it will store as it is without padded with extra memory spaces.
3. CHAR stands for “Character” VARCHAR stands for “Variable Character”
4. Storage size of CHAR datatypes is equal to n bytes i.e. set length The storage size of the VARCHAR datatype is equal to the actual length of the entered string in bytes.
5. We should use the CHAR datatype when we expect the data values in a column are of the same length. We should use the VARCHAR datatype when we expect the data values in a column are of variable length.
6. CHAR takes 1 byte for each character VARCHAR takes 1 byte for each character and some extra bytes for holding length information
9. Better performance than VARCHAR Performance is not good as compared to CHAR

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


Last Updated : 21 Jun, 2022
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads