|
Home > Archive > microsoft.public.sqlserver.server > November 2002 > Varchar vs Char
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| Sathish 2002-11-29, 3:33 pm |
| Need to know whether there is any advantage of using Char
over Varchar or vice versa. If Varchar is going to save
us space, then why should we have usage for Char at all.
Is there any query performance involved in using Varchar.
Please let me know.
Thanks,
Sathish.
| |
| Russell Fields 2002-11-29, 3:33 pm |
| Sathish,
There is some small extra overhead on Varchar since the position in the page
requires more calculation. They can also cause more page splits when data
is updated. But there is also some small overhead for Char columns that are
rarely filled up as well.
I would suggest Char for columns where the data almost always fills up the
space. For example, US telephone numbers. If the column is varying but
small (some have suggested 10 Characters or less, but I don't have a
personal rule of thumb on this) stick with Char.
For bigger columns always use Varchar. (Bigger columns are the ones that
are more rarely filled, of course.)
Russell Fields
"Sathish" <nrsathishkumar@yahoo.com> wrote in message
news:1f7f701c297ea$0a5f3bb0$8d
f82ecf@TK2MSFTNGXA02...
> Need to know whether there is any advantage of using Char
> over Varchar or vice versa. If Varchar is going to save
> us space, then why should we have usage for Char at all.
> Is there any query performance involved in using Varchar.
> Please let me know.
> Thanks,
> Sathish.
| |
| BP Margolin 2002-11-29, 4:23 pm |
| Sathish,
Russell has pretty much explained the physical implementation of CHAR and
VARCHAR, so I'll concentrate on performance ...
Basically, it depends ... in other words, one can not give a **simple**
blanket answer that covers all bases.
The CHAR data type requires a few less CPU cycles to access. But at speeds
of 2GB and 3GB, it takes a heck of a lot of CPU cycles before one notices a
performance difference, so the "real" answer is that in practice there is
very little performance difference between **accessing** a CHAR vs. a
VARCHAR data type.
However, that's not the full story by any means. Generally, CHARs take more
physical space on data and index pages than do VARCHARs (note the use of the
word "generally", because VARCHARs do carry a physical 2-byte overhead that
is not required by the CHAR data type).
Since CHARs generally take more space that VARCHARs on database pages, there
is a greater probability that fewer rows will be fit onto a data page when
one uses only CHAR data types than when uses only VARCHAR data types. Fewer
rows per page translates, usually, into more physical I/O to satisfy
queries. Physical I/O is the "killer" when it comes to performance.
Bottom line ... look at the logical requirements of the column. If the data
is always fixed length, make it CHAR. If the data is variable in length,
make it VARCHAR. Just about the only exceptions I make to this rule is that
I, except under the most extraordinary circumstances, would not have a
VARCHAR (1) or a VARCHAR (2) since in both instances, the VARCHAR **always**
takes at least as much physical space as does the equivalent CHAR.
-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.
"Sathish" <nrsathishkumar@yahoo.com> wrote in message
news:1f7f701c297ea$0a5f3bb0$8d
f82ecf@TK2MSFTNGXA02...
> Need to know whether there is any advantage of using Char
> over Varchar or vice versa. If Varchar is going to save
> us space, then why should we have usage for Char at all.
> Is there any query performance involved in using Varchar.
> Please let me know.
> Thanks,
> Sathish.
|
|
|
|
|