[dba-SQLServer] varchar vs char

Jim Lawrence accessd at shaw.ca
Mon Jan 22 21:29:06 CST 2007


Hi Arthur:

You expressed a couple of very interesting points. Data concepts as related
to and from the perception of the client.

Jim


-----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 6: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




More information about the dba-SQLServer mailing list