Oct 20, 2015

Difference between VARCHAR and NVARCHAR in SQL Server

1.    VARCHAR is Non-Unicode variable length character data type while NVARCHAR Unicode variable length character data type.

2.    VARCHAR takes 1 byte per character while NVARCHAR takes 2 byte per Unicode/Non-Unicode character. For example:


DECLARE @name AS VARCHAR(50) = 'Dilip'
SELECT @name AS Name,
DATALENGTH(@name) AS [Length]


Result Set
    Name                                               Length
    -------------------------------------------------- -----------
    Dilip                                              5

    (1 row(s) affected)


                     
DECLARE @name AS NVARCHAR(50) = 'Dilip'
SELECT @name AS Name,
DATALENGTH(@name) AS [Length]


Result Set
Name                                               Length
-------------------------------------------------- -----------
Dilip                                              10

(1 row(s) affected)

3.    VARCHAR can store max 8000 Unicode characters while NVARCHAR can store max 4000 Unicode/Non-Unicode characters.


4.    VARCHAR takes number of bytes equal to the number of characters entered plus two bytes extra for defining offset while NVARCHAR  takes number of bytes equal to twice the number of characters entered plus two bytes extra for defining offset.