[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