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