SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Char vs varchar
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ourspt
Starting Member

33 Posts

Posted - 03/28/2006 :  03:28:53  Show Profile  Reply with Quote
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.

Thanks
ourspt

a_r_satish
Yak Posting Veteran

India
84 Posts

Posted - 03/28/2006 :  04:12:23  Show Profile  Send a_r_satish a Yahoo! Message  Reply with Quote
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 useful


Regards,
satish.r
"Known is a drop, Unknown is an Ocean"
Go to Top of Page

ourspt
Starting Member

33 Posts

Posted - 03/28/2006 :  04:25:05  Show Profile  Reply with Quote
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)?

Thanks
ourspt
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

United Kingdom
3608 Posts

Posted - 03/28/2006 :  04:27:19  Show Profile  Reply with Quote
From BOL:

quote:

char and varchar
Fixed-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.

Remarks
When 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.

Go to Top of Page

RickD
Slow But Sure Yak Herding Master

United Kingdom
3608 Posts

Posted - 03/28/2006 :  04:31:56  Show Profile  Reply with Quote
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)?

Thanks
ourspt


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

Go to Top of Page

a_r_satish
Yak Posting Veteran

India
84 Posts

Posted - 03/28/2006 :  05:07:36  Show Profile  Send a_r_satish a Yahoo! Message  Reply with Quote
i think rickd made it absolutely clear from BOL

Regards,
satish.r
"Known is a drop, Unknown is an Ocean"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000