Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Many of my databases have fields defined as varchars. This hasn't been much of problem since I live and work in America (where the only language that exists is "American". ahem)

After working with databases for about 5 years, I've found I eventually run into problems with the limited nature of the varchar field and I have to modify my fields to store data as nvarchars. After having to make another update to a table, converting a varchar field to an nvarchar, I just had the thought-- why are we still doing it this way? I've long since made the mental decision to define all of new my text fields to nvarchar, instead of varchar, which is what I learned to do from my text books when I was in school 10 years ago.

It's 2011 and there was a new release of SQL Server last year. Why do we continue to support a varchar datatype when we can/should instead be using nvarchar?

I know that it is often argued that nvarchars are "twice as large" as varchars, so storage space usage could be one arguement for maitaining varcars.

However, today's users could define their nvarchars to store the data as UTF-8 instead of the default UTF-16 if they want to save on storage space. This would allow for 8-bit encoding if that is primarily desirable, while giving assurance that the rare 2-8 byte character that gets inserted into their DB would't break anything.

Am I missing something? Is there a good reason why this hasn't changed over the past 15-20 years?

share|improve this question
3  
How would your setting help? If I suddenly needed to support Unicode data, I'd either have to change the column to NVARCHAR (today) or change that setting (in your ideal world). Anyway the reason this hasn't changed is backward compatibility. Now that we have DATE and DATETIME2 you could argue that we could get rid of DATETIME/SMALLDATETIME but it's not as simple as you make it sound to just eradicate a data type (much less get people to stop using it). Look how many people still get tripped up on TIMESTAMP... –  Aaron Bertrand Sep 2 '11 at 13:49
1  
But you're expecting those to happen overnight, and they just don't. I am skeptical when someone proposes a design that includes varchar columns, and it doesn't take much to convince them nvarchar is better, but they still do make sense in cases where you know you will never use Unicode. Why use nvarchar for a phone number or zip code? The other issue is that lots of folks are still on 2000 or 2005, or newer editions but not on Enterprise+, so can't take advantage of nvarchar improvements such as Unicode compression (which in a lot of shops makes nvarchar a no-brainer). For everyone else... –  Aaron Bertrand Sep 2 '11 at 13:56
1  
IMHO, a limited character set is typically the exception, not the rule. A "common" exception, but still, an exception. This hasn't changed in the past couple years-- it's been that way (and often a problem) for at least 15 years, when considering encoding needs for many applications. Yet the paradigm hasn't changed in peoples mines. In regards to using a UTF-8 vc, that doesn't seem to always behave properly. I don't know why, but I assume it's for either "internal" reasons, or maybe there are tweaks that I may be ignorant of. If I have importation encoding issues, storing data as nvc works. –  RLH Sep 2 '11 at 14:06
1  
So can you show an example of a SQL Server database that uses UTF-8 for a varchar column and allows you to store Chinese and other Far East characters without loss going in or coming out? –  Aaron Bertrand Sep 2 '11 at 14:32
1  
@R..: varchar allows for a single byte per character. The UTF-8 character set cannot be represented witha single byte, as UTF-8 is a variable length encoding. The characters that UTF-8 shares with ASCII are all a single byte (and the same value as ASCII), but the other characters are not. –  Adam Robinson Sep 4 '11 at 0:17
show 8 more comments

5 Answers

up vote 31 down vote accepted
  1. varchar work is good enough for a lot of Western European languages (Norwegian, Danish, German, French, Dutch etc too) subject to some collation issues

  2. See this on SO varchar vs nvarchar performance nvarchar has serious performance implications

  3. This is trivial compared to dealing with dates MDY vs DMY

share|improve this answer
1  
I raised my question out of curiosity because the system that I have had trouble exporting data from is a legacy IBM system that has been in use for 20+ years, which stores multi-byte character data. This led me to believe that there has to be a better, more simplified solution since we've been encoding data like this for at least 20+ years! I've worked with text long enough to know that the whole issue of text encoding & conversion can get ugly, fast. That said, it just feels like there should be a more elegant, modern solution to storing text that should be ubiquitous among DBAs. –  RLH Sep 2 '11 at 15:40
7  
@RLH: The fact is that you can pick speed or size, but not both. Variable-length encodings (which all Unicode encodings other than UTF-32 are) add overhead when it comes to parsing. Among other things, character-offset memory optimizations cannot be made because the characters are of an unknown length. This makes parsing slow, which makes searching slower. Assuming UTF-32 for everything could make it faster, but data that fits perfectly well within ASCII would suddenly be four times its original size. –  Adam Robinson Sep 2 '11 at 17:07
1  
@Adam: Character-offsets are useless in general textual data. The only time they might be mildly useful is for strictly fixed-form data like fixed-length alphanumeric serial numbers, keys, and such, and even then it's a stretch. Your comment is nothing but the classic ignorant anti-UTF-8 arguments that were already shot down nearly 20 years ago by people much smarter than us. –  R.. Sep 4 '11 at 1:02
    
@R.. you're certainly entitled to your opinion, but the fact remains that nvarchar columns have poorer performance than equivalent varchar columns in many RDBMS's (my experience is with SQL Server, but I have heard of implications in Oracle as well; no idea bout DB2, Sybase, etc.). If the data truly won't need the wider character set of unicode, then there's no point in suffering the performance impact. –  Adam Robinson Sep 4 '11 at 1:22
1  
The real issue is that even nvarchar won't deal with the fact that Unicode codepoints aren't exactly characters as perceived by users. Combining diacritics (and Unicode NFD) are a classic example. The best advice I've seen for most software is to “store it and ship it as it is, but try to avoid understanding it”. I suspect databases can work like that just fine most of the time… –  Donal Fellows Sep 5 '11 at 9:36
show 3 more comments

In addition to the answers addressing standards and compatibility, one should also keep in mind performance. While disk space is readily accepted as cheap, DBAs/Developers often ignore the fact that query performance is at times directly related to the row/page size of a table. Using NVARCHAR rather than VARCHAR (when unnecessary) will effectively double the row-size for your character fields. If you have, say, 5 or 10 50-length fields, you're talking about potentially adding an additional 500 bytes per row. If you have a wide table, this could push each row into multiple pages and have an adverse affect on performance.

share|improve this answer
add comment

Plenty of organizations still have a large installed base of applications, interfaces, platforms and tools that assume single-byte characters. Databases rarely live in isolation - they are one part of an IT ecosystem. If you have thousands of components and millions of lines of code dependent on single byte characters then you'd need a good reason to invest the time and money required to switch to unicode. Changes on that scale could take years to complete. In some places Unicode is still relatively new, rare or not fully supported.

VARCHAR and NVARCHAR are both part of ISO Standard SQL. Removing or deprecating VARCHAR support in SQL Server would be a step backwards in compatibility and portability.

share|improve this answer
add comment

Because some of us build lighter, smaller applications on less than state-of-the-art hardware that have no need for Unicode capabilities. Maybe we will need to change it later, but for now, we simply don't need it. I like my strings taking 1/2 the space they otherwise would have to under NVARCHAR.

share|improve this answer
    
but if you're building a small applications, does it really matter if your table takes 10 MB vs 20 MB? –  Lie Ryan Sep 2 '11 at 19:41
    
Depends on how small your server is, and your definition of "small". –  Jennifer S Sep 2 '11 at 20:02
1  
If you're developing a smartphone application, 10MB vs 20MB could be a big deal –  Derek Kromm Sep 3 '11 at 0:20
1  
Searching through those strings takes time as well. Especially if you're building a "large" app with millions of rows. The bottom line is that it matters. All I'm saying is that if I don't require Unicode, it would hurt performance if varchar didn't exist. I liked Adam Robinson's comment on another answer that said you can have speed or size, but not both. I don't need the size, therefore, I would like to enjoy the speed. –  Jason Sep 5 '11 at 1:39
add comment

Alternatively, today's users could define their nvarchars to store the data as UTF-8 instead of the default UTF-16 if they want to save on storage space.

This is exactly what most open-source databases do with VARCHAR.

  • MySQL provides utf8 and ucs2 "collations".
  • SQLite gives you a choice between UTF-8 (the default) and UTF-16.
  • PostgreSQL supports UTF-8 (but not UTF-16).

No need to have two separate string types.

Microsoft is the odd one out with its view that 8-bit strings are for legacy encodings and Unicode = UTF-16. Which is probably related to the Windows API itself treating char and wchar_t that way.

share|improve this answer
4  
+1 for pointing out that OP's question is an artificial issue that arises out of Windows' brokenness. –  R.. Sep 3 '11 at 4:45
    
@R. Can you explain Sybase and Oracle then? infocenter.sybase.com/help/index.jsp?topic=/… and download.oracle.com/docs/cd/B28359_01/server.111/b28318/… –  gbn Sep 3 '11 at 13:45
    
@dan04: MS is not the odd one out... –  gbn Sep 3 '11 at 13:45
1  
The only explanation for Oracle even existing is incompetent management having way too much money to throw away... –  R.. Sep 3 '11 at 13:56
1  
@gbn: Oracle does support UTF-8 (or "AL32UTF8"), so NCHAR isn't needed the way it is with MS SQL Server. –  dan04 Sep 3 '11 at 15:27
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.