First of all, the basic difference between a varchar and a nvarchar datatype
is that for each variable the latter one takes double the space needed by the
former one. Now why double? Because of the extra "n", which means that it *can*
store Unicode characters as well.
Unicode characters represent a wide variety of foreign locales and many times
they need an extra byte for the same char storage. Hence nvarchar can store an
extended character set.
In SQL Server 2000, varchar has the maximum limit of 8000 characters (when
you need to give a particular size), and nvarchar has 4000 characters only
(remember it needs double the space needed by a varchar, hence the storage
capacity becomes half). Infact a given row in a SQL Server 2000 table cannot
exceed 8000 characters in size.
But if you need to store more than 8000 characters? There was no option other
than to use TEXT or the NTEXT datattypes, common called as BLOBS (Binary Large
Objects), both of which have their own limitations.
Hence in SQL Server 2005, the MAX identifier was introduced, which allows us
to go beyond 8000 characters and store upto 2^31- 1 bytes,which comes around 2
GB!! But note that you still cannnot specify a size greater than 8000 characters
in n/varchar (like varchar(12000) is still not allowed, need to use MAX for
data greater than 8000 bytes).
We can use them as: varchar (MAX) nvarchar (MAX)
So as a general practice, avoid using TEXT/NTEXT datattypes and use
varchar(MAX) and nvarchar(MAX) instead.