[dba-SQLServer] varchar vs char

Actually this is not really the question.  Of course a field that always
contains a fixed number of characters should be char.  

But take the case of a field that holds manufacturer codes.  The codes may
contain anywhere from 4 to 10 letters.  Char, or varchar?  If the range is 4
to 7 characters - char or varchar.  IOW, there must be a cost associated
with the "variable length" property of the varchar field.  At what point
does it become more expensive (processing time / index processing / whatever
costs you can think of) to process a varchar than it does to just make it a
fixed width field? 

Additionally what happens to the extra unused characters?  Are they nulls,
spaces, or something else?  IOW, if making the field a char really means
"padding right with char X" then there is the issue of how to do where
clauses ("ASDF    " or "ASDF") etc.  

This is more of an "educate me on the mechanics" question.  Remember I come
from Access where all strings are varchar so I am trying to come to grips
with the "what really happens" that I suddenly have to deal with.

John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of
artful at rogers.com
Sent: Monday, January 22, 2007 9:03 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] varchar vs char

AFAIK it has more to do with indexes than with storage. Assuming some column
such as Zip 5+4 (i.e. total 9 chars, ignoring the dash) then there is IMO no
point in declaring this varchar. Better to declare it either:

a) zip 5 (not null) + zip 4 (null); or
b) zip 9.

The former lets you create a covering index consisting of z5 + z4.
The latter gives maximal performance for one precise code, and also for the
z5 codes, which should be aligned on one or more pages.

These may sound similar but they are different. It depends on how you want
to retrieve the data. One might imagine a query such as "select all the
clients living in a zip code whose proportion of BMW-ownership is in the TOP
5 zip codes". This could occur because my client is in the business of
reselling parts to BMW owners.

I might be interested in zip5+4 precisely, or alternatively I might be
interested in zip5 and want the zip4 part sorted. Impossible to predict this
beforehand. Interrogate the client, discover the data that yields the
maximum customer service (e.g. money to be made), and deliver accordingly.
In the absence of expert input from the crucial stakeholders, you're
guessing about a business you don't comprehend. That's my $.02 anyway. 

In such meetings, despite all my instincts, I try to shut up and listen.
Hear what matters. Sift the stakeholders from the wannabes. Find out what
causes the organization to live or die. Focus on that. All else is trivial.

A.

----- Original Message ----
From: JWColby <jwcolby at colbyconsulting.com>
To: dba-sqlserver at databaseadvisors.com
Sent: Monday, January 22, 2007 8:35:53 PM
Subject: [dba-SQLServer] varchar vs char

Is there a rule of thumb for where it is better to use a fixed width rather
than a variable width field?  Something like... "if a field may be more than
5 characters"?
 
John W. Colby
Colby Consulting
www.ColbyConsulting.com
 
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com





_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com