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

Quote:

Originally Posted by

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.

Quote:

Originally Posted by

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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Nick Chan wrote:

Quote:

Originally Posted by

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

Quote:

Originally Posted by

Nick Chan wrote:
>

Quote:

Originally Posted by

all these while i've only used varchar for any string

>

Quote:

Originally Posted by

i heard from my ex-boss that char helps speed up searches. is that
true?

>

Quote:

Originally Posted by

so there are these:

>

Quote:

Originally Posted by

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

>

Quote:

Originally Posted by

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?

>

Quote:

Originally Posted by

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 -

char or varchar?

Is it better to set a column type to char(x) or varchar(x), from the indexes
point of view? Which is faster?
Also which is more convinient when comparing strings (do i have to right
trim char(x) columns before comparing)?It depends on your requirements. SQL Server stores the data for these
datatypes diffrerently
1) VARCHAR(n)
SQL Server stores 1 byte per character.Declared but unused charcters do not
consume storage
2) CHAR(n)
SQL Server stores 1byte per charcter n declared ,event if partialy unused
"Savvoulidis Iordanis" <iordanis_sav@.hotmail.com> wrote in message
news:OF%23DNEVOHHA.4244@.TK2MSFTNGP04.phx.gbl...
> Is it better to set a column type to char(x) or varchar(x), from the
> indexes point of view? Which is faster?
> Also which is more convinient when comparing strings (do i have to right
> trim char(x) columns before comparing)?
>
>|||On Tue, 16 Jan 2007 08:05:56 +0200, "Savvoulidis Iordanis"
<iordanis_sav@.hotmail.com> wrote:
>Is it better to set a column type to char(x) or varchar(x), from the indexes
>point of view? Which is faster?
>Also which is more convinient when comparing strings (do i have to right
>trim char(x) columns before comparing)?
It depends. 8-)
On the one hand, a varying length column takes more processing than a
fixed length column. I seem to remember hearing that this is
especially true when they are used in an index.
On the other hand, a varchar column MAY, depending on the size of the
actual data compared to the declared size, save a lot of space. And
wasting space means the index takes up more space, which is to say
more pages. More pages means more I/O, and consuming more space in
the cache - or fitting fewer pages in the cache.
(Keep in mind that varchar has a two-byte overhead that char lacks. So
a varchar(5) column uses up 2 to 7 bytes of storage.)
There are some clear-cut cases. A column that averages 6 characters
of data would be better as a varchar(30) than as a char(30), even if
used in an index. But a column that averages 6 characters of data
would probably be better as a char(10) than a varchar(10 for indexing.
Sorry, I don't have a magic number to tell you when the balance tips
from one to the other.
Roy Harvey
Beacon Falls, CT|||As well as Uri's and Roy's comments, you might want to consider the
update/insert ratio as another factor n deciding which datatype to use. If
there are a lot of updates you might potentially end up with many forwarding
pointers if there isn't enough space to expand the varchar column's data
in-place, so in such circumstances varchar may become slower than char
access. Running DBCC SHOWCONTIG and supplying the option WITH TABLERESULTS
will give you an indication if this is an issue for you.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .