What is the difference between nchar and varchar in MSSQL? What does nvarchar mean?
Cheers!
+16
A:
nchar and char pretty much operate in exactly the same way as each other, as do nvarchar and varchar. The only difference between them is that nchar/nvarchar store Unicode characters (essential if you require the use of extended character sets) whilst varchar does not. Because Unicode characters require more storage, nchar/nvarchar fields take up twice as much space (so for example in earlier versions of SQL Server the maximum size of an nvarchar field is 4000).
Edit: This question is a duplicate of this one.
Luke Bennett
2008-10-06 22:51:40
I was always curious about this.Thanks for the info!
Miles
2008-10-08 15:11:58
You forget one thing: nchar uses a fixed-length so nchar(10) always needs to receive ten characters. And varchar(10) is indeed Unicode and will accept any number of characters, up to 10 characters. Also see http://msdn.microsoft.com/en-us/library/ms186939.aspx
Workshop Alex
2009-11-02 10:29:42
+1
A:
nchar(10) is a fixed-length Unicode string of length 10. nvarchar(10) is a variable-length Unicode string with a maximum length of 10. Typically, you would use the former if all data values are 10 characters and the latter if the lengths vary.
binarycoder
2008-10-06 22:53:07
Wrong comparison - question relates to nchar and varchar, not nchar and nvarchar.
Luke Bennett
2008-10-06 22:56:23
A:
NVARCHAR can store Unicode characters and takes 2 bytes per character.
Gustavo Rubio
2008-10-06 22:53:14
WRONG! Unicode uses between 1 and 4 bytes per character! Many people forget this! Even the use of UTF-16 might result in some characters taking 4 bytes instead of 2, although the common length will be 2 bytes. Certain other subformats of Unicode might take even more than 4 bytes!
Workshop Alex
2009-11-02 10:37:31
A:
nchar is fixed-length and can hold unicode characters. it uses two bytes storage per character.
varchar is of variable length and cannot hold unicode characters. it uses one byte storage per character.
Manu
2008-10-06 22:59:43
Wrong. Unicode can use 1 to 4 bytes (in general) for every character. Also, a varchar can hold unicode, but it's not recognised as unicode. As a result, a varchar is considered unreliable for unicode storage. (Especially since there's a risk that the code that accesses the field will translate it incorrectly.)
Workshop Alex
2009-11-02 10:39:43
@Alex: I think you made your point but I still do not agree with you. What you are saying is that an int CAN hold a long if the long happens to be smaller than 2^32. This is not only 'unreliable', it is an inherent limitation which makes it impossible to cover the whole value range.
Manu
2009-11-05 08:36:53
+26
A:
Just to clear up... or sum up...
- nchar and nvarchar can store Unicode characters.
- char and varchar cannot store Unicode characters.
- char and nchar are fixed-length which will reserve storage space for number of characters you specify even if you don't use up all that space.
- varchar and nvarchar are variable-length which will only use up spaces for the characters you store. It will not reserve storage like char or nchar.
nchar and nvarchar will take up twice as much storage space, so it may be wise to use them only if you need Unicode support.
Brian Kim
2008-10-06 23:02:33
char and varchar aren't meant to store unicode, but with some additional coding tricks and extra logic, you can still misuse a [var]char field for unicode storage.
Workshop Alex
2009-11-02 10:41:51
+1
A:
nchar requires more space than nvarchar.
eg,
A char(100) will always store 100 characters even if you only enter 5, the remaining 95 chars will be padded with spaces. Storing 5 characters in a varchar(100) will save 5 characters.
by,
Sunil.M.L
Not completely true, since you're required to fill a char(100) with up to 100 characters. You would use this when you're eg store phone numbers in your database, or order numbers with a fixed length. Because the field length is fixed, you have no choice to fill it up to the maximum number of characters. But when all your data is 100 characters per record, a char(100) will take less storage than a varchar(100) because it doesn't need a length indication: every value would be exactly 100 characters.
Workshop Alex
2009-11-02 10:44:44
+1
A:
char: fixed-length character data with a maximum length of 8000 characters.nchar: fixed-length unicode data with a maximum length of 4000 characters.Char= 8 bit lengthNChar= 16 bit length
ss
2009-11-02 09:35:40
+1
A:
The differences are:
- n[var]char stores unicode while [var]char just stores single-byte characters.
- [n]char requires a fixed number of characters of the exact length while [n]varchar accepts a variable number of characters up to and including the defined length.
Another difference is length. Both nchar and nvarchar can be up to 4,000 characters long. And char and varchar can be up to 8000 characters long. But for SQL Server you can also use a [n]varchar(max) which can handle up to 2,147,483,648 characters. (Two gigabytes, a signed 4-byte integer.)
Workshop Alex
2009-11-02 10:33:36
+1
A:
nchar and nvarchar can store unicode characters like $, @. So use these datatypes only when storing such unicodes are required. Otherwise char and varchar are preferrable
chirag
2009-12-15 08:04:32
+1
A:
Thank you very much for nice comments! This is more a question then answer... Every character in varchar fits in nvarchar. But if nvarchar length is 8 (16) and the character you save is varchar of 8 characters; will the nvarchar field be 8 long or 16? I assume 8, because if I save a nvarchar character of 8 using 2 bits; it will be 16...correct?
RBrattas
2010-10-08 18:41:38