Re: Perfromance issue
- From: "Anthony Thomas" <ALThomas@xxxxxxxxx>
- Date: Thu, 6 Oct 2005 07:56:33 -0500
Although I agree with Andrew in his response, entirely, there are some
things for you to watch out for that he didn't mention; I assume because he
wanted to stress the importance of data type selection based on its own, not
for performance reasons.
With that said, however, there are performance differences. CHARs are
fixed-width, so there is no needed overhead to maintain them. VARCHARs on
the other hand are variable, and that variability must be managed. That
does introduce a small performance hit on the system. That impact is the
same regardless of the length of the VARCHAR definition. Basically, you
have to leave space in a secondary attribute that basically tells you how
long any particular one value is. This is not necessary for CHARs because
the definition is sufficient. However, this has nothing to do with
indexing, this is just an artifact of the data type.
Also, depending on the ANSI_PADDING settings, both VARCHARs and CHARs will
handle "short" values (those where the length does not match the definition)
differently, from each other as well as determinant on the current
ANSI_PADDING setting.
As far as indexing is concerned, there is no explicit performance overhead
difference between the two data types. However, implicitly, with CHARs, you
will get a uniform fill of the index pages because that data type is
fixed-width. VARCHARs, on the other hand, will vary; thus, their index
pages will contain varying number of rows. This too is determinant on the
current Cluster Index definition, since all non-clustered index also save
the corresponding cluster-index values needed for the Bookmark Lookups.
With that said, it would be difficult to say whether or not a consistent
page fill would have a performance impact or not. That would depend on what
queries are being executed that would make use of such indexes as well as
whether or not the Clustered Index definition was fixed-width or varying.
To support what Andrew advised, the determination of either VARCHAR or CHAR
is base on the data definition, not the performance impact: you either have
fixed-width data or varying.
Sincerely,
Anthony Thomas
--
"Andrew J. Kelly" <sqlmvpnooospam@xxxxxxxxxxxx> wrote in message
news:%23oaAnihyFHA.664@xxxxxxxxxxxxxxxxxxxxxxx
> Not sure what you are asking. Are you asking if you should use a char or
a
> varchar for performance reasons? If so then that is not the way to
> determine a datatype. You need to use the proper datatype for the data
you
> are storing. Chars and Varchars are not the same thing and should not be
> used interchangeably. Maybe you can provide more info so we can give a
> better reply.
>
> PS: This newsgroup is not related to indexes, it is for Failover
> clustering. You may want to try .programming or .server next time for a
> more appropriate audience.
>
> --
> Andrew J. Kelly SQL MVP
>
>
> "charlie" <charlie@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:284F4306-B406-4F09-81AA-B475E2BF09F0@xxxxxxxxxxxxxxxx
> > For a non-clustered index, would it have a performance difference
between
> > varchar(20) and char(20) , using only either one of them.
> >
> > thx!!!
>
>
.
- References:
- Re: Perfromance issue
- From: Andrew J. Kelly
- Re: Perfromance issue
- Prev by Date: Re: Perfromance issue
- Next by Date: Re: Receiving 'denied access using default cluster SD' error.
- Previous by thread: Re: Perfromance issue
- Next by thread: Re: Receiving 'denied access using default cluster SD' error.
- Index(es):
Relevant Pages
|
