Few Points on VarChar and NVarchar in SQL Server

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.