char vs. vchar?

kknieriem
kknieriem used Ask the Experts™
on
What is the difference between the char and vchar field format and is there a maximum number of characters for either?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
char is a fixed-length string, varchar is a variable length string.  Both have a max length of 8000.

The basic difference.  Say you have this query:

SELECT Last_Name + ', ' + First_Name FROM Customers

The returned value will differ if you are using char vs varchar:

Last_Name   First_Name
Smith            Bill

If your fields are both length of 15 (just as an example), then if they are char, your return value would be:

"Smith          , Bill           "

Whereas with varchar, the return would be:
"Smith, Bill"

In addition, varchar fields only use as much space as they need (plus a bit for the total length value), whereas char fields always use the total space.  So, if you have a field defined as [var]char(1000), and the average length of data in it is only 100 characters, and you have 10,000 records, total space used for varchar would be about (102 * 10000) = 1,020,000 bytes.  However, a char field would use 1000 * 10000 = 10,000,000 bytes, almost 10 times the space.
the max for both is 8000 characters

VARCHAR stands for variable length char.  This means that SQL server does not store the entire defined field.  If a field is defined as 8000 characters, and it is only populated with 10 characters, it will only store those 10 characters.  Also, varchar fields trim off any spaces off the end of a string before storing.

ADW
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
Slight correction: varchar fields require two bytes to store the length, not one bit.  There is also a row overhead of at least two bytes (or something like that) per row if any variable length column is used in a row.  Of course you will still save a lot of space with a VARCHAR(100) vs. a CHAR(100) if you usually don't store 100 bytes.  But, note that it makes no sense to define a VARCHAR(1) or VARCHAR(2) (or even VARCHAR(3)), although I've seen people do it; you will save space and very slightly improve performance be using CHAR(1) or CHAR(2) (or CHAR(3)).
kknieriem:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial