Re: CHAR vs VARCHAR

From: Hans Forbrich <forbrich_at_telusplanet.net>
Date: Wed, 02 Apr 2003 22:38:43 GMT
Message-ID: <3E8B6582.B36D1BB0@telusplanet.net>

Paul Pruchnik wrote:

> Greetings,
> What is the savings of CHAR(4) vs VARCHAR(4) for storing a 4 (required)
> character code? What is the overhead in using VARCHAR for fields of required
> length?
> Thanks,
> -Paul Pruchnik

When you say "4 (required)", can we state that there are 4 non-blank characters? And there will NEVER be any trailing blanks?

My observations and opinions on using CHAR(4)

- it adds a non-coded constraint to ensure that all 4 bytes will be used.
- it is very rarely used in any application code
- there may be am indexing benefit (precalc. block structures), but I'd guess it

is so rarely used that the developers simply wouldn't bother

- it will help in your size calculations
- helps avoid the "is it NULL or just empty?" issue.
- since a non-Null entry MUST be the right size, a Null value byte "should" not

be required.
- I would expect run-time size information 'length of string byte/word' (or - as used in some other systems - 'trailing null byte terminates string') not to be necessary since that information is stored at the dictionary level.

At the same time, virtually every [Oracle] programmer is familiar with the operations & assumptions of the VarChar2, and manby will get tripped up by the Char padding. So I suggest avoiding Char unless you can ensure that there will never be trailing blanks.

JIMHO/Hans Received on Wed Apr 02 2003 - 16:38:43 CST