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.
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
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
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.
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 articleMySQL Data Types to improve your knowledge about the difference between Char and Varchar.
Refer to our Guided Path to upskill yourself in DSA, Competitive Programming, JavaScript, System 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
Using Netflix data to master Power BI : Ace Data Analytics interview
by Ashwin Goyal, Product @ HRS Group, Ex - Udaan, OYO
07 Nov, 2024
01:30 PM
Get hired as an Amazon SDE : Resume building tips
by Anubhav Sinha, SDE2 @ Amazon
05 Nov, 2024
01:30 PM
Using Netflix data to master Power BI : Ace Data Analytics interview
by Ashwin Goyal, Product @ HRS Group, Ex - Udaan, OYO