Data type question

Hello All
Can some one give me a quick lesson in data types - What is the basic
difference between a char data type and varchar data type and when does one
use one over the other and advantages and disadvantages of one over the
other.
Here is the reason I asked.
Initially I had a table to which I was importing data from a text file. The
majority of the columns in this table was of datatype varchar ranging from
size of 3 to size of 100. THe columns also consisted of decimal data types
and datetime datatypes. It was suggested that changing the data type to char
from varchar would speed up the insertion process. The file the data is
being inserted from is a fixed length ASCII text file which
Would using a char datatype over a varchar make a difference in select
statement (Select *)
Please advise
Thanks in advance"Rahul Chatterjee" <rahul@.benesysinc.com> wrote in message
news:u2jrgy1VDHA.2024@.TK2MSFTNGP12.phx.gbl...
> Hello All
> Can some one give me a quick lesson in data types - What is the basic
> difference between a char data type and varchar data type and when does
one
> use one over the other and advantages and disadvantages of one over the
> other.
> Here is the reason I asked.
> Initially I had a table to which I was importing data from a text file.
The
> majority of the columns in this table was of datatype varchar ranging from
> size of 3 to size of 100. THe columns also consisted of decimal data types
> and datetime datatypes. It was suggested that changing the data type to
char
> from varchar would speed up the insertion process. The file the data is
> being inserted from is a fixed length ASCII text file which
> Would using a char datatype over a varchar make a difference in select
> statement (Select *)
I think that using varchar in your situation is better solution. Varchars
are generally space savers and when distribution of your data ranges from 3
to 100 characters it is better that you use varchar instead of char. If you
use char you'll need to set it to the size of the largest string (100) and
each and every time you enter the value into this field it will take up all
100 chars even if actual data takes only 3 (the rest will be filled with
blanks). Internaly SQL Server stores varchar and char data types a little
bit different, but that shouldn't affect the performance of select
statements. The problem might appear when you update or insert data because
of possible page splitting if new value doesn't fit on the page so SQL
Server has to split page in two to get the space for this new data. This
process is expensive and you want to avoid it. I guess you'll just need to
know your data if you want to select best data type for it. I suggest you
that you use char if the size of your data dont range much. If it does use
varchar and you'll save some space on your hard drive.
Regards,
Tomislav Kralj
tomislav.kralj1@.zg.tel.hr|||I don't see where using a char in this case would give you any performance
gain and might even decrease it if the majority of the actual data are
relatively small in the number of bytes. While there is a little overhead
in maintaining a varchar over a char it is minimal and usually not worth the
extra space the char will take up when storing and retrieving. If you want
to get the fastest data load then make sure you have the proper conditions
to get a minimally logged load. Lookup BCP in BooksOnLine and there is a
section that explains how to do this.
--
Andrew J. Kelly
SQL Server MVP
"Rahul Chatterjee" <rahul@.benesysinc.com> wrote in message
news:u2jrgy1VDHA.2024@.TK2MSFTNGP12.phx.gbl...
> Hello All
> Can some one give me a quick lesson in data types - What is the basic
> difference between a char data type and varchar data type and when does
one
> use one over the other and advantages and disadvantages of one over the
> other.
> Here is the reason I asked.
> Initially I had a table to which I was importing data from a text file.
The
> majority of the columns in this table was of datatype varchar ranging from
> size of 3 to size of 100. THe columns also consisted of decimal data types
> and datetime datatypes. It was suggested that changing the data type to
char
> from varchar would speed up the insertion process. The file the data is
> being inserted from is a fixed length ASCII text file which
> Would using a char datatype over a varchar make a difference in select
> statement (Select *)
> Please advise
> Thanks in advance
>|||With the most important already stated, a further point:
in case the column is part of an index, the impact could be significant.
With the fixed length, the index size would be much larger, taking more
space and making traverse of the index longer. Then, checking the
individual index value mostly also takes much longer than needed.
Quentin
"Rahul Chatterjee" <rahul@.benesysinc.com> wrote in message
news:u2jrgy1VDHA.2024@.TK2MSFTNGP12.phx.gbl...
> Hello All
> Can some one give me a quick lesson in data types - What is the basic
> difference between a char data type and varchar data type and when does
one
> use one over the other and advantages and disadvantages of one over the
> other.
> Here is the reason I asked.
> Initially I had a table to which I was importing data from a text file.
The
> majority of the columns in this table was of datatype varchar ranging from
> size of 3 to size of 100. THe columns also consisted of decimal data types
> and datetime datatypes. It was suggested that changing the data type to
char
> from varchar would speed up the insertion process. The file the data is
> being inserted from is a fixed length ASCII text file which
> Would using a char datatype over a varchar make a difference in select
> statement (Select *)
> Please advise
> Thanks in advance
>