char vs varchar and indexes

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?

Re: char vs varchar and indexes

oh and

if the column is char(10)

and there's this data 'abc '


so is there a difference between these two ?

select * from t1 where col = 'abc'

or

select * from t1 where col='abc '

Re: char vs varchar and indexes

Nick Chan (zzzxtreme@yahoo.com) writes:
> 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?

The choice between char and varchar should be made be from the business
rules. If I see a char(12) column, I expect most columns to have 12
characters without trailing blanks.

I can't see why char would things faster. The physical layout of the row
is somewhat simpler, but on the other hand if the average length is far
from the max length, the char columns takes up more space, and more
space means more pages to read, and thus longer access times.

> if the column is char(10)
>
> and there's this data 'abc '
>
>
> so is there a difference between these two ?
>
> select * from t1 where col = 'abc'
>
> or
>
> select * from t1 where col='abc '

Why don't you test? I think they are the same, as trailing blanks are
ignore when comparing. But these two are not the same:

SELECT * FROM tbl WHERE col LIKE @varcharval + '%'
SELECT * FROM tbl WHERE col LIKE @charval + '%'


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: char vs varchar and indexes

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

Re: char vs varchar and indexes

Thanks guys for the replies !!
On Sep 8, 3:48 am, Gert-Jan Strik 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 -