Summary: In this article, you will learn about the differences between CHAR Vs VARCHAR, and what are the used in string data types.
CHAR Vs VARCHAR Overview
CHAR and VARCHAR both are the ASCII character data types and both are almost same but they are different at the stage of storing and retrieving the data from the database. Therefore, there are the following some important differences between CHAR and VARCHAR−
CHAR DATA TYPE
VARCHAR DATA TYPE
full name is CHARACTER
full name is VARIABLE CHARACTER
It can stores values in fixed lengths and are padded with space characters to match the specified length
VARCHAR can stores values in variable length along with 1-byte or 2-byte length prefix and are not padded with any characters
The CHAR and VARCHAR types both are similar, however they differ in the way when are stored and retrieved. they also differ to each other in maximum length and in whether trailing spaces are retained.
Both data types are declared with a length therefore, they indicates the maximum number of characters you want to store. such as, CHAR(30). it will hold up to 30 characters.
The length of a CHAR column is fixed to the length that when you create the table you declare. furthermore, the length can be any value from 0 to 255. Furthermore, when the CHAR values are stored, they are right-padded with spaces to the specified length. furthermore, When you want to CHAR values retrieved, then the trailing spaces will removed unless the PAD_CHAR_TO_FULL_LENGTH, when SQL mode is enabled.
VARCHAR columns values are variable-length strings, length can be specified as a value from 0 to 65,535. The effective maximum length of a VARCHAR is subject to the max row size (65,535 bytes, that is shared among all columns) and also the character set used.
In contrast to CHAR, and VARCHAR values will stored as a one-byte or two-byte length prefix plus data. furthermore, the length prefix specify the number of bytes in the value. furthermore, column uses one(1) length byte if the values required no more than 255 bytes, two(2) length bytes if values can required more than 255 bytes.
Furthermore, if strict SQL mode isn’t enabled and when you will assign a value to a CHAR or VARCHAR column that exceeds the column’s max. length, so, the value is truncated to fit and a warning will generate. For truncation of non-space characters, you may cause an error to occur and suppress insertion of the value by using strict SQL mode.
Illustration of CHAR Vs VARCHAR
The following given below table illustrates the differences between CHAR Vs VARCHAR by showing the result of storing various string values into CHAR(4) and VARCHAR(4) columns. suppose that the column uses a single-byte character set like latin1.
Question: Can I just use VARCHAR for everything?
Answer: The short answer is: No.
Sure, VARCHAR is very flexible and will accept most kinds of data. when using VARCHAR for everything robs your database of critical functionality, performance and data consistency.
Let’s see the example of storing data in a VARCHAR column. We’ve instantly lost functionality, because we can not simply add, subtract, or compare our date.
furthermore, if we use one of the date types e.g., DATETIME and SMALLDATE, etc. then we’ve a host of system functions such as DATEADD and DATEPART.
Another drawback with storing date in a VARCHAR is that we’ve no built-in format control. Our system will now readily accept all of the subsequent as “valid” date data:
March 1, 2015
Mar 1 2015
Star date 003.1.2015.304
March 31, 2015
We want to unify and enforce the date format within the database, therefore we can simply search out date errors such as March 31 and compare date, for example: SELECT columns FROM table1 WHERE myDate > ‘2/1/2015’.
We run into the similar problems no matter what non-string type we try to store in VARCHAR.
Question: When should I use CHAR instead of VARCHAR?
Answer:The short answer is: Almost never.
VARCHAR only costs 2 “extra” bytes, compared to CHAR. It’s only in rare cases where using the CHAR will actually save you effort and space. The following Examples of those cases:
CHAR(2) for state abbreviation. therefore, if your business rules say that the State column will ALWAYS be two(2) characters long, then use CHAR(2).
Fixed length product codes e.g., CHAR(8) for product codes such as ‘005-3118’. Just such as the state, furthermore, if you have a field, like product code, that is ALWAYS now and evermore a set length, then CHAR is most preferred.
A single letter string. as an example, we should always use CHAR(1) for a middle initial column.
Your users, database and developers will be much happier if you use proper data types.
In this article, you have learned the differences between CHAR and VARCHAR, if have any problem contact me, if will try to resolve the problem. I hope you will enjoy! this article.