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
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
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.