CREATE BLOG dbo.SqlServer: DBA Job Interview Question #4: Explain the Difference Between VARCHAR and CHAR

Monday, November 27, 2006

DBA Job Interview Question #4: Explain the Difference Between VARCHAR and CHAR

Here's question #4 in our series on DBA Job Interview Questions:

Explain the Difference Between VARCHAR and CHAR

This is about as much of a softball question as you could expect on a junior-level interview but, nonetheless, it is still an important one to understand. A lot of times interviewers will ask softball questions so that they can grill you on your answers:

Interviewer: "Why don't you tell me the difference between using VARCHAR and CHAR?"

Interviewee: "Oh sure - VARCHAR is for variable length data and CHAR is for fixed data."

Now, if I'm the interviewer, that's exactly the type of answer that sounds like it was memorized from a book. If you gave me that answer in a job interview, I would immediately start hammering you to see if you really knew what you just said! If, on the other hand, you gave a more meaningful answer (something that sounds like you have experience with the topic), I'm more likely to be impressed:

Interviewer: "Why don't you tell me the difference between using VARCHAR and CHAR?"

Interviewee: "Oh sure - VARCHAR is used for string data and is really used for storing strings that have an indefinite length; i.e. someone's last name. We don't know, in advance, what everyone's last name will be so we use something like VARCHAR(128) so that, in case they have a really long last name, we have the storage for it but, if they have a very short last name, we can store it as well. Now with the CHAR datatype, that's something that I really don't use often since it's for a fixed length string. Something like Country codes, state codes or even product codes that a fixed number of characters - that's when I use the CHAR datatype."

You haven't really given a definition or memorized-from-a-book type of answer; what you've really done is explain the real-world usage of the two datatypes in a way that shows that you are THE MAN.

BTW - In case you haven't heard or don't know, there are multiple US English pronunciations of VARCHAR and CHAR. I say var-char (pronouncing the "ch") but others say var-car (a hard C like the word "car"). They're both right - it's just a preference. I've also heard var-chair and var-care (like the word "care"). I've even heard "vair-" (rhymes with "care")... Don't be intimidated to pronounce it differently from the interviewer or to mention different pronunciations. Because I work with people from all parts of the world, whenever I talk about VARCHAR/CHAR datatypes, I make sure to use both of the most likely pronunciations when I introduce them into converstation. I say something like, "Now I notice your var-char/var-car usage is a bit suspect. By the way, some people say var-char and others say var-car. I just say var-car." That way it's out in the open, it helps everyone understand my accent/preferences, and it sort of lets them know that it's totally fine for them to say it a different way.

I guess "data" (like a sheep - "daaaaaaahhhhhta") and "data" (like "day-tah") are the same way. Sometimes I say database, other times I say "database". I don't know why.