Posted by Nick Chan on 09/11/07 03:11

Thanks guys for the replies !!
On Sep 8, 3:48 am, Gert-Jan Strik <so...@toomuchspamalready.nl> wrote:
> Nick Chan wrote:
>
> > all these while i've only used varchar for any string
>
> > i heard from my ex-boss that char helps speed up searches. is that
> > true?
>
> > so there are these:
>
> > 1) char with index
> > 2) char without index
> > 3) char with clustered index
> > 4) varchar with index
> > 5) varchar without index
> > 6) varchar with clustered index
>
> > some of my tables primary key (clustered) is a string type. would it
> > be benificial to use char? or would using (6) makes no difference?
>
> > for non primary key columns that needs to be searched a lot, can i say
> > (1) is the best?
>
> I don't think there is a big performance difference between
> handling/comparing a char column versus a varchar column.
>
> So for optimal performance, it comes down to two other aspects, required
> space and fragmentation.
>
> A varchar has an overhead of 2 bytes per values. These 2 bytes specify
> the length of the value. Also, if the column in question is the only
> varchar column in the table, then you should add another byte (because
> that byte would be saved if no varchar columns were used). So then,
> based on the average value length, you can calculate whether char or
> varchar uses the least space. For example, a varchar(10) with an average
> data length of 6 would require less space than a char(10). Another
> example: a varchar(2) will always be less space efficient than a
> char(2).
>
> The other consideration is fragmentation. If you use a varchar column,
> and it is updated often, and the updates will often change the data
> length of the value, then this will cause fragmentation. Updates of a
> char column can always be done in place, which minimizes fragmentation.
>
> So in general, if the column's defined size is small, or if the average
> data length is close to the defined length, then you best choose char,
> otherwise, use varchar.
>
> --
> Gert-Jan- Hide quoted text -
>
> - Show quoted text -

[Back to original message]