Difference between Varchar & Nvarchar in Sqlserver

Varchar means Variable-length Character string.
Nvarchar will store Unicode characters.Both will be used all most for the same purpose but with little difference.
Varchar will store the 8-bit data in database where as Nvarchar will be stored as 16-bit data in Database.
In Sql server 2005

The size for a table page is 8,196 bytes, and no one row in a table can be more than 8,060 characters.
This in turn limits the maximum size of a VARCHAR to 8,000 bytes.
Varchar (MAX) and Nvarchar (MAX). Varchar (MAX) can hold max of 2,147,483,648 characters and NVARCHAR (MAX) can hold 1,073,741,823 characters. We use Varchar instead of TEXT and NTEXT which cannot be used to passed as variables in a stored procedure where as Varchar (MAX) or Nvarchar (MAX) cannot have such restriction.

If you're in the process of migrating an existing data design for SQL Server 2005, it might make sense to migrate some TEXT / NTEXT fields to VARCHAR (MAX) / NVARCHAR (MAX) types when appropriate.

If you need Unicode support for a given data type, either now or soon enough, go with NVARCHAR. If you're sticking with 8-bit data for design or storage reasons, go with VARCHAR. Note that you can always migrate from VARCHAR to NVARCHAR at the cost of some room -- but you can't go the other way 'round. Also, because NVARCHAR involves fetching that much more data, it may prove to be slower depending on how many table pages must be retrieved for any given operation.


Comments

Author: sangeetha06 Mar 2009 Member Level: Gold   Points : 2

varchar uses 8 bits per character. nvarchar is UNICODE, a double-byte
character set, requiring 16 bits to store each character. UNICODE can
handle languages that are difficult to impossible with single-byte
character sets such as ASCII. If you must support many languages
UNICODE is a very clean way to do it.

Author: PHANI HARSHITHA MADALA14 Mar 2009 Member Level: Gold   Points : 0

hi,

nice piece of information

Author: Gopalakrishnan Natarajan18 Mar 2009 Member Level: Bronze   Points : 1

hi jayakumar,

Last week i was attend one interviews.They ask the difference between varchar and nchar...your information is very useful for me....

Thanks a lot....

Author: D.Jeya kumar(JK)19 Mar 2009 Member Level: Gold   Points : 0

Dear all,

Thanks a lot for your valuble feedbacks...

Regards
JK

Author: Shivshanker Cheral04 May 2009 Member Level: Gold   Points : 1

Its copied from

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1266201,00.html



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: