database-char-and-varchar-what-are-the-differences

Most programmers may already know the main different between CHAR and VARCHAR - Thefirst one supports fixed-length data and the second one supports variable-lengths of data.

Some people may think, since VARCHAR can do what CHAR does, then why we still need CHAR in our database design. I even have heard one of the very experienced Oracle database administrator say "Nowadays nobody is using CHAR anymore, you can forget about putting CHAR column in your database design".

 

The statement is not totally right. Even though VARCHAR can do what CHAR does, CHAR does has certain level of advantages over VARCHAR in terms of performance, efficiency and space.

I think it is good for a database designer to know how a database is being design. In other words, to think how a database programmer will design the database management system (DBMS) for the best performance and efficiency.

1. When a table column size is quite small, for instance 1 to 4 bytes, using CHAR helps save more space then VARCHAR. This is because DBMS will need additional few bytes (depends on database limitation for VARCHAR) to keep track the length of the data (all the rows) for the VARCHAR column.

2. Since VARCHAR can keep variable size of data, therefore it needs to keep track the length of the data for all the records. Therefore it adds little overhead to the DBMS's performance if compared to CHAR.

3. DBMS (I would say most systems) keeps fixed-length data and index more efficiently. Retrieving and updating data from and to a fixed-length row table is faster then a variable-length row table.

Therefore, I think CHAR is still very useful. We should always use CHAR if the column size is small enough or the data input is always going to be the size of the column (e.g. most of the data is in 10-12 bytes size and the column size is 12 bytes), so that we can get betterperformance for our application.