Question:
What is the advantage of using CHAR instead of VARCHAR in a database since the advantages of VARCHAR exceed those of CHAR (if any?). Is there a difference in field size definition time since VARCHAR holds a variable length string (up to its limit established by the column creator)?
Answer:
For a start, it always depends on the DBMS. In theory:
VARCHAR is used to store variable length strings and uses dynamic memory allocation.
CHAR is a fixed size and has static memory allocation.
Normally, if your content is of fixed size (or very similar) then using CHAR brings better performance. When the size differs a lot then it is better to use VARCHAR.
It varies by implementation, but typically VARCHAR uses an additional one or two bytes of memory (for size or to mark the end of the data) in relation to the total data size.
For example, to save the word "Potato"
- CHAR(6) = 6 bytes, no 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 you define the field with is exactly what you get at the end, processing the strings is simpler since the data size is completely predictable.
With VARCHAR, the processing is a little different. For example, when you define a column with type VARCHAR(10) the DBMS dynamically allocates up to 11 characters (10 + 1 to store the data size). The processing of strings should always have to include some form of data size validation.
This difference becomes more apparent when we think about 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 in theory produce more compact (smaller size) records and consequently, less occupied disk space.
two). Since CHAR requires less data manipulation due to the fixed size it can usually be up to 20% faster to do a lookup in index compared to the same field in VARCHAR. (This is valid for MySQL according to the book MySQL Database Design and Tuning)
3). Another thing to consider has to do with fragmentation. Sometimes a table with PK ID VARCHAR can get fragmented due to page division in VARCHAR columns. Sometimes defining the column as CHAR can solve this problem.
A final note, and I apologize for the multiple edits, but the browser (IE7) is constantly crashing.
Everything I've written depends on the DBMS and since DBMS are constantly improving, this type of generalization could end up, in the future, becoming just a myth. There is nothing quite like testing your implementation and using knowledge from past experiences to make you choose the best option.