[RESOLVED] How Char is advantageous over VarChar2 ??

[amolt]

Hi All,
Can someone tell me plz How Char datatype is advantageous over VarChar?
How? and When to use Char?

Thanks,

amolt.

[Asgorath]

Varchar is advantageous over Char because space allocated grows dinamically.

[szlamany]

CHAR is advantageous over VARCHAR due to storage techiques.

All CHAR columns are stored in the physical data (on disk) first - since the length of each of them is known to the engine. All VARCHAR data is then stored following this - with "pointers" to indicate the size of each of these columns. These pointers exist in every row - since column sizes vary so.

So conventional wisdom would indicate that CHAR data is faster then VARCHAR. And that argument is debated often - and probably reasonably true.

Which leads to the belief - by some people - that CHAR should be used whenever the length of the data is consistent in a column - such as a SOCIAL SECURITY NUMBER - which is always 9 characters.

We personally do not support this belief.

We always use VARCHAR - even for a VARCHAR(1) field that will always have a character in it.

We like that the value in that column will be a "zero-length" string if the column is empty. As a matter of fact we really like the "trailing spaces" being removed by VARCHAR definition.

So with that said we so like the VARCHAR() difference in storage and display of data that we don't mind taking the "hit" for speed - which is probably negligible anyway...

That same goes for NULL columns!

[amolt]

Hi szlamany,

thanks for such a descriptive reply, it really helps me to find the difference.

Rgds,
amolt.

[brucevde]

I agree with szalamany. I used to use Char fields because I believed the so-called experts. But after spending considerable time adding Trim$ around recordset fields in VB programs I switched. As already mentioned using Char has diskspace and processing benefits but they add costs elsewhere (imho of course)