Posted by Erland Sommarskog on 09/07/07 10:19

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/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]