Re: char vs varchar2

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 20 Dec 1999 07:41:49 -0500
Message-ID: <nk8s5sg97p5kmvbh05htulh0nqfmvttiip@4ax.com>

A copy of this was sent to marcus_chan_at_my-deja.com (if that email address didn't require changing) On Mon, 20 Dec 1999 04:55:03 GMT, you wrote:

>hello,
>
>hope someone could enlighten me on these?
>1. What are the diffrences between Char/varchar2?

chars and varchars are stored in exactly the same way:

leading length field, number of bytes dependent on the size of the string
followed by data

A char is always blank padded to the max length. if you have a char(10) and store the word hello in it, it will be stored with a leading byte count of 10 and the value 'hello '.

A varchar is not stored blank padded. the word hello would have a byte count of 5 and be stored as 'hello'.

If you do comparisions in SQL using mixed mode datatypes -- eg: you have a CHAR(10) in the database but using a VARCHAR variable in a query like : select * from emp where ename = :bind_variable -- you must make sure the varchar bind variable is blank padded, eg: you would want to code:

select * from emp where ename = rpad( :bind_variable, 10, ' ' )

in order for the compare to work . for example:

tkyte_at_8.0> create table t ( x char(10) );

Table created.

tkyte_at_8.0> insert into t values ( 'hello' );

1 row created.

tkyte_at_8.0> 
tkyte_at_8.0> variable bind_variable varchar2(10)
tkyte_at_8.0> exec :bind_variable := 'hello'

PL/SQL procedure successfully completed.

tkyte_at_8.0> select * from t where x = :bind_variable;

no rows selected

tkyte_at_8.0> select * from t where x = rpad( :bind_variable, 10, ' ' );

hello

So, a varchar only takes as much space as it needs (which is good and bad. good = less space, more rows per block. bad = row size changes with updates as the column grows and shrinks leading to chained rows and migrated rows). A varchar is easier to compare to as you do not need to rpad.

>2. When should i use char/varchar2 in table creation?
>

that is something only you can answer. I think most people would say "varchar2" in almost every case.

>thanks
>marcus
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Dec 20 1999 - 06:41:49 CST