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

What is meant by nvarchar?

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


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.


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

enter image description here

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 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.


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!