What is the advantage of using CHAR instead of VARCHAR?

To begin with, it always depends on the DBMS. In theory:

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

CHAR is fixed in size and has static memory allocation.

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

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

For example, to save the word "Potato"

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

In terms of performance we have two things to consider:

With CHAR, and since the size with which you define the field is exactly the one you get at the end, processing the strings is simpler since the data size is completely predictable.

With VARCHAR, the process is slightly different. For example, when you define a column with type VARCHAR (10) the DBMS actually dynamically allocates up to 11 characters (10 + 1 to save the data size). The processing of the strings should always have to contemplate some form of validation of the size of the data.

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). Of course, VARCHAR has an advantage because it can theoretically produce more compact (smaller size) records and consequently less disk space occupied.

2). Since CHAR requires less data manipulation because of the fixed size it can typically be up to 20% faster to perform a lookup on the index compared to the same field in VARCHAR. (This applies to MySQL according to the book MySQL Database Design and Tuning)

3). Another thing to keep in mind has to do with fragmentation. Sometimes a table with PK VARCHAR ID can become fragmented due to page splitting in VARCHAR columns. Sometimes setting the column to CHAR can remedy this problem.

One final note, and I apologize for the multiple issues, but the browser (IE7) is bound to crash.

This is all about the DBMS, and since DBMS is constantly improving, this kind of generalization may eventually become just a myth. There's nothing like testing your implementation and using the knowledge of past experiences to make the best choice.