Char and Varchar in SQL

What is the difference between char and varchar in SQL data type? Is both the same? If not in which situation one use char and in which situation one use varchar?

memory allocation is static for char data type variables but in case of varchar memory allocation is dynamic. This is the basic difference between them

-- James

James it was a good explanation which gave me an insight about the main difference. But could you say me in what situations people prefer to use char.

Fred,

I think u have understood the basic difference between char & varchar is that char datatype is used to store fixed length character value & varchar is used to store variable length character value.

Since ur very much interested in an example, let me explain with an example.
Lets say the data which has to be stored in Employee_ID is fixed length of 7, then CHAR datatype can be used. CHAR(7) - the length of the value that will be stored will be static & so CHAR is used.
If variable length data has to be stored like Employee Name, then VARCHAR can be used. The size of the column value can vary so variable length datatype is used.

*** Innila ***

Thats nice.
Can you tell me the difference between varchar and varchar2 in oracle?

Well, VARCHAR can store upto 2000bytes and VARCHAR2 can store upto 4000bytes.
Also, VARCHAR occupies space for NULLs and VARCHAR2 doesn't. One can say VARCHAR2 is advanced VARCHAR. Apparantly, Orcale has plans of scraping VARCHAR and retaining only VARCHAR2.

And oracle strongly recommends use of VARCHAR2:D

Cheers,
Kalayama

In varchar, you can store 2000 bytes of data...
In varchar2, you can store 4000 bytes of data

In future you want add any other further updatation (ie increase the size of data...) not possible in varchar..You can do if you use varchar2.

I don't know this is correct...According to my knowledge i told this..

If anybody knows other than this plz post their answer.

-----------------------------
suresh

Ohhh already kalayama explained it....

Just now i saw that one...

Good answer kalayama.

----------------
suresh

I have never seen you post unnecessarily (When answer is told, you never repeat like most people do). Was wondering what went wrong here when I saw your post.

Then afterwards I looked at your next post...huh huh! May be we were typing answers at the same time and I just ended up posting it earlier :D

No wonder you were chosen member of the month :)

Hay, thanks guys,
this thread gave me some good info.

I always used VARCHAR2 thinking there is no difference between VARCHAR and VARCHAR2. :D

Though I always used an advanced type (VARCHAR2), I should know the difference. Anyways, now I know it. :cool:

I thank you all again.

Well, good to see that this forum is serving its purpose :)

At the end of the day this is what we want. Our knowledge should be shared and improved.

(I could see that the question was asked by [B]Fred[/B] and [B]sutnarcha[/B] was benifited by the thread too :D

The char is a fixed-length character data type, the varchar is a variable-length character data type.

Because char is a fixed-length data type, the storage size of the char value is equal to the maximum size for this column. Because varchar is a variable-length data type, the storage size of the varchar value is the actual length of the data entered, not the maximum size for this column.

You can use char when the data entries in a column are expected to be the same size.
You can use varchar when the data entries in a column are expected to vary considerably in size.

CHAR
CHAR should be used for storing fix length character strings. String values will be space/blank padded before stored on disk. If this type is used to store varibale length strings, it will waste a lot of disk space.

VARCHAR
Currently VARCHAR behaves exactly the same as VARCHAR2. However, this type should not be used as it is reserved for future usage.

VARCHAR2
VARCHAR2 is used to store variable length character strings. The string value's length will be stored on disk with the value itself.