DA Morgan wrote:

> Ok ok I know ... just a fantasy from an academic. ;-)

Tell me about it. ;-)

--
Knut Stolze
Information Integration Development
IBM Germany / University of Jena

No single correct answer but I'd use VARCHAR(15) NOT NULL and make sure
that there are no fixed length columns in the row to the right of the
varchar.

1. Searches and index construction on fixed length columns will not have
to compute where the column is in the row.

2. City is very likely to be used to construct city, state for addresses
which would require truncation anyway. If you are transmitting this data
to a terminal; transmission costs for the spaces can add up. IBM studies
done over 25 years ago demonstrated that truncating trailing blanks
before transmission was always a beneficial tradeoff. (Increased network
speeds have changed this metric but not everyone connects with a T1 or
faster link.)

3. Most city names are less than 15 chars. I'd suspect that an analysis
of name length * frequency (your specific data) would show that varchar
ends up occupying less space than fixed length.

4. NOT NULL should always be used for City unless you have a need for a
null because the length can be set to zero. This also (usually)
simplifies application programming.

Phil Sherman



hikums@gmail.com wrote:
> So, what's the recommendation for java programmers if a char(15) is
> used to store an attribute like city, and it is padded with spaces to
> the right?
>
>
>
> Knut Stolze wrote:
>
>>Jan M. Nelken wrote:
>>
>>
>>>Knut Stolze wrote:
>>>
>>>
>>>>This is certainly true, but you also have to keep in mind that more short
>>>>VARCHARs might fit on a page than (padded) CHAR values. So you could
>>>>easily have a performance benefit because less pages need to be loaded to
>>>>satisfy a query.
>>>
>>>That would be true providing that application programmer shows some
>>>restrains and will not use construct like VARCHAR(1) which will use up to
>>>6 bytes (4 bytes of length - at least on DB2 for Linux, Unix and Windows -
>>>1 byte for actual data and 1 byte for null indicator). Contrast that with
>>>CHAR(1) NOT NULL which will use exactly 1 byte.
>>
>>No question that this is indeed not very smart.
>>
>>--
>>Knut Stolze
>>Information Integration Development
>>IBM Germany / University of Jena
>
>

Thanks Phil.

Phil Sherman wrote:
> No single correct answer but I'd use VARCHAR(15) NOT NULL and make sure
> that there are no fixed length columns in the row to the right of the
> varchar.
>
> 1. Searches and index construction on fixed length columns will not have
> to compute where the column is in the row.
>
> 2. City is very likely to be used to construct city, state for addresses
> which would require truncation anyway. If you are transmitting this data
> to a terminal; transmission costs for the spaces can add up. IBM studies
> done over 25 years ago demonstrated that truncating trailing blanks
> before transmission was always a beneficial tradeoff. (Increased network
> speeds have changed this metric but not everyone connects with a T1 or
> faster link.)
>
> 3. Most city names are less than 15 chars. I'd suspect that an analysis
> of name length * frequency (your specific data) would show that varchar
> ends up occupying less space than fixed length.
>
> 4. NOT NULL should always be used for City unless you have a need for a
> null because the length can be set to zero. This also (usually)
> simplifies application programming.
>
> Phil Sherman
>
>
>
> hikums@gmail.com wrote:
> > So, what's the recommendation for java programmers if a char(15) is
> > used to store an attribute like city, and it is padded with spaces to
> > the right?
> >
> >
> >
> > Knut Stolze wrote:
> >
> >>Jan M. Nelken wrote:
> >>
> >>
> >>>Knut Stolze wrote:
> >>>
> >>>
> >>>>This is certainly true, but you also have to keep in mind that more short
> >>>>VARCHARs might fit on a page than (padded) CHAR values. So you could
> >>>>easily have a performance benefit because less pages need to be loaded to
> >>>>satisfy a query.
> >>>
> >>>That would be true providing that application programmer shows some
> >>>restrains and will not use construct like VARCHAR(1) which will use up to
> >>>6 bytes (4 bytes of length - at least on DB2 for Linux, Unix and Windows -
> >>>1 byte for actual data and 1 byte for null indicator). Contrast that with
> >>>CHAR(1) NOT NULL which will use exactly 1 byte.
> >>
> >>No question that this is indeed not very smart.
> >>
> >>--
> >>Knut Stolze
> >>Information Integration Development
> >>IBM Germany / University of Jena
> >
> >