Why is there still a varchar data type?

Question

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?

Answer 1

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

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

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.