char vs. varchar

2 Dec 2005 12:31 AM

Vern Rabe

The company I'm contracting at has a guideline that table columns should be
of type char if less than 20 characters, otherwise varchar. This guideline
was just changed to a requirement. In my opinion, the choice between char and
varchar should consider variability of data size as well as need of
modification performance vs. read performance, and therefore shouldn't be
based on a fixed size. Any comments I could use to help my cause, or any
disagreement?

Thanks
Vern Rabe

2 Dec 2005 12:47 AM

Stijn Verrept

Vern Rabe wrote:

> In my opinion, the
> choice between char and varchar should consider variability of data
> size as well as need of modification performance vs. read
> performance, and therefore shouldn't be based on a fixed size. Any
> comments I could use to help my cause, or any disagreement?

I agree with you. When for example you got a FirstName field, there
are names from 3 chars till 18 (in an example DB). Why would you waste
the space by using char? I only use char when the column length is the
same for every row. Good luck convincing the company ;)

--

Kind regards,

Stijn Verrept.

2 Dec 2005 12:50 AM

Stijn Verrept

Vern Rabe wrote:

Another advantage of using varchars for non fixed length columns: when
the text entered in a char column is smaller than the size of that
column it will be padded to the correct length so you'll need to handle
this in your application or use trim queries.

--

Kind regards.

2 Dec 2005 3:55 AM

Dan Guzman

I'd like to hear the company's rationale for this requirement but a length
of 20 characters seems a bit excessive to me. Data are typically read much
more often than written. Although inexpensive storage mitigates the need
for byte counting, I don't see how one can justify using a particular data
type before the schema or application is designed.

--
Hope this helps.

Dan Guzman
SQL Server MVP

Show quote

"Vern Rabe" <VernR***@discussions.microsoft.com> wrote in message
news:194CC9B9-0702-4E74-B3D2-602BA234DEA9@microsoft.com...
> The company I'm contracting at has a guideline that table columns should
> be
> of type char if less than 20 characters, otherwise varchar. This guideline
> was just changed to a requirement. In my opinion, the choice between char
> and
> varchar should consider variability of data size as well as need of
> modification performance vs. read performance, and therefore shouldn't be
> based on a fixed size. Any comments I could use to help my cause, or any
> disagreement?
>
> Thanks
> Vern Rabe

2 Dec 2005 4:24 PM

JT

Char is for fixed width text while VarChar is for variable width text. If
the column is updated frequently, they may be concerned that changing the
length of data in a VarChar would result in page splits. However, this is a
very specific situation and would not justify using Char instead of VarChar
as a general rule. Find out who is responsible for defining database design
requirements, and ask them about it.

Show quote

"Vern Rabe" <VernR***@discussions.microsoft.com> wrote in message
news:194CC9B9-0702-4E74-B3D2-602BA234DEA9@microsoft.com...
> The company I'm contracting at has a guideline that table columns should
> be
> of type char if less than 20 characters, otherwise varchar. This guideline
> was just changed to a requirement. In my opinion, the choice between char
> and
> varchar should consider variability of data size as well as need of
> modification performance vs. read performance, and therefore shouldn't be
> based on a fixed size. Any comments I could use to help my cause, or any
> disagreement?
>
> Thanks
> Vern Rabe