[dba-SQLServer] varchar vs char

Thanks for that, interesting stuff. 


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 Stuart
McLachlan
Sent: Monday, January 22, 2007 10:43 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] varchar vs char

On 22 Jan 2007 at 22:13, JWColby wrote:

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

A few pointers from http://www.sql-server-performance.com/datatypes.asp

If the text data in a column varies greatly in length, use a VARCHAR data
type instead of a CHAR data type. The amount of space saved by using VARCHAR
over CHAR on variable length columns can greatly reduce I/O reads cache
memory used to hold data, improving overall SQL Server performance.  

Another advantage of using VARCHAR over CHAR columns is that sorts performed
on VARCHAR columns are generally faster than on CHAR columns. This is
because the entire width of a CHAR column needs to be sorted. [6.5, 7.0,
2000] Updated 8-21-2005.  

*****

If a column's data does not vary widely in length, consider using a
fixed-length CHAR field instead of a VARCHAR. While it may take up a little
more space to store the data, processing fixed-length columns is faster in
SQL Server than processing variable-length columns. [6.5, 7.0, 2000] Updated
8-21-2005  


If you are using fixed length columns (CHAR, NCHAR) in your table, do your
best to avoid storing NULLs in them. If you do, the entire amount of space
dedicated to the column will be used up. For example, if you have a fixed
length column of 255 characters, and if you place a NULL in it, then 255
characters have to be stored in the database. This is a large waste of space
that will cause SQL Server to have to perform extra disk I/O to read data
pages. It also wastes space in the data cache buffer. 
Both of these contribute to reduced SQL Server performance.

Instead of using NULLs, use a coding scheme similar to this in your
databases:

    * NA: Not applicable
    * NYN: Not yet known
    * TUN: Truly unknown

Such a scheme provides the benefits of using NULLs, but without the
drawbacks.

If you really must use NULLs, use a variable length column instead of a
fixed length column. (I doubt your reason for using Nulls is very good.)
Variable length columns only use a very small amount of space to store a
NULL. [7.0, 2000] Updated 5-15-
2006
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com