What data type should I use: CHAR or VARCHAR2???


We all know that CHAR and VARCHAR2 are two different datatypes used for storing strings in Oracle. When I asked many people about what’s the difference exactly, all were saying VARCHAR2 can be used for variable sized character strings. As I’m not satesfied with the answer, as always, I’ve decided to do my own experiments on these two. Please find the exercise I’ve performed below.

SQL> create table t1 (col1 char(10), col2 varchar2(10));

Table created.

SQL> insert into t1 values(‘Oracle’,’Oracle’);

1 row created.

SQL> commit;

Commit complete.

Now try to query the table like this

SQL> select * from t1 where col1= ‘Oracle’;

COL1                COL2
———-          ———-
Oracle              Oracle

SQL> variable v1 varchar2(10)

SQL> exec :v1 := ‘Oracle’

PL/SQL procedure successfully completed.

SQL> select * from t1 where col1 = :v1;

no rows selected

SQL> select * from t1 where col1 = rpad(:v1,10);

COL1                COL2
———-          ———-
Oracle              Oracle

So you could clearly see the side effect: To get a hit in comparing a character value with a varchar2 variable, you need to use rpad, which appends blank spaces at the end of the variable v1. This is because

CHAR data will be stored in the Database with padding BLANK SPACES at the tail end. i.e. the string ‘Oracle’ in a column with CHAR(10) will be stored as ‘Oracle<space><space><space><space>’

You can prove this by issuing the below queries

SQL> select replace(col1,’ ‘,’*’),replace(col2,’ ‘,’*’) from t1;

———-                      ———-
Oracle****                Oracle

So, CHAR(10) and VARCHAR2(10) don’t have significant differences if we use them store data of exactly 10 characters (like IDs, Phone numbers etc…).

But if you are sure the data varies, it’s better to use VARCHAR2 for better space utilization and performance (You need not to consume 10 memory locations for 6 character data and need not include the blank spaces while doing the search operations).

So, I personally feel using CHAR is really not suggestible in any kind of scenarios. Oracle retained it just for backward compatibility.  I feel we can use it in case of CHAR(1) as typing VARCHAR2(1) takes more time than the CHAR(1) 😉

If you observed or not, how does this statement returned one row?

SQL> select * from t1 where col1= ‘Oracle’;

COL1                COL2
———-          ———-
Oracle              Oracle

If col1 have a string ‘Oracle<space><space><space><space>’ then we should give the comparison condition like  select * from t1 where col1= ‘Oracle<space><space><space><space>’;. Isn’t it?

That’s incorrect assumption because Oracle always trim trailing spaces on both the predicates involved in comparison operation if one of the predicate is a CHAR column.

That’s why you could see the results even if you give

SQL> select * from t1 where col1= ‘Oracle <space><space><space><space><space><space><space><space>’;

COL1                     COL2
———-               ———-
Oracle                  Oracle

I always welcome your queries and feedback 🙂


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: