Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I'm learning T-SQL. From the examples I've seen, to insert text in a varchar() cell, I can write just the string to insert, but for nvarchar() cells, every example prefix the strings with the letter N.

I tried the following query on a table which has nvarchar() rows, and it works fine, so the prefix N is not required:

insert into [TableName] values ('Hello', 'World')

Why the strings are prefixed with N in every example I've seen?

What are the pros or cons of using this prefix?

share|improve this question
    
Isn't the N only needed for literal strings? –  Wayne In Yak Jul 6 '12 at 14:56
    
Polish is a non-Latin based language???? –  Heckflosse_230 Jul 6 '12 at 16:58
    
N means National, as in "National Varying Character", see Equivalent ANSI SQL Data Types. –  ErikE Jun 17 at 20:16

3 Answers 3

NVarchar is used for Unicode. If your database is not storing multilingual Data you can keep using Varchar. As an example: N'abc' simply converts your string to unicode.

share|improve this answer
    
Why don't you have to prefix with U instead of N then? –  kahoon Jul 6 '12 at 21:42
    
U could be confused for unsigned as a guess –  JB King Jul 6 '12 at 23:22

By default SQL server uses the Windows-1252 character codes for varchar. It contains most of characters for latin-based languages (English, German, French, etc.) But it does not contain characters for non-latin based languages (Polish, Russian, etc.). As stated by @Pieter B, nvarchar is used to get around that issue because it is for Unicode which contains those missing characters. This comes at a cost, it takes twice as much space to store nvarchar than varchar.

Putting N in front of your string ensures the characters are converted to Unicode before being placed into a nvarchar column. Most of the time you will be okay leaving the N off, but I wouldn't recommend it. It is a lot better to be safe than sorry.

share|improve this answer
2  
Just a clarification: "By default" SQL server uses the encoding corresponding to the Varchar field's collation, which is overridable at the time of creation of the field, generally based on the default collation for your instance. The default collation for your instance can be set at install time, but generally corresponds to the system default locale's CP_ACP. That will be Windows 1252 on a US-English machine, but 932 on a machine with a Japanese system locale, 1251 on a Russian machine, etc. The moral of the story? Use NVarchar :) –  JasonTrue Jul 14 '12 at 4:12

Because MS SQL Server has poor support for UTF-8 compared to other RDBMS.

MS SQL Server follows the convention, used within Windows itself, that "narrow" strings (char in C++, CHAR or VARCHAR in SQL) are encoded in a legacy "code page". The problem with code pages is that they have a limited number of characters (most are single-byte encodings, which limits the reportoire to 256 characters) and are designed around a single language (or group of languages with similar alphabets). This makes it hard to store multilingual data. For example, you can't store both Russian and Hebrew data because Russian uses code page 1251 and Hebrew uses code page 1255.

Unicode solves this problem by using a single giant coded character set with room for more than a million characters, enough to represent every language in the world. There are several Unicode encoding schemes; Microsoft prefers to use UTF-16, for historical reasons. Because UTF-16 represents strings as a sequence of 16-bit code units instead of the traditional 8-bit, a separate character type is needed. In MSVC++, this is wchar_t. And in MS SQL, it's NCHAR or NVARCHAR. The N stands for "national", which seems backwards to me because Unicode is about inter-nationalization, but that's the ISO terminology.

Other SQL implementations let you store UTF-8 text in a VARCHAR column. UTF-8 is a variable-length (1-4 bytes per character) encoding that's optimized for the case when your data is mostly in the Basic Latin range (which are represented as the same 1 byte per character as ASCII), but can represent any Unicode character. Thus, you'd avoid the "twice as much space" problem mentioned by bwalk2895.

Unfortunately, MS SQL Server does not support UTF-8 VARCHAR, so instead you have to either use UTF-16 instead (and waste space for ASCII text), use a non-Unicode code page (and lose the ability to represent foreign characters), or store UTF-8 in a BINARY column (and deal with inconveniences like SQL string functions not working properly, or having to view the data as a hex dump in your GUI DB manager).

share|improve this answer

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.