What is the advantage of using CHAR instead of VARCHAR?

Question

What is the advantage of using CHAR instead of VARCHAR in database since the advantages of VARCHAR exceed those of CHAR (if it has? ). There is time difference of field size definition since VARCHAR holds a string of variable size (up to its limit set by the column creator)?

Answer 1

For starters, it always depends on the DBMS.In theory:

VARCHAR is used to store strings of variable size and uses dynamic memory allocation.

CHAR has fixed size and static memory allocation.

Normally, if your content is fixed-size (or very similar) then the use of CHAR brings better performance. When the size differs so much then it is better to use the VARCHAR.

It varies by implementation, but usually VARCHAR uses one or two additional bytes of memory (for size or to mark the end of the data) relative to the total size of the data.

For example, to keep the word "potato"

• CHAR(6) = 6 bytes, not overhead
• VARCHAR(10) = 8 bytes used ( 2 overhead)
• CHAR(10) = 10 bytes used (4 bytes overhead)

In terms of performance we have two things to consider:

With CHAR, and since the size that defines the field is exactly the one you get at the end, string processing is simpler since the size of the data is completely predictable.

With VARCHAR, the process is a little different. For example, when defining a column with the type VARCHAR(10) in reality the DBMS dynamically allocates up to 11 characters (10 + 1 to store the data size). The parsing of strings must always include some form of validation of the data size.

This difference becomes more apparent when we think of two aspects: 1) Storing millions or billions of records and 2) indexing CHAR or VARCHAR columns.

1). It is obvious that VARCHAR has an advantage because it can theoretically produce more compact records (of smaller size) and consequently less disk space.

2). Since CHAR requires less data manipulation due to fixed size it can normally be up to 20% faster to perform a lookup on the index compared to the same field in VARCHAR. (This is valid for Mysql according to the Mysql Database Design and Tuning book)

3). Another thing to consider is fragmentation. Sometimes a table with VARCHAR PK ID may become fragmented due to division of pages in VARCHAR columns. Sometimes set the column as CHAR can solve this problem.

A final note, and I apologize for the multiple edits, but the browser (IE7) is steadily crashing.

All this I have written depends on the DBMS and since DBMS are constantly improving, this kind of generalization may end up only becoming a myth in the future. There’s nothing like testing your implementation and using past experience knowledge to make you choose the best option.