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

CHAR versus VARCHAR2

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


This was last published in December 2002

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close