Another trip with Mr. Peabody's WABAC Machine unearthed a valuable blog entry that I posted before my Simple-Talk blogging days. This one is somewhat of a "Back to the basics" style blog entry that I thought would be worth sharing once again. Enjoy!

If you peruse quotes and philosophies of very successful people it will not take long to see that revisiting the basics of a discipline is something that is critical to excelling; thus, I thought that it would be a good idea to comment on the varchar and nvarchar data types.

According to SQL Server 2005 Books Online the varchar(n) data type is described as:
"Variable-length, non-Unicode character data. n can be a value from 1 through 8,000."

According to SQL Server 2005 Books Online the nvarchar(n) data type is described as:
"Variable-length Unicode character data. n can be a value from 1 through 4,000."

The "n" referred to above is the defined maximum size of the data type. For example declaring the column in your table as varchar(50) will mean that the column will store up to 50 characters. For the untrained eye the immediate impression may be that varchar can store twice the data of nvarchar. In actuality both data types store the same number of bytes - it is the number of characters that differ. Within the BOL definitions above the key word to pay attention to is "Unicode".

Non-Unicode characters are stored in a single byte. For example: "A" would be encoded as an ASCII value of "65". These single byte values range from 0 - 255 which are represented in an ASCII table. The following link is a good resource for these values: http://www.asciitable.com/

In reviewing the ASCII table you will find that if you are using languages that utilize characters that are not included in the ASCII table (such as Japanese, Chinese, Korean, etc.) it can be rather limiting.

Unicode characters are encoded in two bytes (double-byte). For example: "A" would be stored as a value of "A". These double-byte characters have 65,536 combinations which accommodates majority, if not all, languages in the world. A good tool to convert ASCII text to Unicode text can be found at this link: http://www.industrialtrainer.com/Unicode.shtm

If the database that is being designed does not need to consider character sets beyond the ASCII set the use of varchar would be the more efficient storage option; but in this world of ever growing globalization and need to accommodate various character sets it may be wise to consider the strategic utilization of nvarchar for select columns.