Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
What is CHAR? 
2.1.
Code in SQL
2.2.
SQL
2.3.
Output
2.4.
Explanation
3.
What is VARCHAR? 
3.1.
Code in SQL
3.2.
SQL
3.2.1.
Output
3.3.
Explanation
4.
Key Differences Between CHAR and VARCHAR
5.
Difference Between CHAR Vs. VARCHAR
6.
What are Some More SQL Datatypes?
7.
Frequently Asked Questions
7.1.
Why is VARCHAR better than CHAR?
7.2.
Which is faster CHAR or VARCHAR?
7.3.
Can CHAR store numbers?
7.4.
What is the maximum size of VARCHAR?
8.
Conclusion
Last Updated: Mar 27, 2024
Easy

Difference between CHAR and VARCHAR

Author Rishabh
0 upvote

Introduction

Welcome to our blog on differences between CHAR and VARCHAR! If you've ever wondered why there are two seemingly similar data types, you're not alone. There are two primary types of fields that are used for storing character data in a database: CHAR and VARCHAR. Although they both serve the same purpose, it is important to look at some of their differences as well.

Difference Between CHAR and VARCHAR

CHAR and VARCHAR are like two ways to store words in a computer. CHAR sets aside a fixed amount of space for each word, no matter how short or long. It's like having a box for each word, and the box is always the same size. VARCHAR is more flexible. It adjusts the box size based on how long the word is. So, if you have short words, it uses smaller boxes; for longer words, it uses bigger boxes.

What is CHAR? 

CHAR is a data type that represents a fixed-length data type in SQL. To define a char column, we need to specify the maximum number of characters it can hold. The data stored in the column will be padded with spaces to fill the entire length.

Code in SQL

Let's see the code for creating a table in which we demonstrate how to use CHAR.

  • SQL

SQL

CREATE TABLE Example(State CHAR(20), Capital VARCHAR(20));
INSERT into Example VALUES('Telangana' , 'Hyderabad');
INSERT into Example VALUES('Maharashtra' , 'Mumbai');
INSERT into Example VALUES('Bihar' , 'Patna');
SELECT LENGTH(State) FROM Example;


Output

output 1

Explanation

We have created a table called Example with two columns. The state is defined as a CHAR column having a fixed length of 20 characters, while Capital is defined as a VARCHAR column with a maximum length of 20 characters. Then we see the length of the State column, which is defined as CHAR.

What is VARCHAR? 

VARCHAR is a data type that represents a variable length data type but a maximum of the set length specified in SQL. The storage size of VARCHAR is equal to the length of the entered string in bytes. We generally use varchar when the length of the datatypes is variable.
Now let’s look at the code to realize about char and varchar.

Code in SQL

Let's see the code for creating a table in which we demonstrate how to use CHAR.

  • SQL

SQL

CREATE TABLE Example(State CHAR(20), Capital VARCHAR(20));
INSERT into Example VALUES('Telangana' , 'Hyderabad');
INSERT into Example VALUES('Maharashtra' , 'Mumbai');
INSERT into Example VALUES('Bihar' , 'Patna');
SELECT LENGTH(Capital) FROM Example;


Output

output 2

Explanation

We have created a table called Example with two columns. State is defined as a CHAR column having a fixed length of 20 characters, while Capital is defined as a VARCHAR column with a maximum length of 20 characters. Then we see the length of the Capital column, which is defined as VARCHAR.

Also see, SQL EXCEPT

Key Differences Between CHAR and VARCHAR

CHAR and VARCHAR are two common data types used to store character strings in databases, some of their key differences are listed below:-

  • Length: CHAR is a fixed-length data type which means it always occupies a fixed number of bytes, while VARCHAR only uses the actual amount of bytes used.
     
  • Storage Efficiency: CHAR is less storage-efficient because it reserves space for the maximum defined length, even if the data is shorter.
     
  • Trailing Spaces: CHAR values are padded with spaces at the end to match the specified length. On the other hand, VARCHAR values do not include trailing spaces.

Difference Between CHAR Vs. VARCHAR

Now, let's see the crucial difference between CHAR and VARCHAR based on some important parameters.

Parameter CHAR VARCHAR
SIZE CHAR is used to store character strings of fixed size. VARCHAR is used to store character strings that have a variable size.
PADDING CHAR pad values with trailing spaces to reach fixed size. VARCHAR does not pad values.
PERFORMANCE CHAR can be faster because the database engine can predict the position of each record. VARCHAR is only useful when there is a significant difference in the length of the values.
SPACE EFFICIENCY CHAR can waste space for shorter values. VARCHAR is more space efficient for shorter values. 
USAGE We should use CHAR whenever we are storing values of consistent size. We use VARCHAR when we are not sure about the length of the string.
MEMORY USAGE  It may use extra memory because of its fixed size.  It may use less memory because of variable size.
BYTES USED  CHAR takes one byte per character for storage  VARCHAR takes one byte per character for storage plus one or two bytes extra for storing length information.
STORAGE  It stores only characters   It stores both characters and numbers.
LENGTH The length can be from 0 to 255  The length can be from 0 to 65535

What are Some More SQL Datatypes?

There are several datatypes in SQL other than CHAR and VARCHAR:

Data Type Description
Numeric Types
INT Integer type for whole numbers.
FLOAT Floating-point type for numbers with decimals.
DECIMAL or NUMERIC Exact numeric type for fixed-point numbers.
Date and Time Types
DATE Represents a date (year, month, and day).
TIME Represents a time of day (hour, minute, second).
DATETIME or TIMESTAMP Represents a combination of date and time.
Text Types
TEXT Variable-length character string (similar to VARCHAR).
CLOB For very large character data.
Binary Types
BLOB For storing large binary data like images or documents.
VARBINARY Variable-length binary data.
Boolean Type
BOOLEAN or BOOL Represents true or false values.
Bit Type  
BIT Represents binary data, often used for storing binary flags.
Enum Type
ENUM Represents a set of predefined values.
UUID Type
UUID Universally Unique Identifier, used to uniquely identify records.
JSON Type
JSON Stores JSON-formatted data.
XML Type
XML Stores XML-formatted data.
Interval Type
INTERVAL Represents a time interval.

Frequently Asked Questions

Why is VARCHAR better than CHAR?

For situations where your data has large differences in their length, using VARCHAR is better than CHAR because it only uses the required amount of space.

Which is faster CHAR or VARCHAR?

Neither is consistently faster; it depends on usage. CHAR has fixed length, while VARCHAR is variable. Choose based on your data characteristics and requirements.

Can CHAR store numbers?

Yes, CHAR can store numbers, but it will allocate a fixed amount of space for each value, potentially leading to wasted storage for shorter numbers.

What is the maximum size of VARCHAR?

The maximum size of VARCHAR depends on the specific database management system you are using. It is not a standardized value. For example, the maximum size for a VARCHAR column in MySQL is 65,535 characters but Microsoft SQL Server supports up to 536,870,911 characters.

Conclusion

In this article, we discussed the differences between Char and Varchar. You can also read the article MySQL Data Types to improve your knowledge about the difference between Char and Varchar.

To learn more, check out our articles:

Refer to our Guided Path to upskill yourself in DSACompetitive ProgrammingJavaScriptSystem Design, and many more! If you want to test your competency in coding, you may check out the mock test series and participate in the contests hosted on Coding Ninjas Studio!
However, you may consider our paid courses to give your career an edge over others!

Live masterclass