Datatype char vs. varchar 3

[jhilltektips]

Hello,

If I am just inserting a simple select list field from an HTML form into my SQLTable, should I use the datatype char, or varchar? What is the difference, and is one more beneficial than the other?

Thanks,
JHill

 

[SQLBill]

Basically, CHAR is a 'set' length. If you use less than the alloted amount of space, it fills the rest. So a CHAR(10) always takes the space of 10 characters even if you only enter two characters for example.

VARCHAR is a variable length. If you use less than the alloted amount of space it DOESN'T fill the rest. So a VARCHAR(10) will use less space if you enter less than 10 characters.

-SQLBill

 

[rac2]

The decision to use CHAR or VARCHAR was at one time important to conserve disk storage. CHAR(10) always uses the same amount of space for an item, 'x' is converted to 'x ' and stored; it uses the same amount of space as 'xxxxxxxxxx'; VARCHAR(10) would use less space to store 'x' than to store 'xxxxxxxxxx'. VARCHAR has some overhead that CHAR does not because the system eventually must calculate how many characters to retrieve. With MS SQL Server, you must remember that you have stored 'x ' and not 'x' when you are searching; the two values are not equal.

Unless you are working in an environment with an enormous volume of data and transactions this kind of fine tuning won't make any difference.

I like to use CHAR for codes such as state abbreviations, CHAR(2) to document the fact that I am using two characters for state and not the name of the state; but this is more a matter of style than function.

 

[SQLSister]

If you use Char and the data in the field doesn't match the field length, then it becomes more complicted to use in a where clause because you have to trim the trailing zeros or you won't find the records you want. I only use char datatype if the exact length of the field is known, the state abbreviations are a good example. Other than that I use varchar or nvarchar.

 

[jhilltektips]

Thank you for your help.
I will use varchar for select lists, etc so I can avoid any mishap by forgetting to use the trim() function. I will use char for state abbreviations, zipcodes, etc where there is a set length (2,5,etc)
However, I do notice a significant increase in execution speed when searching on a datatype carchar rather than nvarchar. So, unless I am dealing with universal characters, I will user varchar over nvarchar.

Thanks for all the help.

JHill