What is meant by nvarchar
?
What is the difference between char
, nchar
, varchar
, and nvarchar
in SQL Server?
This question is related to
sql-server
varchar
My attempt to summarize and correct the existing answers:
First, char
and nchar
will always use a fixed amount of storage space, even when the string to be stored is smaller than the available space, whereas varchar
and nvarchar
will use only as much storage space as is needed to store that string (plus two bytes of overhead, presumably to store the string length). So remember, "var" means "variable", as in variable space.
The second major point to understand is that, nchar
and nvarchar
store strings using exactly two bytes per character, whereas char
and varchar
use an encoding determined by the collation code page, which will usually be exactly one byte per character (though there are exceptions, see below). By using two bytes per character, a very wide range of characters can be stored, so the basic thing to remember here is that nchar
and nvarchar
tend to be a much better choice when you want internationalization support, which you probably do.
Now for some some finer points.
First, nchar
and nvarchar
columns always store data using UCS-2. This means that exactly two bytes per character will be used, and any Unicode character in the Basic Multilingual Plane (BMP) can be stored by an nchar
or nvarchar
field. However, it is not the case that any Unicode character can be stored. For example, according to Wikipedia, the code points for Egyptian hieroglyphs fall outside of the BMP. There are, therefore, Unicode strings that can be represented in UTF-8 and other true Unicode encodings that cannot be stored in a SQL Server nchar
or nvarchar
field, and strings written in Egyptian hieroglyphs would be among them. Fortunately your users probably don't write in that script, but it's something to keep in mind!
Another confusing but interesting point that other posters have highlighted is that char
and varchar
fields may use two bytes per character for certain characters if the collation code page requires it. (Martin Smith gives an excellent example in which he shows how Chinese_Traditional_Stroke_Order_100_CS_AS_KS_WS exhibits this behavior. Check it out.)
UPDATE: As of SQL Server 2012, there are finally code pages for UTF-16, for example Latin1_General_100_CI_AS_SC, which can truly cover the entire Unicode range.
NVARCHAR can store Unicode characters and takes 2 bytes per character.
nchar requires more space than nvarchar.
eg,
A nchar(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 nvarchar(100) will save 5 characters.
The differences are:
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.)
All the answers so far indicate that varchar
is single byte, nvarchar
is double byte. The first part of this actually depends on collation as illustrated below.
DECLARE @T TABLE
(
C1 VARCHAR(20) COLLATE Chinese_Traditional_Stroke_Order_100_CS_AS_KS_WS,
C2 NVARCHAR(20)COLLATE Chinese_Traditional_Stroke_Order_100_CS_AS_KS_WS
)
INSERT INTO @T
VALUES (N'???????',N'???????'),
(N'abc',N'abc');
SELECT C1,
C2,
LEN(C1) AS [LEN(C1)],
DATALENGTH(C1) AS [DATALENGTH(C1)],
LEN(C2) AS [LEN(C2)],
DATALENGTH(C2) AS [DATALENGTH(C2)]
FROM @T
Returns
Note that the ?
and ?
characters were still not represented in the VARCHAR
version and were silently replaced with ?
.
There are actually still no Chinese characters that can be reprsented by a single byte in that collation. The only single byte characters are the typical western ASCII set.
Because of this it is possible for an insert from a nvarchar(X)
column to a varchar(X)
column to fail with a truncation error (where X denotes a number that is the same in both instances).
SQL Server 2012 adds SC (Supplementary Character) collations that support UTF-16
. In these collations a single nvarchar
character may take 2 or 4 bytes.
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.
Just to add something more: nchar - adds trailing spaces to the data. nvarchar - does not add trailing spaces to the data.
So, if you are going to filter your dataset by an 'nchar' field, you may want to use RTRIM to remove the spaces. E.g. nchar(10) field called BRAND stores the word NIKE. It adds 6 spaces to the right of the word. So, when filtering, the expression should read: RTRIM(Fields!BRAND.Value) = "NIKE"
Hope this helps someone out there because I was struggling with it for a bit just now!
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).
This question is a duplicate of this one.
NVARCHAR can store Unicode characters and takes 2 bytes per character.
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.
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 lengthnchar[(n)]
(national character)
n
defines the string length and must be a value from 1 through 4,000.n
bytes.nvarchar [(n | max)]
(national character varying.)
n
defines the string length and can be a value from 1 through 4,000.max
indicates that the maximum storage size is 2^31-1 bytes (2 GB).char [(n)]
(character)
non-Unicode
string data.n
defines the string length and must be a value from 1 through 8,000.n
bytes.varchar [(n | max)]
(character varying)
n
defines the string length and can be a value from 1 through 8,000.max
indicates that the maximum storage size is 2^31-1 bytes (2 GB).Just to add something more: nchar - adds trailing spaces to the data. nvarchar - does not add trailing spaces to the data.
So, if you are going to filter your dataset by an 'nchar' field, you may want to use RTRIM to remove the spaces. E.g. nchar(10) field called BRAND stores the word NIKE. It adds 6 spaces to the right of the word. So, when filtering, the expression should read: RTRIM(Fields!BRAND.Value) = "NIKE"
Hope this helps someone out there because I was struggling with it for a bit just now!
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 lengthnchar 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.
NVARCHAR can store Unicode characters and takes 2 bytes per character.
nchar requires more space than nvarchar.
eg,
A nchar(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 nvarchar(100) will save 5 characters.
My attempt to summarize and correct the existing answers:
First, char
and nchar
will always use a fixed amount of storage space, even when the string to be stored is smaller than the available space, whereas varchar
and nvarchar
will use only as much storage space as is needed to store that string (plus two bytes of overhead, presumably to store the string length). So remember, "var" means "variable", as in variable space.
The second major point to understand is that, nchar
and nvarchar
store strings using exactly two bytes per character, whereas char
and varchar
use an encoding determined by the collation code page, which will usually be exactly one byte per character (though there are exceptions, see below). By using two bytes per character, a very wide range of characters can be stored, so the basic thing to remember here is that nchar
and nvarchar
tend to be a much better choice when you want internationalization support, which you probably do.
Now for some some finer points.
First, nchar
and nvarchar
columns always store data using UCS-2. This means that exactly two bytes per character will be used, and any Unicode character in the Basic Multilingual Plane (BMP) can be stored by an nchar
or nvarchar
field. However, it is not the case that any Unicode character can be stored. For example, according to Wikipedia, the code points for Egyptian hieroglyphs fall outside of the BMP. There are, therefore, Unicode strings that can be represented in UTF-8 and other true Unicode encodings that cannot be stored in a SQL Server nchar
or nvarchar
field, and strings written in Egyptian hieroglyphs would be among them. Fortunately your users probably don't write in that script, but it's something to keep in mind!
Another confusing but interesting point that other posters have highlighted is that char
and varchar
fields may use two bytes per character for certain characters if the collation code page requires it. (Martin Smith gives an excellent example in which he shows how Chinese_Traditional_Stroke_Order_100_CS_AS_KS_WS exhibits this behavior. Check it out.)
UPDATE: As of SQL Server 2012, there are finally code pages for UTF-16, for example Latin1_General_100_CI_AS_SC, which can truly cover the entire Unicode range.
All the answers so far indicate that varchar
is single byte, nvarchar
is double byte. The first part of this actually depends on collation as illustrated below.
DECLARE @T TABLE
(
C1 VARCHAR(20) COLLATE Chinese_Traditional_Stroke_Order_100_CS_AS_KS_WS,
C2 NVARCHAR(20)COLLATE Chinese_Traditional_Stroke_Order_100_CS_AS_KS_WS
)
INSERT INTO @T
VALUES (N'???????',N'???????'),
(N'abc',N'abc');
SELECT C1,
C2,
LEN(C1) AS [LEN(C1)],
DATALENGTH(C1) AS [DATALENGTH(C1)],
LEN(C2) AS [LEN(C2)],
DATALENGTH(C2) AS [DATALENGTH(C2)]
FROM @T
Returns
Note that the ?
and ?
characters were still not represented in the VARCHAR
version and were silently replaced with ?
.
There are actually still no Chinese characters that can be reprsented by a single byte in that collation. The only single byte characters are the typical western ASCII set.
Because of this it is possible for an insert from a nvarchar(X)
column to a varchar(X)
column to fail with a truncation error (where X denotes a number that is the same in both instances).
SQL Server 2012 adds SC (Supplementary Character) collations that support UTF-16
. In these collations a single nvarchar
character may take 2 or 4 bytes.
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.
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.
NVARCHAR can store Unicode characters and takes 2 bytes per character.
nchar requires more space than nvarchar.
eg,
A nchar(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 nvarchar(100) will save 5 characters.
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).
This question is a duplicate of this one.
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.
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).
This question is a duplicate of this one.
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.
nchar[(n)]
(national character)
n
defines the string length and must be a value from 1 through 4,000.n
bytes.nvarchar [(n | max)]
(national character varying.)
n
defines the string length and can be a value from 1 through 4,000.max
indicates that the maximum storage size is 2^31-1 bytes (2 GB).char [(n)]
(character)
non-Unicode
string data.n
defines the string length and must be a value from 1 through 8,000.n
bytes.varchar [(n | max)]
(character varying)
n
defines the string length and can be a value from 1 through 8,000.max
indicates that the maximum storage size is 2^31-1 bytes (2 GB).nchar requires more space than nvarchar.
eg,
A nchar(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 nvarchar(100) will save 5 characters.
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).
This question is a duplicate of this one.
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.
The differences are:
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.)
Source: Stackoverflow.com