CREATE BLOG dbo.SqlServer

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

What is the difference between VARCHAR and NVARCHAR?

This is somewhat of a tricky question in the respect that the typical answer people will give sounds like they read it from a book - and that's bad. Here's a typical answer: "NVARCHAR is for Unicode data and VARCHAR is for non-Unicode data."

That's certainly correct but, as an interviewer, I would start digging deeper by asking further detail. Yes, you've provided a technically correct answer, but your answer shows no real knowledge other than your having memorized a definition.

What I would like to hear, as the interviewer, is an answer that shows me you know what Unicode data is, when it's used and what scenarios that it is appropriate to use Unicode. If you knew a few "gotchas" and "Best Practices" then I would be even more impressed. I would expect that, after watching my video on COLLATE and How Queries and Table Data Work with Case-Sensitive and Accent-Sensitive Data you'll be very well versed in using Unicode and Non-Unicode data.

The main point of asking this question is to see how "deep" your knowledge of SQL Server's internals is. For me, this question is really four questions:

• Explain character sets/code pages in relation to using VARCHAR/NVARCHAR
• How do you decide between using VARCHAR or NVARCHAR?
• What is Unicode?
• Why don't you just use Unicode for everything?
  

I can't say that every interviewer that asks this question (the main question) has the same four questions in mind but, if you know the answers to these questions, that means you have a solid understanding of how SQL Server processes string data - that will help you in any SQL interview. Let's cover each of these:

• Explain character sets/code pages in relation to using VARCHAR/NVARCHAR
• Character Set and Code Page are interchangeable words
• They define which characters are used
• Common to use Latin1_General in Latin-language based countries such as those that speak English, Italian, French, and Spanish
  
• Determine how the data is sorted in an ORDER BY and compared in WHERE/JOIN clauses
• How do you decide between using VARCHAR or NVARCHAR?
• This is generally a pretty simple decision: will you store data in multiple languages or that contains characters from multiple languages? If "Yes" then it should most likely be NVARCHAR otherwise you're probably safe using VARCHAR.
• Columns like FirstName and LastName for a website like ours that has users all around the world requires NVARCHAR
• Columns like CountryCode and EmailAddress are English-only thus they are VARCHAR
• What is Unicode?
• Too much for this post - read the Wikipedia entry
• Why don't you just use Unicode for everything?
• Because, if you just need to store US English (for example) then you would double the storage by using NVARCHAR vs. VARCHAR

Hope this helps!

---

Technorati Tags: Scott Whigham, Whigham, SQL Server, SQL Server 2005, SQL Server, Microsoft SQL Server