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 indexe
s
>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 .