VarChar versus NVarChar in SQL Server 2005

Both of them are used for storing text (series of characters) but have the following differences:

VarChar field can store a string of characters, but NVarChar enables Unicode characters to also be stored in a field. Unicode characters are characters used globally in difference locales (example É, ݘ, ✉, etc.).
And this difference stems for the second difference.

As NVarChar can store Unicode characters which may require 2 bytes (i.e. 16 bits) it takes double the space for a character in memory in comparison to VarChar

In SQL Server 2000, varchar has a maximum limit of 8000 characters and nvarchar has 4000 characters only (remember it needs double the space needed by a varchar, hence the storage capacity becomes half). In fact a given row in a SQL Server 2000 table cannot exceed 8000 characters in size.
Beyond 8000, one had to go with Text and Ntext data types. However, in SQL Server 2005, the max keyword has been introduced which replaces the Text/NText fields by maximizing the capacity of varchar/nvarchar.

By using varchar(max)/nvarchar(max) the storage limit reaches upto 2^31 bytes of data.

HTH!

Advertisement