Char
In SQL, it is a kind of data used to hold character strings of a given length. When the PAD_CHAR_TO_FULL_LENGTH_SQL mode is active, if the length of the string is less than the set or fixed length, it is padded with additional blank spaces until it reaches the set length. The CHAR datatype has a storage capacity of n bytes (set length). When we anticipate that all of the data values in a column will be the same length, we should use this datatype.
Example
Consider the query
CREATE TABLE Data_ (Name VARCHAR(30), Gender CHAR(6));
INSERT into Data_ VALUES('Harita', 'Female');
INSERT into Data_ VALUES('Mahendra', 'Male');
SELECT LENGTH(Gender) FROM Data_; |
Output:
Explanation: The output is fixed because the gender column is initiated with length 6.
Varchar
It is a datatype in SQL that is used to record character strings that can be any length up to the maximum allowed. The string won't be padded with additional blank spaces if its length is less than a defined or fixed-length limit. The actual length of the entered string in bytes corresponds to the storage size of the VARCHAR datatype. When we anticipate that the data values in a column may have varied lengths, we should use this datatype.
Example
Consider the following query:
CREATE TABLE Data_ (Name VARCHAR(30), Gender CHAR(6));
INSERT into Data_ VALUES('Harita', 'Female');
INSERT into Data_ VALUES('Mahendra', 'Male');
SELECT LENGTH(Name) FROM Data_; |
Output:
Explanation: The output is not fixed. The length of Name even though mentioned as 30 during initialization takes length according to the input string.
Difference between char and varchar
Char | Varchar |
Character strings of a defined length are stored using the CHAR datatype. | Character strings of varying length are stored using the VARCHAR datatype. |
If the length of the string in CHAR is less than the set or fixed-length, extra memory space is added to it. | If the length of the string in VARCHAR is smaller than the set or fixed-length, it will be stored without being padded with additional memory spaces. |
Better performance than VARCHAR | Performance is not good as compared to CHAR |
Character is referred to as CHAR. | Variable Character is referred to as VARCHAR. |
The storage size for CHAR datatypes is n bytes, or the specified length. | The actual length of the entered string in bytes corresponds to the storage size of the VARCHAR datatype. |
When a column's data values are anticipated to be the same length, the CHAR datatype should be used. | When we anticipate that the data values in a column may have varying lengths, we should use the VARCHAR datatype. |
Characters in CHAR require one byte. | Each character in VARCHAR requires one byte, plus a few extra bytes for length information. |
Conclusion
VARCHAR conserves space when the length of values varies, while CHAR might do better.
