|
|
Posted by Gert-Jan Strik on 09/07/07 19:48
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
Navigation:
[Reply to this message]
|