What is the difference between char, nchar, varcha

Question

What is meant by nvarchar?

What is the difference between char, nchar, varchar, and nvarchar in SQL Server?

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

NVARCHAR can store Unicode characters and takes 2 bytes per character.

Answer 5

• 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 length
• NChar = 16 bit length

Answer 6

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.