Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ourspt
Starting Member
33 Posts |
 Posted - 2006-03-28 : 03:28:53
|
| Hi,I am not sure of the exact difference of these two datatypes. I know char uses up the complete length of the size in the database and that varchar uses only 'required' space.I also understand that there is a very slight performance overhead with varchar (as it has to determine its length first and then read the actual value). But other than this point, what are the cases that support my using a char instead of varchar? Why should I ever use a Char datatype. Lot of resources suggest that if I anticipate fixed size values (or near fixed size) in a column, I should be using a char datatype. But why?? Why should I still not use a varchar datatype? I am trying to understand what the precise conditions under which Char datatype is to be used.Thanksourspt |
|
|
a_r_satish
Yak Posting Veteran
84 Posts |
Posted - 2006-03-28 : 04:12:23
|
| Take my Suggestion :-Use char Data Type when the data entries in a column are expected to be the same size(as it is fixed length)And-Use varchar when the data entries in a column are expected to vary considerably in size(as it is variable in length).simple but very usefulRegards,satish.r"Known is a drop, Unknown is an Ocean" |
 |
|
|
ourspt
Starting Member
33 Posts |
Posted - 2006-03-28 : 04:25:05
|
| Satish,I know I should be doing that and lot of resources say that. But - Why? On what basis do you (or all the sources making similar suggestion) make the suggestion? Why should I not use a varchar for columns that are expected to have fixed length (mostly) values also(other than the very minor performance overhead with varchar datatypes - what other drawbacks do they have in comparison with Char datatype)?Thanksourspt |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2006-03-28 : 04:27:19
|
From BOL:quote:
char and varcharFixed-length (char) or variable-length (varchar) character data types.char[(n)]Fixed-length non-Unicode character data with length of n bytes. n must be a value from 1 through 8,000. Storage size is n bytes. The SQL-92 synonym for char is character.varchar[(n)]Variable-length non-Unicode character data with length of n bytes. n must be a value from 1 through 8,000. Storage size is the actual length in bytes of the data entered, not n bytes. The data entered can be 0 characters in length. The SQL-92 synonyms for varchar are char varying or character varying.RemarksWhen n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified with the CAST function, the default length is 30.Objects using char or varchar are assigned the default collation of the database, unless a specific collation is assigned using the COLLATE clause. The collation controls the code page used to store the character data.Sites supporting multiple languages should consider using the Unicode nchar or nvarchar data types to minimize character conversion issues. If you use char or varchar: Use char when the data values in a column are expected to be consistently close to the same size.Use varchar when the data values in a column are expected to vary considerably in size. If SET ANSI_PADDING is OFF when CREATE TABLE or ALTER TABLE is executed, a char column defined as NULL is handled as varchar. When the collation code page uses double-byte characters, the storage size is still n bytes. Depending on the character string, the storage size of n bytes may be less than n characters.
|
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2006-03-28 : 04:31:56
|
quote:
Originally posted by ourspt
Satish,I know I should be doing that and lot of resources say that. But - Why? On what basis do you (or all the sources making similar suggestion) make the suggestion? Why should I not use a varchar for columns that are expected to have fixed length (mostly) values also(other than the very minor performance overhead with varchar datatypes - what other drawbacks do they have in comparison with Char datatype)?Thanksourspt
Mainly because a varchar of the same length as a char will take up an extra byte or two to store the length and therefore be larger.. |
|
|
|
a_r_satish
Yak Posting Veteran
84 Posts |
Posted - 2006-03-28 : 05:07:36
|
| i think rickd made it absolutely clear from BOLRegards,satish.r"Known is a drop, Unknown is an Ocean" |
|
|
|
|
|
|
|
|
Char vs varchar