Problem solve Get help with specific problems with your technologies, process and projects.


One of our clients is insisting that we change all our CHAR data columns to VARCHAR2 data types. He claims that CHAR is bad for performance. I have never heard this point of view. He's failed to provide any technical reason or a sample script demonstrating the issue. Are there any problems using CHAR? Is it a bad data type to use? Any feedback at all would be extremely helpful to me. A sample script to verify this would be of great help.

A CHAR and a VARCHAR2 are stored identically. The difference between the two is that CHAR(n) will always be "n" bytes long and will blank pad to ensure this. Whereas a VARCHAR2(n) will only store the amount of bytes that is actually there. For instance, if you have CHAR(5) and VARCHAR2(5) and insert the word 'CAR' into that column, the CHAR column will contain 'CAR ' and the VARCHAR2 column will contain 'CAR'. Using CHAR on a field with varying length entries can be a real pain due to the search semantics. You'd always have to make sure and trim a CHAR field when doing comparisons. In our 'CAR' example, CHAR(5) <> VARCHAR2(5). In other words, 'CAR ' does not equal 'CAR'.

For a good example of how you can see the differences in performance see Tom Kytes's answer to a similar question. This link will show you how PL/SQL deals with CHAR and VARCHAR2 differently.

In general, I only use CHAR if I'm doing a CHAR(1)....and not because of any real performance reason... I'm just lazy and typing CHAR(1) takes less typing than VARCHAR2(1)! Otherwise, there's not a real reason to use CHAR and more good reasons to use VARCHAR2.

For More Information

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.