char vs varchar and indexes

[Nick Chan]

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?

[Nick Chan]

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 '

[Erland Sommarskog]

Nick Chan (zz*******@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Gert-Jan Strik]

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

[Nick Chan]

Thanks guys for the replies !!
On Sep 8, 3:48 am, Gert-Jan Strik <[e-mail]:

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 -