Difference between VARCHAR and NVARCHAR in SQL Server


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

2.    VARCHAR takes 1 byte per character while NVARCHAR takes 2 bytes 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]


ResultSet
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 the number of bytes equal to the number of characters entered plus two bytes extra for defining the offset while NVARCHAR  takes the number of bytes equal to twice the number of characters entered plus two bytes extra for defining offset.

No comments:

Post a Comment

Please do not enter any spam link in the comment box.

Related Posts

Fatal Error: Peer authentication failed for user "postgres", when trying to get pgsql working with rails.

Problem: Sometimes, we get the following error when trying to make a connection in Rails.  FATAL ERROR: Peer authentication failed for us...