[dba-SQLServer] varchar vs char

artful at rogers.com artful at rogers.com
Mon Jan 22 20:03:00 CST 2007


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








More information about the dba-SQLServer mailing list