Char vs Varchar
Char and Varchar are commonly used character data types in the database system that look similar though there are differences between them when it comes to storage requirements. In database designing, there are a lot of data types used. Out of them, character data types get a more prominent place as they are used to store a lot of information compared to numbers. Character data types are used for storing characters or alphanumeric data in strings. The type of database character set is defined when creating the database. Again, out of these character data types, Char and Varchar are the commonly used ones. This article explains what these two data types, char and varchar, are and the difference between them.
What is Char?
The ISO definition of char is a character and the char data type is used to store a character. Char (n) can store n fixed size of characters. The maximum number of characters that a char (n) can hold is 255 chars and a string length must be a value from 1 to 8000. Char is fifty percent faster than varchar and, therefore, we can get a better performance when we work with char. Char uses static memory allocation when storing data. When we want to store strings with a known fixed length, it is better to use the char. As an example, when storing ‘Yes’ and ‘No’ as ‘Y’ and ‘N’, we can use the data type char. And also when storing a person’s national identity card number with ten characters, we can use the data type as char (10).
What is Varchar?
As the name suggests, varchar is called a variable character. Varchar is used to store alphanumeric data that have variable lengths. The maximum number of characters that this data type can hold is 4000 characters and the maximum storage size is 2 GB. The storage size of varchar is the actual length of the data plus two bytes. Varchar is slower than char and it uses dynamic memory allocation when storing data. We can use varchar when storing data such as names, addresses, descriptions, etc. Not only strings, but also non-string types such as date types, “March 12th 2015”, ”12/03/2015” also can be stored in the varchar data type.
What is the difference between Char and Varchar?
• Although char and varchar are character data fields, char is a fixed length data field and varchar is a variable size data field.
• Char can store only fixed size non-Unicode string characters, but varchar can store variable sizes of strings.
• Char is better than varchar for data that frequently change . This is because the fixed-length row of data is not prone to fragmentation.
• Char will occupy only the fixed space that is defined when declaring the variable. But varchar will occupy the space based on the data that is inserted and also it will occupy 1 or 2 bytes as the length prefix.
• If the data is less than 255 chars, 1 byte is allocated and if data is more than 255 chars 2 bytes are reserved. If we use a char to store a flag of ‘Y’ and ‘N’ it will use one byte to store, but when we use varchar it will take two bytes to store the flag including an extra byte as the length prefix.
Summary:
Char vs Varchar
Char and varchar are the most highly used character data type available in databases. Char is used to store a string with a fixed length while varchar is used to store strings that have a varying length. In order to get a better performance from the data, it is more important to choose the correct data types for the fields of the tables in your database. It is more convenient to use the smallest data types that can correctly store the data, because they take less space from the memory.
Images Courtesy: Varchar via Wikicommons (Public Domain)
.PNG#mediaviewer/File:Emp_Add_Tables_(Database).PNG){kind=link}
Leave a Reply